Presto如何从JSON数组列提取包含指定值的JSON对象

你之前写的SQL存在3个核心问题，无法得到预期结果：

• JSONPath是独立的路径匹配语法，不支持在路径字符串内嵌套SQL SELECT子句，这部分写法完全不符合语法规则
• 用LIKE '%357%'对整个JSON文本做模糊匹配可靠性极差，若JSON其他字段（如value字段、其他未展示的扩展字段）出现"357"字符串会触发误匹配，无法精准定位到items数组内的编码
• ::text是PostgreSQL的类型转换语法，Presto中类型转换需使用CAST(xxx AS 目标类型)格式，且该场景下本身不需要做文本级别的模糊匹配。

推荐写法（简洁版，适用于Presto 0.201及以上版本） #

直接用JSON_QUERY函数配合带过滤谓词的JSONPath即可，因为你明确每个JSON数组中仅存在1个匹配项，路径直接取匹配结果的第一个元素，最终返回单个JSON对象，不会带数组外壳：

SELECT
  JSON_QUERY(
    column_a,
    'lax $[*] ? (@.items[*] == "357") [0]'
  ) AS column_B
FROM 你的表名

路径参数说明：

• lax代表宽松匹配模式，遇到部分结构缺失的异常数据不会直接抛出错误，会返回空值
• $[*]代表遍历根节点JSON数组下的所有元素
• ? (@.items[*] == "357")是过滤条件，筛选出当前对象下items数组中存在值等于"357"的元素
• 末尾的[0]代表取过滤后结果数组的第一个元素，直接返回单个JSON对象，符合你需要的非数组格式要求。

兼容写法（适用于所有Presto版本） #

如果你的Presto版本较低不支持JSONPath过滤谓词，可以通过数组拆解+条件过滤的方式实现：

SELECT
  obj AS column_B
FROM 你的表名
CROSS JOIN UNNEST(CAST(json_parse(column_a) AS ARRAY(JSON))) AS t(obj)
WHERE json_array_contains(
  json_extract(obj, '$.items'),
  CAST('357' AS JSON)
)

逻辑说明：

1. 先通过json_parse把column_a的JSON字符串解析成JSON类型，再转成JSON数组类型
2. 用UNNEST把数组中的每个JSON对象拆成单独的行
3. 用json_array_contains函数判断每个对象的items数组是否包含编码"357"，过滤出符合条件的对象即可。因为原数据每个数组仅存在1个匹配项，最终每个原数据行只会返回1个对应的JSON对象，和预期结果一致。

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