postgresql - PostgreSQL插入到从JSON选择(postgresql insert into select from json)

Question

Is it possible to insert into a table from a json dictionary in PostgreSQL without specifying the columns?

(是否可以在不指定列的情况下从PostgreSQL的json字典插入表中？)

Given there is a table books_1:

(鉴于有一个表books_1：)

CREATE TABLE books_1 ( data json );
INSERT INTO books VALUES ('{ "name": "Book the First", "author": "Bob White" }');
INSERT INTO books VALUES ('{ "name": "Book the Second", "author": "Charles Xavier" }');
INSERT INTO books VALUES ('{ "name": "Book the Third", "author": "Jim Brown" }');

Which I want to insert into a table that has the exact same columns and datatypes, such as:

(我想插入具有完全相同的列和数据类型的表中，例如：)

CREATE TABLE books_2 ( name varchar(100), author varchar(100) );

Ideally I would execute:

(理想情况下，我将执行：)

INSERT INTO books_2
SELECT something_json(data) FROM books_1

Background: Using standard PostgreSQL I can easily make JSON table dumps.

(背景：使用标准PostgreSQL，我可以轻松进行JSON表转储。)

I am also able to import them, but they are imported as jsonb.

(我也能够导入它们，但是它们被作为jsonb导入。)

COPY ( SELECT ROW_TO_JSON(t) FROM (SELECT * FROM books_2) t) TO 'C:UsersPublicooks.json';

CREATE TABLE books_1 ( j jsonb );
COPY t FROM 'C:UsersPublicooks.json';

I want to re-import the json into 'regular' attributes, not json.

(我想将json重新导入“常规”属性，而不是json。)

I could do this by specifiying all the columns, but since there are a lot of tables and columns I really don't want to do that.

(我可以通过指定所有列来做到这一点，但是由于有很多表和列，我真的不想这样做。)

There should be no need as the columns are the same anyway.

(因为列是相同的，所以没有必要。)

I'm probably looking for a function that returns a list of columns based on the keys in the json.

(我可能正在寻找一个根据json中的键返回列列表的函数。)

I guess the order would have to be the same.

(我想顺序必须相同。)

But since the export is also done from PostgreSQL that is probably guaranteed.

(但是由于导出也是从PostgreSQL完成的，因此可以保证。)

  ask by svenema translate from so

Answer 1

You can use json_populate_record with the rowtype of the target table:

(您可以将json_populate_record与目标表的行类型一起使用：)

INSERT INTO books_2
SELECT rec.* FROM books_1, json_populate_record(null::books_2, books_1.data) rec;

( online demo )

(（ 在线演示 ）)