如何在PostgreSQL中动态提取嵌套JSON内列表的唯一值？

可以通过PostgreSQL的JSON函数实现这个需求，核心思路是利用jsonb_each遍历动态变化的duplicate_ids键，再提取嵌套的duplicates数组并展开去重。假设你的表名为your_table，result字段类型为jsonb（如果是json类型，可通过::jsonb转换），具体查询语句如下：

SELECT DISTINCT unnest(jsonb_array_text(value->'duplicates')) AS duplicates
FROM your_table,
     jsonb_each(result->'duplicate_ids')
WHERE id = 1;

语句解释： #

• result->'duplicate_ids'：提取JSON中duplicate_ids对象
• jsonb_each(...)：将duplicate_ids的键值对拆分成多行，每行对应一个动态键及其对应的值
• value->'duplicates'：从每个键值对的值中提取duplicates数组
• jsonb_array_text(...)：将JSON数组转换为文本数组
• unnest(...)：把数组展开为单行记录
• DISTINCT：去除重复的ID值
• WHERE id = 1：筛选目标ID的行

如果result是json类型，只需将语句中的jsonb_相关函数替换为json_，或者先转换为jsonb：

SELECT DISTINCT unnest(json_array_text((result::jsonb)->'duplicates')) AS duplicates
FROM your_table,
     jsonb_each((result::jsonb)->'duplicate_ids')
WHERE id = 1;

执行上述查询后，就能得到你期望的唯一duplicates值列表。

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