0

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

Given there is a table 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. I am also able to import them, but they are imported as jsonb.

COPY ( SELECT ROW_TO_JSON(t) FROM (SELECT * FROM books_2) t) TO 'C:\Users\Public\books.json';

CREATE TABLE books_1 ( j jsonb );
COPY t FROM 'C:\Users\Public\books.json';

I want to re-import the json into 'regular' attributes, not 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. I guess the order would have to be the same. But since the export is also done from PostgreSQL that is probably guaranteed.

svenema
  • 1,766
  • 2
  • 23
  • 45
  • Why not simply export into a format that `copy` can process natively? –  Dec 01 '19 at 13:24
  • Reasons to use json is that I or others can easily change the data and store it under version control such as git. For the latter the format doesn't really matter, but for humans to update data the first is quite handy. – svenema Dec 01 '19 at 13:30
  • 1
    you can store a CSV file in git just as well –  Dec 01 '19 at 13:30

1 Answers1

1

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

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

(online demo)

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Perfect. Funny thing, I was trying this, but used the wrong version jsonb vs. json of this function. Thanks a lot! – svenema Dec 01 '19 at 18:13