SQL中json_extract提取JSON数组字段返回null的解决方法求助

核心问题 #

你操作的hits是JSON数组结构，而非单个JSON对象，直接取根路径下的title字段不存在，因此json_extract(hits,'$.title')无返回结果。

可行修复方案 #

你之前使用unnest报错是因为没有先将JSON数组转换为SQL可识别的数组类型，按如下写法操作即可批量提取所有元素的对应字段：

1. 如果hits字段是字符串（varchar）类型存储的JSON数组：

SELECT 
    json_extract_scalar(hit_item, '$.title') AS title,
    json_extract_scalar(hit_item, '$.domain') AS domain
FROM 你的业务表名,
-- 先将字符串解析为JSON，再转成JSON数组，再展开为单行单个JSON对象
UNNEST(CAST(json_parse(hits) AS ARRAY(JSON))) AS t(hit_item)

2. 如果hits字段本身就是JSON类型：

SELECT 
    json_extract_scalar(hit_item, '$.title') AS title,
    json_extract_scalar(hit_item, '$.domain') AS domain
FROM 你的业务表名,
UNNEST(CAST(hits AS ARRAY(JSON))) AS t(hit_item)

之前操作报错的原因 #

• 双星号路径$**.title报错：你使用的SQL引擎（如Trino/Presto早期版本）不支持JSONPath的递归下降语法
• unnest报错：unnest函数仅支持对数组/行类型操作，你直接传入了varchar或JSON类型的字段，没有先转换为数组类型
• 双引号包裹路径报错：SQL引擎会将双引号包裹的内容识别为列名，JSON路径参数必须用单引号包裹

内容的提问来源于stack exchange，提问作者Kian O'Connor