sql - 从数组中的数组中提取值并将其格式化为行

Question

数据的原始结构如下所示：

superarray:[
  array1:{
    subarray1:("id":"id1", "position":"1"),
    subarray2:("id":"id2", "position":"2"), 
    subarray3:("id":"id3", "position":"3"),
    subarray4:("id":"id4"), "position":"4")
  },  
  array2:{
    subarray5:("id":"id5", "position":"5"),
    subarray6:("id":"id6", "position":"6"), 
    subarray7:("id":"id7", "position":"7"),
    subarray8:("id":"id8", "position":"8") 
  },
  array3:{
    subarray9:("id":"id9, "position":"9")
  }
]

我可以使用以下方法提取最外层的数组FROM table, UNNEST(superarray) as super_array：

+------------+-------------------+------------------+----------------------+
| Row_number | super_array.array | super_array.ids  | super_array.position |
+------------+-------------------+------------------+----------------------+
|          1 | array1            | id1              |                    1 |
|            |                   | id2              |                    2 |
|            |                   | id3              |                    3 |
|            |                   | id4              |                    4 |
|          2 | array2            | id5              |                    5 |
|            |                   | id6              |                    6 |
|            |                   | id7              |                    7 |
|            |                   | id8              |                    8 |
|          3 | array3            | id9              |                    9 |
+------------+-------------------+------------------+----------------------+

但是我很难让内部水平给出如下内容：

+------------+-------------+------------+----------------+
| Row_number | array.array | array.ids  | array.position |
+------------+-------------+------------+----------------+
|          1 | array1      | id1        |              1 |
|          2 | array1      | id2        |              2 |
|          3 | array1      | id3        |              3 |
|          4 | array1      | id4        |              4 |
|          5 | array2      | id5        |              5 |
|          6 | array2      | id6        |              6 |
|          7 | array2      | id7        |              7 |
|          8 | array2      | id8        |              8 |
|          9 | array3      | id9        |              9 |
+------------+-------------+------------+----------------+

我将如何在UNNEST不出现重复列错误的情况下进行第二次操作？我是否需要INSERT INTO为每个单独的 id 添加新行？

标签： sqlarraysgoogle-cloud-platformgoogle-bigquery