从包含PostgreSQL中的JSON值的列中提取列表或目录

发布于12月14日

我有一个数据表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"
"D"
"E"

我try 了Postgresql文档中的一些json函数，比如:

select * from json_each_text(select data from my_table as foo)

但我在"from"(第二个"from")的位置或附近遇到了语法错误.

我还试着go 掉括号外的别名，以影响第一个‘SELECT’.到目前为止，没有一件事是成功的.

当我这样做时，我可以访问列中包含的信息并提取单个值或计数:

select count(*) from my_table
  where data::jsonb ? 'A' = true;

结果是‘2’，这是预期的.

如何获取json列中包含的值的列表？

推荐答案

这可以使用json_array_elements(如果数据类型为jsonb，则使用jsonb_array_elements)将json(B)数组转换为行:

select distinct t.value::text
from my_table
cross join json_array_elements(data) t

对于这个简单的数据:

create table my_table (
  id int,
  column1 varchar(20),
  data json
);

insert into my_table values
(1,  'value_1',  '["A", "B"]'),
(2,  'value_2',  '["B", "C"]'),
(3,  'value_3',  '["A", "C"]'),
(4,  'value_4',  '["E", "B"]');

结果: