PostgreSQL如何从存储JSON的TEXT字段提取唯一电影分类数据

实现方案 #

你存储的movieGenres是单引号包裹的非标准JSON文本，需要先做格式转换再拆分数组去重，直接用以下SQL即可：

方案1：直接输出去重后的分类JSON对象

SELECT DISTINCT genre
FROM (
    -- 替换单引号为双引号转标准JSON，再拆分数组为单行对象
    SELECT jsonb_array_elements(REPLACE(movieGenres, '''', '"')::jsonb) AS genre
    FROM 你的实际表名 -- 替换为你自己的表名
) t
ORDER BY (genre->>'id')::int;

方案2：输出拆分后的分类ID和名称两列（更适合后续使用）

SELECT DISTINCT
    (genre->>'id')::int AS genre_id,
    genre->>'name' AS genre_name
FROM (
    SELECT jsonb_array_elements(REPLACE(movieGenres, '''', '"')::jsonb) AS genre
    FROM 你的实际表名
) t
ORDER BY genre_id;

注意事项 #

• 如果使用的PostgreSQL版本不支持JSONB，将所有jsonb关键字替换为json、jsonb_array_elements替换为json_array_elements即可正常运行
• 若存在格式非法的脏数据，可在子查询中添加WHERE jsonb_valid(REPLACE(movieGenres, '''', '"'))过滤非法记录避免报错
• 后续这类JSON查询需求多的话，建议直接将字段类型改为JSONB，查询效率更高还支持索引优化

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