英文:
Building a structure in BigQuery where an element of Array has Arrays associated with it
问题
我正在尝试在BigQuery中构建一个嵌套结构，类似于以下示例：
对于Items和SubItems，我尝试创建一个具有两个数组的结构：Items是一个字符串数组，而SubItems是一个数组的数组。
我尝试了以下方法：
WITH table AS (
SELECT Items, ARRAY_AGG(SubItems) AS SubItems FROM ...
GROUP BY Items
)
SELECT MacroItems, ARRAY_AGG(STRUCT(Items, SubItems))
FROM ...
JOIN ...
GROUP BY MacroItems
但我无法对一个数组进行ARRAY_AGG操作。
英文:
I am trying to build in BigQuery a nested structure like that:
For Items and SubItems I tried to create a structure with two arrays: Items is an Array of Strings, while SubItems is an Array of Arrays.
I tried this approach
WITH table AS (
SELECT Items, ARRAY_AGG(SubItems) AS SubItems FROM ...
GROUP BY Items
),
SELECT MacroItems, ARRAY_AGG(Items, SubItems)
FROM ...
JOIN ...
GROUP BY MacroItems
But I can't ARRAY_AGG an ARRAY
答案1
得分: 0
BigQuery不支持数组的数组，但您可以定义一个结构体数组的数组。
我认为您想要的是 ARRAY<STRUCT<items STRING, ARRAY<STRING> SubItems>>，然后您可以考虑以下内容。
WITH sample_table AS (
SELECT 'macro_item_1' MacroItems, 'item_1' Items, 'sub_item_1' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_1' Items, 'sub_item_2' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_3' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_4' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_5' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_3' Items, 'sub_item_6' SubItems
)
SELECT MacroItems, ARRAY_AGG(STRUCT(Items, SubItems)) Items FROM (
SELECT MacroItems, Items, ARRAY_AGG(SubItems) SubItems
FROM sample_table
GROUP BY 1, 2
) GROUP BY 1;
查询结果
英文:
BigQuery doesn't support an array of an array, but you can define an array of a struct of an array.
I think you want an ARRAY<STRUCT<items STRING, ARRAY<STRING> SubItems>>, then you can consider below.
WITH sample_table AS (
SELECT 'macro_item_1' MacroItems, 'item_1' Items, 'sub_item_1' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_1' Items, 'sub_item_2' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_3' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_4' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_2' Items, 'sub_item_5' SubItems UNION ALL
SELECT 'macro_item_1' MacroItems, 'item_3' Items, 'sub_item_6' SubItems
)
SELECT MacroItems, ARRAY_AGG(STRUCT(Items, SubItems)) Items FROM (
SELECT MacroItems, Items, ARRAY_AGG(SubItems) SubItems
FROM sample_table
GROUP BY 1, 2
) GROUP BY 1;
Query result
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论