从一个表中复制值，然后将其插入另一个JSONB字段

Question

请帮助我理解，如何从一个表中复制值并将其插入JSONB字段中的另一个？

additional_info是产品表中的JSONB字段。

dedication, stories, status我想在additional_info字段中复制的书籍表中的此字段。

我尝试过这样的尝试，但是很明显这不是正确的

INSERT INTO products (order_id, name, price, type, additional_info)
SELECT order_id, name, null, 'Book', '{"dedication": dedication, "stories": stories, "status": status }'
FROM books;

Answer 1

您需要使用jsonb_build_object。在SQL中没有特殊的含义，SQL字符串内部不需要"字面的字体：

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

Answer 2

类似的东西。虽然我不确定您如何使用" "。

INSERT INTO products (order_id, name, price, type, additional_info)
SELECT order_id, name, null, 'Book', json_build_object("dedication", dedication,  "stories", stories, "status", status) AS additional_info
FROM books;