如何在Presto 0.282中提取JSON数组中的信息？

首先要指出：你给出的['{month_01=0','date_last_activity=2023-01-01','month_02=0','month_03=0','month_04=0','month_05=0}']并非标准JSON格式，标准JSON要求键名、字符串值必须用双引号包裹，键值对之间用逗号分隔，对象需用完整大括号包裹。正确的标准JSON格式应为：

[{"month_01": 0, "date_last_activity": "2023-01-01", "month_02": 0, "month_03": 0, "month_04": 0, "month_05": 0}]

以下分两种情况给出提取方案：

1. 处理标准JSON数组 #

提取单个字段 #

使用json_extract_scalar提取标量值（字符串、数字等），语法为json_extract_scalar(列名, 'JSON路径')：

• 提取日期字段：

SELECT json_extract_scalar(json_column, '$[0].date_last_activity') AS date_last_activity
FROM your_table;

• 提取月份数字（需转类型）：

SELECT cast(json_extract_scalar(json_column, '$[0].month_01') AS integer) AS month_01
FROM your_table;

解释：$代表整个JSON对象，[0]指向数组第一个元素，.字段名定位目标键值对。

展开数组提取多元素 #

若数组包含多个对象，用unnest配合json_array_elements展开每行对应一个数组元素：

SELECT 
    json_extract_scalar(element, '$.date_last_activity') AS date_last_activity,
    cast(json_extract_scalar(element, '$.month_01') AS integer) AS month_01,
    cast(json_extract_scalar(element, '$.month_02') AS integer) AS month_02
FROM your_table,
     unnest(json_array_elements(json_column)) AS t(element);

2. 处理非标准JSON格式 #

如果json_column存储的确实是你给出的非标准格式，先通过字符串替换转为标准JSON再提取：

SELECT 
    json_extract_scalar(standard_json, '$[0].date_last_activity') AS date_last_activity
FROM (
    SELECT 
        replace(
            replace(
                replace(json_column, '=', '":"'),
                '{', '{"'),
            '}', '"}'
        ) AS standard_json
    FROM your_table
) t;

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