Search code examples
postgresqljsonb

Copy values from one table and insert it to another jsonb field

Please help me understand, how to copy values from one table and insert it to another in jsonb field ?

additional_info is jsonb field in products table.

dedication, stories, status this fields from books table i want to copy in additional_info field.

I tried like that, but obvious this isn't correct

INSERT INTO products (order_id, name, price, type, additional_info)

SELECT order_id, name, null, 'Book', '{\"dedication\": dedication, \"stories\": stories, \"status\": status }'

FROM books;
Solution

  • You need to use jsonb_build_object. And \ has no special meaning in SQL, there is no need for \" inside a SQL string literal:

    INSERT INTO products (order_id, name, price, type, additional_info)
select order_id, 
       name, 
       null, 
       'Book', 
       jsonb_build_object('dedication', dedication, 'stories', stories, 'status', status)
from books