Using json_to_record() to add a new row to a table PostgresSQL

Question

I have table_A with two columns:

create table table_a(id int, data json);

A row could be:

insert into table_a values
(1, '{"name": "kate", "cellphone": "000-000-0000", "address": "some text here"}');

I want to write a function that will take a row from table_A and insert a new row into table_B. Table_B has columns: id integer, name VARCHAR, cellphone VARCHAR, address TEXT, additional_info TEXT.

So my function should parse json field and put each value in the corresponding column of the Table_B (assume that all possible columns exist in Table_B).

It looks like I can use json_to_record(json), but how can I insert returned values into Table_B?

I am using PyGreSQL to connect with my database.

Answer 1

You should use the function in a lateral join. A column definition list should be added as the function returns record.

select id, r.*
from table_a
cross join lateral 
    json_to_record(data) 
    as r(name varchar, cellphone varchar, address text, additional_info text)

 id | name |  cellphone   |    address     | additional_info 
----+------+--------------+----------------+-----------------
  1 | kate | 000-000-0000 | some text here | 
(1 row) 

The insert statement may look like this:

insert into table_b
select id, r.*
from table_a
cross join lateral 
    json_to_record(data) 
    as r(name varchar, cellphone varchar, address text, additional_info text)

Answer 2

This worked for me

insert into customers(firstName, lastName,email)
select r."firstName", r."lastName",email
    from json_to_record('{"firstName": "fn","lastName":"ln","email":"[email protected]"}') 
    as r("firstName" varchar, "lastName" varchar, email text);