Presto 0.273如何提取JSON对象数组中的目标元素

核心要实现的JSON数组转逐行独立JSON对象需求，不需要提前知道元素索引，通过Presto原生的数组展开能力即可完成，全程在SQL侧处理完后再返回结果，可直接规避Python侧Series嵌套类型的报错问题。

具体实现步骤 #

• 首先修正初始JSON路径语法错误
  你最初写的JSON_EXTRACT(library_data, '.$books')不符合JSONPath规范，根节点必须以$开头，正确的书籍数组提取路径为$.books。
• 将JSON格式的数组转为Presto原生数组类型
  JSON_EXTRACT返回的是JSON类型值，无法直接展开，需要先显式转换为SQL数组类型：CAST(JSON_EXTRACT(library_data, '$.books') AS ARRAY(JSON))，转换后数组内的每个元素都是独立的书籍JSON对象。
• 通过UNNEST将数组打平为多行记录
  这一步不需要依赖数组索引，会自动遍历数组内所有元素，每个元素单独生成一行结果，完整SQL如下：

SELECT
  book_obj
FROM
  -- 替换成你的实际表名
  library_table,
  UNNEST(
    CAST(JSON_EXTRACT(library_data, '$.books') AS ARRAY(JSON))
  ) AS t(book_obj)

执行后返回的每一行book_obj都是单个书籍JSON对象，结构和你给出的数组内元素完全一致，Python侧读取后可以直接遍历，不会出现嵌套Series的类型问题。

之前方案报错的原因说明 #

• 固定取$[0]的方案只能拿到数组第一个元素，自然无法适配索引位置未知的全量对象提取场景。
• array_join触发类型转换报错，是因为数组内元素是JSON类型而非字符串类型，如果确实需要将所有对象拼成逗号分隔的单字符串返回（不推荐，后续解析成本更高），可以先对数组元素做类型转换再拼接：

SELECT array_join(
  transform(
    CAST(JSON_EXTRACT(library_data, '$.books') AS ARRAY(JSON)),
    x -> CAST(x AS VARCHAR)
  ),
  ', '
)

可选优化：直接在Presto侧完成requestor筛选 #

如果最终目的是筛选指定requestor的记录，完全不需要拉取全量书籍数据到Python再过滤，可以直接在SQL中加条件，执行效率更高：

SELECT
  book_obj
FROM
  library_table,
  UNNEST(
    CAST(JSON_EXTRACT(library_data, '$.books') AS ARRAY(JSON))
  ) AS t(book_obj)
WHERE
  -- 直接提取对象内的requestor字段做匹配
  JSON_EXTRACT_SCALAR(book_obj, '$.requestor') = '替换为你要筛选的目标requestor值'

注意：你给出的JSON示例里requestor字段后少了逗号，实际数据如果存在JSON格式语法错误，需要先做数据清洗再执行提取，否则会触发JSON解析失败报错。

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