BigQuery：在标准SQL中使用重复的/数组STRUCT字段加入联接吗？

吴维群

我基本上有两个表，Orders和Items。因为这些表是从Google Cloud
0 q5 I, W5 W/ |' {Datastore备份文件导入，引用不简单ID但是一对一的关系，包括id字段表示我唯一想匹配的实际情况ID。一对多关系（REPEATED），使用这种模式ARRAY。
我可以使用LEFT OUTER JOIN查询一对一关系，我也知道如何连接非重复结构和重复字符串或整数，但我很难使用 重复的结构
( a, U7 y# ~( ?: m, D5 Z! d! r实现类似的联接查询。
一个订单包 一项    ：
: n1 o: k* F" ]#standardSQLWITH Orders AS (  SELECT 1 AS __oid__,STRUCT(STRUCT(2 AS id,"default" AS ns) AS key) AS item UNION ALL   SELECT 2 AS __oid__,STRUCT(STRUCT(4 AS id,"default" AS ns) AS key) AS item UNION ALL   SELECT 3 AS __oid__,STRUCT(STRUCT(6 AS id,"default" AS ns) AS key) AS item),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__  ,Order_item AS itemFROM OrdersLEFT OUTER JOIN(  SELECT     key    ,title  FROM Items) Order_itemON Order_item.key.id = item.key.id结果(按预期工作):
5 `0 r$ a6 d0 b: O8 \4 q8 y    ----- --------- -------------- ------------- ------------ | Row | __oid__ |  item.key.id | item.key.ns | item.title | ----- --------- -------------- ------------- ------------ |   1 |       1 |       |     default |       #1.2 | ----- --------- -------------- ------------- ------------ |   2 |        |            4 |     default |       #1.4 | ----- --------- -------------- ------------- ------------ |   3 |       3 |       |     default |       #1.6 | ----- --------- -------------- ------------- ------------ 类似的查询，但这次有 很多    物品订单：
" t7 D" p& G7 x7 W/ N#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__  ,Order_items AS itemsFROM OrdersLEFT OUTER JOIN(  SELECT     key    ,title  FROM Items) Order_itemsON Order_items.key.id IN (SELECT item.key.id FROM UNNEST(items) AS item)错误：    不支持内部连接谓词IN子查询。
' D/ Z: ~5 Z5 f我实际预期的结果是:
    ----- --------- -------------- ------------- ------------ | Row | __oid__ |  item.key.id | item.key.ns | item.title | ----- --------- -------------- ------------- ------------ |   1 |       1 |            1 |     default |       #1.1 ||     |         |       |     default |       #1.2 | ----- --------- -------------- ------------- ------------ |   2 |        |       |     default |       #1.3 ||     |         |       |     default |       #1.4 | ----- --------- -------------- ------------- ------------ |   3 |       3 |          5 5 5 5                                 5 5 5 5  5  5                                                                                                                                                       5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5                   5 5 5 5 5 5 5 5 5 5 5 5 5   5 5            5 5  5  5                         5   5                         5                                                                                                                                                                                                              |     default |       #1.5 ||     |         |       |     default |       #1.6 | ----- --------- -------------- ------------- ------------ 如何更改第二个查询以获得预期结果？
                                                               
7 I* k- w# Y- D# }. d1 y8 m    解决方案: