在PostgreSQL 9.6中，扩展充满简单JSON字典的JSONB列的最简单方法是什么？

Question

说，我有一个带有 JSONB 列的表json_table，json_field .此列中的每个元素都是一个简单的字典，例如，

{'first_field': 2 , 'second_field': 42}

有没有办法创建一个新表，如果字典变成列？

我目前的做法如下：

CREATE TABLE normal_table ... first_field, second_field ... etc;
INSERT INTO normal_table (
    id,
    first_field,
    second_field,
    ...
)
SELECT
    id,
    json_field->>'first_field',
    json_field->>'second_field',
    ...
FROM json_table;

有没有办法做类似的事情？

SELECT
    id,
    expand_json_dict(json_field)
FROM json_table;

还是类似的简洁方法？JSONB 列有很多我想扩展的字段，查询变得笨拙。我实际上已经制作了一个生成创建/插入脚本的 Python 函数。不过，我希望有一个不错的PostgreSQL方法来做到这一点。

有什么想法吗？

编辑

以下是基于此处反馈的工作解决方案。谢谢大家。

drop table if exists json_table;
create table json_table (
    id int,
    json_field jsonb
);
insert into json_table VALUES
    (2, ('{"second_field": 43, "first_field": 3}'::jsonb)),
    (1, ('{"first_field": 2 , "second_field": 42}'::jsonb));
drop table if exists normal_table;
create table normal_table (
    id int,
    first_field int,
    second_field int
);
insert into normal_table
select (
    jsonb_populate_record(
        null::normal_table,
        jsonb_set(json_field, '{id}', id::text::jsonb)
    )
).*
from json_table;
select * from normal_table;

Answer 1

使用 normal_table 类型作为 jsonb_populate_record 函数的基本类型：

create table normal_table (
    id int,
    first_field int,
    second_field int
);
with json_table (json_field) as ( values
    ('{"first_field": 2 , "second_field": 42}'::jsonb)
)
select (jsonb_populate_record(null::normal_table, json_field)).*
from json_table
;
 id | first_field | second_field                                                                                                                                          
----+-------------+--------------                                                                                                                                         
    |           2 |           42

如果需要生成要插入的id，请使用jsonb_set：

with json_table (json_field) as ( values
    ('{"first_field": 2 , "second_field": 42}'::jsonb),
    ('{"first_field": 5 , "second_field": 1}')
)
select (
    jsonb_populate_record(
        null::normal_table,
        jsonb_set(json_field, '{id}', (row_number() over())::text::jsonb)
    )
).*
from json_table
;
 id | first_field | second_field 
----+-------------+--------------
  1 |           2 |           42
  2 |           5 |            1

Answer 2

您可以创建一个反映键的类型(记录(，然后使用 json_populate_record ：

create type my_type as (first_field varchar, second_field varchar);
SELECT id, (json_populate_record(null::my_type, json_field)).*
FROM json_table;

如果 JSON 文档中存在类型中不存在的键，则直接忽略它们。如果类型定义中的字段在 JSON 文档中没有匹配项，则将null这些字段