Redshift处理JSON异常：如何提取嵌套JSON中的id字段

1. 先清理转义字符并转换为可操作的SUPER类型 #

首先从SUPER字段里提取出educations对应的字符串值，先去掉转义的双引号，再用json_parse把它转成Redshift的SUPER类型——这样就能像操作正常JSON一样处理它了：

SELECT 
  json_parse(replace(json_extract_path_text(json_serialize(after), 'educations', 'string'), '\\"', '"')) AS educations_super
FROM <table>
WHERE json_extract_path_text(json_serialize(after), 'educations', 'string') IS NOT NULL 
  AND json_extract_path_text(json_serialize(after), 'educations', 'string') != '[]'
LIMIT 10;

2. 提取数组单个元素的id #

如果你的educations数组一般只有一条记录，可以直接通过索引提取id：

SELECT 
  json_extract_path_text(
    json_parse(replace(json_extract_path_text(json_serialize(after), 'educations', 'string'), '\\"', '"')),
    '[0].id'
  ) AS education_id
FROM <table>
WHERE json_extract_path_text(json_serialize(after), 'educations', 'string') IS NOT NULL 
  AND json_extract_path_text(json_serialize(after), 'educations', 'string') != '[]'
LIMIT 10;

3. 处理数组包含多个元素的情况 #

如果educations是有多条记录的数组，用unnest把数组拆成多行，再逐个提取每条记录的id：

SELECT 
  json_extract_path_text(edu, 'id') AS education_id
FROM <table>,
  unnest(
    json_parse(replace(json_extract_path_text(json_serialize(after), 'educations', 'string'), '\\"', '"'))
  ) AS edu
WHERE json_extract_path_text(json_serialize(after), 'educations', 'string') IS NOT NULL 
  AND json_extract_path_text(json_serialize(after), 'educations', 'string') != '[]'
LIMIT 10;

关键说明 #

• replace(..., '\\"', '"')：把字符串里的转义双引号（\"）替换成普通双引号，让原本转义的JSON内容恢复成合法格式。
• json_parse()：将清理后的字符串转换为SUPER类型，这样才能使用JSON相关函数提取嵌套字段。
• unnest()：当JSON是数组时，把数组展开为单独的行，方便处理多个元素的场景。

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