PostgreSQL如何拆分jsonb数组为多行并提取去重name属性值

核心逻辑 #

你的实现思路完全正确：先把每行存储的JSON数组打平为独立的对象行，提取每个对象的name属性后做去重即可。之前使用jsonb_array_elements运行失败，绝大多数情况是列类型和函数不匹配、缺少横向关联展开语法导致的。

具体写法 #

先做前提约定：

• 假设目标表名为target_table，存储JSON数组的列名为json_list_col
• 如果列类型是jsonb，使用jsonb_array_elements函数；如果列类型是json，替换为json_array_elements函数即可，混用两类函数会直接报类型不匹配错误。

1. 数组打平实现（输出name+date逐行对应结果） #

通过LATERAL关联对每行的数组做逐元素展开，写法如下：

SELECT
  elem ->> 'name' AS name,
  elem ->> 'date' AS date
FROM target_table,
LATERAL jsonb_array_elements(json_list_col) AS elem;

执行后输出结构和你给出的预期打平结果完全一致。

2. 提取不重复name值 #

在提取name字段时增加DISTINCT关键字做去重即可：

SELECT DISTINCT elem ->> 'name' AS unique_name
FROM target_table,
LATERAL jsonb_array_elements(json_list_col) AS elem;

异常场景兼容 #

如果表中存在列值为NULL、或者值不是合法数组格式的脏数据，可以增加类型判断过滤，避免查询报错：

SELECT DISTINCT elem ->> 'name' AS unique_name
FROM target_table,
LATERAL jsonb_array_elements(json_list_col) AS elem
WHERE jsonb_typeof(json_list_col) = 'array';

如果是json类型的列，把判断函数替换为json_typeof即可。

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