另一种选择是做CROSS JOIN而不是LEFT JOIN
#standardSQLWITH 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 itemsFROM OrdersCROSS JOIN( SELECt key ,title FROM Items) Order_itemsWHERe Order_items.key.id IN (SELECt item.key.id FROM UNNEST(items) AS item)GROUP BY __oid__
