如何从PostgreSQL的JSON数组列提取所有唯一字符串值？

问题回顾 #

数据表my_table结构如下：
| id | column1 | data |
|----+---------+------------|
| 1 | value_1 | ["A", "B"] |
| 2 | value_2 | ["B", "C"] |
| 3 | value_3 | ["A", "C"] |
| 4 | value_4 | ["E", "B"] |

需要提取data列JSON数组中所有唯一的字符串值，目标结果为"A"、"B"、"C"、"E"（注：示例中的"D"不存在于数据中，应为笔误）。

之前尝试的json_each_text语句报错，原因是该函数用于解析JSON对象的键值对，而非数组，且语法上未正确处理多行数据的横向展开。

正确解决方案 #

1. 针对JSON类型的data列 #

使用json_array_elements_text函数结合横向连接（LATERAL JOIN），将每行的JSON数组拆分为单个元素，再通过DISTINCT去重：

SELECT DISTINCT elem
FROM my_table
LATERAL JOIN json_array_elements_text(data) AS elem;

简化写法（PostgreSQL支持隐式横向连接）：

SELECT DISTINCT elem
FROM my_table, json_array_elements_text(data) AS elem;

2. 针对JSONB类型的data列 #

如果data列是jsonb类型（或需要转换为jsonb以提升性能），使用jsonb_array_elements_text：

SELECT DISTINCT elem
FROM my_table
LATERAL JOIN jsonb_array_elements_text(data::jsonb) AS elem;

若data本身就是jsonb类型，去掉::jsonb转换即可。

补充：统计每个值的出现次数 #

如果需要同时统计每个值在所有数组中的出现次数，可结合GROUP BY和COUNT：

SELECT elem, COUNT(*) AS occurrence_count
FROM my_table
LATERAL JOIN jsonb_array_elements_text(data::jsonb) AS elem
GROUP BY elem
ORDER BY occurrence_count DESC;

执行后会得到：

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