struct - 如何从标准 SQL 中的数组结构返回结构数组？

Question

我的表上有一个非重复记录列，我想访问它。在这条记录上，有几个重复的值。

所以它是一个RECORD，像这样:

STRUCT<item ARRAY<STRING> unit_cost ARRAY<INT64> quantity ARRAY<INT64>> as costs

例如。数据可能代表:

item ['cheese', 'ham', 'salad']
unit_cost [2, 5, 8]
quantity [1, 2, 1]

所以我想将它作为一个更好的数据结构返回，一个结构数组:

[
  {'item': 'cheese', 'unit_cost': 2, 'quantity': 1}, 
  {'item': 'ham', 'unit_cost': 5, 'quantity': 2}
  {'item': 'salad', 'unit_cost': 8, 'quantity': 1}
]

我试过:

SELECT ARRAY_AGG(costs)

但是结果是

            [
              {
                "item": ['cheese', 'ham', 'salad'],
                "unit_cost": [2, 5, 8],
                "quantity": [1, 2, 1]
              }
            ]

这不是我预期的返回结果。

是否可以使用一些方法从多个 ARRAY 的 STRUCT 到多个 STRUCT 的 ARRAY在这里巧妙地使用标准 SQL？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT 
  ARRAY(
    SELECT AS STRUCT item, unit_cost, quantity
    FROM UNNEST(costs.item) item WITH OFFSET
    LEFT JOIN UNNEST(costs.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(costs.quantity) quantity WITH OFFSET USING(OFFSET)
  ) AS costs
FROM `project.dataset.table`   

如果应用于您问题中的示例数据 - 结果是(在 JSON View 中)

[
  {
    "costs": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      }
    ]
  }
]