另一种选择是做CROSS JOIN而不是LEFT JOIN
#standardsql
WITH Orders AS (
SELECT 1 AS __oid__, ARRAY[STRUCT(STRUCT(1 AS id, "default" AS ns) AS key), STRUCT(STRUCT(2 AS id, "default" AS ns) AS key)] AS items UNION ALL
SELECT 2 AS __oid__, ARRAY[STRUCT(STRUCT(3 AS id, "default" AS ns) AS key), STRUCT(STRUCT(4 AS id, "default" AS ns) AS key)] AS items UNION ALL
SELECT 3 AS __oid__, ARRAY[STRUCT(STRUCT(5 AS id, "default" AS ns) AS key), STRUCT(STRUCT(6 AS id, "default" AS ns) AS key)] AS items
),
Items AS (
SELECT STRUCT(1 AS id, "default" AS ns) AS key, "#1.1" AS title UNION ALL
SELECT STRUCT(2 AS id, "default" AS ns) AS key, "#1.2" AS title UNION ALL
SELECT STRUCT(3 AS id, "default" AS ns) AS key, "#1.3" AS title UNION ALL
SELECT STRUCT(4 AS id, "default" AS ns) AS key, "#1.4" AS title UNION ALL
SELECT STRUCT(5 AS id, "default" AS ns) AS key, "#1.5" AS title UNION ALL
SELECT STRUCT(6 AS id, "default" AS ns) AS key, "#1.6" AS title
)
SELECT
__oid__
,ARRAY_AGG(Order_items) AS items
FROM Orders
CROSS JOIN(
SELECT
key
,title
FROM Items
) Order_items
WHERE Order_items.key.id IN (SELECT item.key.id FROM UNNEST(items) AS item)
GROUP BY __oid__
602392714
清零编程群