使用json_extract提取JSON数组字段返回null及报错问题求解

问题根源 #

你的hits字段存储的是JSON数组而非JSON对象，json_extract(hits,'$.title')是在根层级下找title属性，自然无法返回结果。你所用的查询引擎（多为Presto/Trino/Athena类）原生JSON函数不支持递归通配符，也无法直接提取数组内所有元素的同名字段。

可用解决方法 #

方法1：UNNEST拆解数组提取（兼容所有版本） #

先将JSON数组转为SQL原生ARRAY(JSON)类型，再用UNNEST拆解后逐个提取字段，可按需返回行级结果或聚合为数组：

-- 1. 按行返回所有title值
SELECT json_extract_scalar(hit_item, '$.title') AS title
FROM 你的表名
-- 如果hits是varchar类型用json_parse转JSON，如果本身是JSON类型可直接CAST
CROSS JOIN UNNEST(CAST(json_parse(hits) AS ARRAY(JSON))) AS t(hit_item)

-- 2. 聚合为数组返回，符合你预期的[Facebook, Linkedin]格式
SELECT array_agg(json_extract_scalar(hit_item, '$.title')) AS title_list
FROM 你的表名
CROSS JOIN UNNEST(CAST(json_parse(hits) AS ARRAY(JSON))) AS t(hit_item)

方法2：json_query直接提取（仅支持Presto 0.216+/Trino/Athena 3.0+版本） #

新本版引擎支持用json_query做数组投影，可直接提取数组内所有元素的指定字段：

-- 返回JSON类型的title数组，转字符串数组可在外层加CAST(... AS ARRAY(VARCHAR))
SELECT json_query(hits, 'lax $[*].title' WITH ARRAY WRAPPER) AS title_list
FROM 你的表名

过往报错原因说明 #

• $**.title报错：引擎JSONPath实现不支持递归通配符**语法
• UNNEST报错：未将JSON数组转为SQL原生ARRAY类型，UNNEST仅支持拆解原生数组，不支持直接传入varchar或JSON类型值
• 双引号包裹路径报错：SQL中双引号用于标识列名/表名等标识符，JSON路径为字符串值，必须用单引号包裹，否则会被引擎识别为列名触发找不到列的错误

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