如何从TO_JSON_STRING生成的JSON字符串中提取salary字段的值

问题原因 #

d_list是通过ARRAY_AGG聚合后转成的JSON字符串，根节点是数组结构，数组内的每个元素才是包含name/salary/deptno的对象，你直接用$.salary是从根对象查找字段，自然匹配不到返回null。

正确提取方案 #

方案1：提取所有salary值生成数组

如果你需要获取该id下所有的salary值，可通过解析JSON数组遍历获取：

SELECT 
  id,
  -- 遍历JSON数组提取每个元素的salary字段
  ARRAY(
    SELECT CAST(JSON_EXTRACT_SCALAR(elem, '$.salary') AS INT64) -- 可根据salary实际类型调整转换类型
    FROM UNNEST(JSON_EXTRACT_ARRAY(d_list)) AS elem
  ) AS salary_arr
FROM 你的结果表名

方案2：提取数组指定位置的salary值

如果确定数组内只有1个元素，或需要取固定位置的salary，可直接通过数组下标访问：

SELECT 
  id,
  -- $[0]代表取数组第一个元素，后续数字可替换为你需要的下标
  CAST(JSON_EXTRACT_SCALAR(d_list, '$[0].salary') AS INT64) AS salary
FROM 你的结果表名

优化建议 #

如果你的业务场景不需要保留完整的JSON结构，可在最初的聚合步骤直接生成salary数组，省去后续JSON解析的性能开销：

select 
  id, 
  TO_JSON_STRING(ARRAY_AGG(STRUCT(name,salary,deptno))) as d_list,
  ARRAY_AGG(salary) as salary_arr -- 直接聚合salary数组，无需后续解析JSON
from `project.dataset.sample_tab` 
group by 1

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