如何在BigQuery中提取JSON数组的指定多字段（upc和quantity）

需要从JSON数组中提取upc和quantity两个字段，尝试多字段提取时遇到错误：

ARRAY subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values

错误原因 #

ARRAY()构造器要求内部子查询只能返回单列，而你的查询同时返回了upc和quantity两列，违反了这个限制。

方案1：生成包含STRUCT的数组（保留数组结构） #

将提取的多个字段封装成STRUCT，作为数组的单个元素：

select array(
    select struct(
        json_extract_scalar(jj,"$.salesOffering.upc") as upc,
        cast(json_extract_scalar(jj,"$.quantity") as int64) as quantity -- 可选：将quantity转为数值类型
    ) 
    from unnest(json_array) as jj
) as sales_items
from `temp.test`

方案2：展开数组为单行多列（适合数据分析） #

如果不需要保留数组结构，直接将每个JSON元素拆分为单独的行展示字段：

select 
    json_extract_scalar(jj,"$.salesOffering.upc") as upc,
    cast(json_extract_scalar(jj,"$.quantity") as int64) as quantity
from `temp.test`, unnest(json_array) as jj

方案3：生成仅含目标字段的JSON对象数组 #

如果需要输出新的JSON数组（每个元素是仅保留upc和quantity的JSON对象）：

select array(
    select json_object(
        "upc", json_extract_scalar(jj,"$.salesOffering.upc"),
        "quantity", json_extract_scalar(jj,"$.quantity")
    ) 
    from unnest(json_array) as jj
) as trimmed_sales_json
from `temp.test`

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