Power BI中从Google BigQuery的JSON列提取data.name字段求助

问题背景 #

你的BigQuery表中有一个data列，类型为：

ARRAY<STRUCT<
  id STRING,
  status STRING,
  name ARRAY<STRUCT<code STRING, value STRING>>,
  description ARRAY<STRUCT<code STRING, value STRING>>
>>

需要提取其中的data.name字段（保留ARRAY<STRUCT<code STRING, value STRING>>格式）并命名为data.name，但原SQL使用MAX(name.value)仅返回单个值，不符合需求。

正确SQL写法 #

场景1：提取所有data元素的完整name数组 #

如果不需要过滤name.code，仅提取每个data元素的name数组：

• 拆分每个data元素为单独行，同时提取name字段：

SELECT
  t.* EXCEPT(data),
  data.name AS `data.name`
FROM
  your_table t
CROSS JOIN UNNEST(t.data) AS data

• 保留原表行结构，合并所有data元素的name数组为一个大数组：

SELECT
  t.* EXCEPT(data),
  ARRAY_CONCAT_AGG(data.name) AS `data.name`
FROM
  your_table t
CROSS JOIN UNNEST(t.data) AS data
GROUP BY t.* EXCEPT(data)

场景2：提取code为'en'的name条目（保留数组结构） #

如果需要过滤出name.code = 'en'的条目，同时保留ARRAY<STRUCT>格式，使用数组构造子查询：

SELECT
  t.* EXCEPT(data),
  ARRAY(
    SELECT AS STRUCT name.code, name.value
    FROM UNNEST(t.data) AS data_element
    CROSS JOIN UNNEST(data_element.name) AS name
    WHERE name.code = 'en'
  ) AS `data.name`
FROM
  your_table t

原SQL问题说明 #

你之前的SQL使用MAX(name.value)会将符合条件的value聚合为单个字符串值，而非保留ARRAY<STRUCT>的数组结构，因此无法得到预期的输出格式。

内容的提问来源于stack exchange，提问作者Mounica