Redshift提取JSON数组字段数据报错，如何获取指定字段内容？

错误原因 #

你存储的JSON使用了单引号，但标准JSON要求键名和字符串值必须用双引号，Redshift的JSON解析函数仅兼容标准JSON格式，这是导致解析报错的核心原因。

解决方案 #

先将列中的单引号替换为双引号，转换为标准JSON格式，再展开数组并提取目标字段：

完整SQL语句 #

SELECT
    JSON_EXTRACT_PATH_TEXT(json_obj, 'IDIndex') AS IDIndex,
    JSON_EXTRACT_PATH_TEXT(json_obj, 'History') AS History
FROM
    (
        SELECT
            UNNEST(JSON_PARSE(REPLACE(REPLACE(<your_column_name>, '''', '"'), '""', '"'))) AS json_obj
        FROM
            <your_table_name>
    ) t;

语句说明 #

• REPLACE(<your_column_name>, '''', '"')：将列中所有单引号替换为双引号，转换为标准JSON格式；第二个REPLACE用于处理替换后可能出现的双引号重复问题。
• JSON_PARSE(...)：将处理后的字符串解析为JSON数组。
• UNNEST(...)：把JSON数组拆分成单独的JSON对象行，实现数组的横向展开。
• JSON_EXTRACT_PATH_TEXT(json_obj, '字段名')：从每个JSON对象中提取指定字段的值。

预期输出 #

执行上述语句后，将得到你需要的结果格式：

IDIndex  History
            
0001       4
0002       2
0003       2

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