使用json_extract_array提取嵌套JSON：已有字段返回null？

核心问题分析 #

你的查询返回全null有两个关键原因：

• 错误用json_extract_array处理对象：从JSON片段能看到，body.request.config是JSON对象（{}包裹），而非数组（[]）。但你调用json_extract_array(body, '$.request.config')，这个函数仅能提取数组类型元素，对对象直接返回null，后续unnest自然拿不到任何数据。
• 错误用json_extract_scalar处理数组：affiliationTypes、verificationTypes是数组类型，json_extract_scalar仅支持提取字符串、数字这类标量值，对数组会返回null。

修正后的查询语句 #

如果你想把affiliationTypes数组转为逗号分隔的字符串（即你期望的STUDENT_PART_TIME, EMPLOYEE, FACULTY, VETERAN, STUDENT_FULL_TIME, ACTIVE_DUTY），可以用以下查询：

SELECT 
  -- 将数组转为逗号分隔字符串
  string_agg(affiliation_type, ', ') as affiliation_types
  , json_extract(body, '$.request.config.organizationTypes') as organization_types
  , string_agg(verification_type, ', ') as verification_types
  , json_extract_scalar(body, '$.request.config.notifierIds') as notifier_ids
  , json_extract(body, '$.request.config.metadata') as metadata
  , json_extract_scalar(body, '$.accountId') as account_id
FROM `my database`
-- 提取affiliationTypes数组并展开
left join unnest(json_extract_array(body, '$.request.config.affiliationTypes')) as affiliation_type
-- 提取verificationTypes数组并展开
left join unnest(json_extract_array(body, '$.request.config.verificationTypes')) as verification_type
GROUP BY account_id, organization_types, notifier_ids, metadata
LIMIT 100;

如果不需要拆分数组，仅保留JSON数组格式，同时正确获取config内的字段，可简化为：

SELECT 
  json_extract(body, '$.request.config.affiliationTypes') as affiliation_types
  , json_extract(body, '$.request.config.organizationTypes') as organization_types
  , json_extract(body, '$.request.config.verificationTypes') as verification_types
  , json_extract_scalar(body, '$.request.config.notifierIds') as notifier_ids
  , json_extract(body, '$.request.config.metadata') as metadata
  , json_extract_scalar(body, '$.accountId') as account_id
FROM `my database`
LIMIT 100;

关键修正点说明 #

• 移除对request.config的unnest操作，直接通过JSON路径$.request.config.xxx访问嵌套字段。
• 针对数组类型字段：
  • 要转为逗号分隔字符串：先用json_extract_array提取数组，unnest后用string_agg聚合。
  • 要保留JSON数组格式：直接用json_extract提取。
• 标量类型字段（如accountId、notifierIds）继续使用json_extract_scalar。

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