我继承了一个表，该表包含一个看起来像这样的列：
field_a::json
=============
{'a':['1','2']}
{'b':['foo','bar']}
{'a':[null,'3']}
本质上，我想将其转换为在视图或类似视图中更有用的东西。永远只有一个键，并且数据始终是两个元素的数组。这是我的目标：
field|value1|value2
===================
a | 1| 2
b | foo| bar
c | null| 3
如果我不知道此处正在播放的任何键的名称，我该如何查询？这是在PG11上
您可以创建一个标准化数据的视图：
create view view_of_my_table as
select id,key::text,value->>0 as value1,value->>1 as value2
from my_table
cross join jsonb_each(field_a)
select *
from view_of_my_table
id | key | value1 | value2
----+-----+--------+--------
1 | a | 1 | 2
2 | b | foo | bar
3 | c | | 3
(3 rows)
请注意，视图上的每个select查询都意味着使用函数jsonb_each()查询源表，因此这不是最有效的方法。为了提高性能，您可以使用materialized view并在源表的任何插入/更新后刷新它。
db<>fiddle.中的实时演示
,可能效率低下，但确实可以做到：
DROP TABLE IF EXISTS source_table;
CREATE TEMP TABLE source_table ( field_a JSONB );
DROP TABLE IF EXISTS target_table;
CREATE TEMP TABLE target_table ( field TEXT,value1 JSONB,value2 JSONB );
INSERT INTO source_table VALUES
('{"a":["1","2"]}'),('{"b":["foo","bar"]}'),('{"a":[null,3]}');
INSERT INTO target_table
SELECT
tmp.obj->>'key',COALESCE( TO_JSONB(((tmp.obj->'value')::JSONB)->0),'null' ),COALESCE( TO_JSONB(((tmp.obj->'value')::JSONB)->1),'null' )
FROM (
SELECT TO_JSON(x) obj FROM (SELECT JSONB_EACH(field_a) x FROM source_table) AS x
) AS tmp;
SELECT * FROM target_table;
产量：
field|value1|value2
===================
"a" | "1"| "2"|
"b" | "foo"| "bar"|
"a" | null| 3|
其中value1和value2是JSONB列，而null被保留为JSONB。
此外，即使您指定每个对象仅包含一个键，上述解决方案仍将起作用，无论其包含多少个键。
PosgreSQL 9.5 +