当当前行包含多个值时，BigQuerySQLJSON会返回额外的行

使用array模式将多个值合并为一个数组，然后使用JSON_EXTRACT_ARRAY函数从数组中提取所需的值。

示例代码：

WITH sample_table AS ( SELECT 1 AS id, '{"fruit": ["apple", "banana"], "color": "red"}' AS json_data UNION ALL SELECT 2 AS id, '{"fruit": ["grape"], "color": "purple"}' AS json_data UNION ALL SELECT 3 AS id, '{"fruit": ["orange", "mango"], "color": "orange"}' AS json_data ) SELECT id, color, fruit FROM ( SELECT id, JSON_EXTRACT_SCALAR(json_data, '$.color') AS color, JSON_EXTRACT_ARRAY(json_data, '$.fruit') AS fruit_array FROM sample_table ) , UNNEST(fruit_array) AS fruit ORDER BY id, fruit

上述查询将返回以下结果：