sql - 将 JSON 数据提取到简单的列中

Question

我继承了一个表，其中包含一个看起来像这样的列:

field_a::json
=============
{'a':['1', '2']}
{'b':['foo', 'bar']}
{'a':[null, '3']}

本质上，我想将其转换为在 View 或类似 View 中更有用的东西。只有一个键，数据始终是一个二元数组。这就是我的目标:

field|value1|value2
===================
a    |     1|     2
b    |   foo|   bar
c    |  null|     3

如果我不知道这里使用的任何键的名称，我该如何查询？这是在 PG11 上

Answer 1

您可以创建一个规范化数据的 View :

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)    

请注意， View 上的每个选择查询都意味着使用函数 jsonb_each() 查询源表，因此这不是最高性能的方法。要提高性能，您可以使用 materialized view并在对源表进行任何插入/更新后刷新它。

现场演示 db<>fiddle.