如何在Presto中提取嵌套JSON字符串中的所有id值

由于你的JSON结构是顶层为对象（键为"0","1","3"），而非数组，无法直接用数组通配符提取所有id，需要先拆分顶层键再逐个提取，以下是主流SQL引擎的实现方式：

BigQuery（适配你当前使用的JSON_EXTRACT_SCALAR函数） #

SELECT 
  JSON_EXTRACT_SCALAR(api_response, CONCAT('$.', obj_key, '.id')) AS extract_id
FROM 
  your_table,
  UNNEST(JSON_OBJECT_KEYS(api_response)) AS obj_key;

• 逻辑：JSON_OBJECT_KEYS(api_response)获取所有顶层键（"0","1","3"），UNNEST将这些键拆分为单独行，再通过CONCAT拼接每个子对象的路径，逐个提取id。

MySQL #

使用JSON_TABLE将JSON对象转换为关系表：

SELECT jt.extract_id
FROM your_table,
     JSON_TABLE(
       JSON_KEYS(api_response),
       '$[*]' COLUMNS(
         obj_key VARCHAR(10) PATH '$',
         extract_id INT PATH CONCAT('$.', obj_key, '.id')
       )
     ) jt;

PostgreSQL #

借助jsonb_each拆分JSON对象的键值对：

SELECT (value ->> 'id')::INT AS extract_id
FROM your_table,
     jsonb_each(api_response::jsonb);

• 逻辑：jsonb_each将JSON对象拆分为(key, value)行，直接从每个value中提取id字段并转换为整数类型。

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