现有表1:
select * from jinbo.jsonb_table1;
id | extend
----+---------------------------------
1 | {"id": "1", "city": "beijing"}
2 | {"id": "2", "city": "shanghai"}
3 | {"id": "3", "city": "xian"}
4 | {"id": "4", "city": "nanjing"}
5 | {"id": "5", "city": "yulin"}
(5 rows)
现有表2:
select * from jinbo.jsonb_table2;
id | jsonb_table1_id | content
----+-----------------+----------------
1 | 1 | {"index": "1"}
2 | 2 | {"index": "2"}
3 | 3 | {"index": "4"}
4 | 4 | {"index": "5"}
5 | 5 | {"index": "8"}
(5 rows)
如果要给 jsonb_table1的 extend 增加一列 index, 且index值 要取 jsonb_table2 jsonb_table1_id 的值等于 jsonb_table1 的值 的conten 字段里的index 字段。
开始想整一个脚本,固然可以解决,最后查询了一下 update 语言(\h update ) 且在DBA的指导下可以使用如下sql 完成:
update
jinbo.jsonb_table1
SET
extend = extend::jsonb || ('{"index":"' || t2.index || '"}')::jsonb
from
( select jsonb_table1_id, content->>'index' as index from jinbo.jsonb_table2 ) as t2
where
id = t2.jsonb_table1_id;
#或
update
jinbo.jsonb_table1 t1
SET
extend = extend::jsonb || ('{"test":"' || ( select content->>'index' from jinbo.jsonb_table2 where jsonb_table1_id = t1.id ) || '"}')::jsonb