Повторное поле в подзапросе

Question

Я пытаюсь сравнить два строковых столбца, один из которых находится в повторяющейся записи и находится в подзапросе:

#standardSQL
SELECT name AS produit
FROM   samples.overmind_reports 
WHERE  name IN (SELECT lines.article.sub_category.label FROM   samples.orders_lines)
ORDER BY produit 

Ошибка:

Невозможно получить доступ к полю статьи для значения с типом ARRAY>, ...>> в [5:21]

Схема таблицы:

Я пробовал с FLATTEN, но это только устаревшая функция?

#standardSQL
SELECT name AS produit
FROM   samples.overmind_reports 
WHERE  name IN (SELECT lines.article.sub_category.label FROM (FLATTEN(samples.orders_lines,lines)))
ORDER BY produit 

Я получил:

Ошибка: синтаксическая ошибка: ожидаемое ключевое слово JOIN, но полученное ")" в [6:50]

Я не могу использовать UNNEST

Answer 1

Попробуй это:

#standardSQL
SELECT
  t1.name AS produit
FROM `samples.overmind_reports` t1
WHERE EXISTS(SELECT 1 FROM `samples.orders_lines` t2, UNNEST(t2.lines) lines WHERE lines.article.sub_category.label = t1.name)
ORDER BY 1

Вы можете проверить с симулированными данными:

WITH `samples.overmind_reports` AS(
  SELECT 'name1' name UNION ALL
  SELECT 'name2' UNION ALL
  SELECT 'name3'
),
`samples.orders_lines` AS(
  SELECT ARRAY<STRUCT<article STRUCT<sub_category STRUCT<label STRING> > > > [STRUCT(STRUCT(STRUCT('label' AS label) AS sub_category) AS article), STRUCT(STRUCT(STRUCT('name1' AS label) AS sub_category) AS article)] lines UNION ALL
  SELECT ARRAY<STRUCT<article STRUCT<sub_category STRUCT<label STRING> > > > [STRUCT(STRUCT(STRUCT('label' AS label) AS sub_category) AS article), STRUCT(STRUCT(STRUCT('name1' AS label) AS sub_category) AS article)] lines UNION ALL
  SELECT ARRAY<STRUCT<article STRUCT<sub_category STRUCT<label STRING> > > > [STRUCT(STRUCT(STRUCT('label' AS label) AS sub_category) AS article), STRUCT(STRUCT(STRUCT('name3' AS label) AS sub_category) AS article)] lines
)

SELECT
  t1.name AS produit
FROM `samples.overmind_reports` t1
WHERE EXISTS(SELECT 1 FROM `samples.orders_lines` t2, UNNEST(t2.lines) lines WHERE lines.article.sub_category.label = t1.name)
ORDER BY 1