update json data in postgreSQL

工作中遇到的一个问题, 需要更新数据表里面的一个JSON字段, 最后的解决方法有点小巧妙, 整个求解的过程也蛮有意思, 记录下.

假定现有一张表 orders, orders 中有一个 double 类型的字段 amount 和一个 JSON 类型的 meta, 现在需要更新orders中的amount字段(amount = amount * 2) , 同时将更新前的 amount 旧值存在 meta 中.

假定更新前 orders 表的数据如下:

id	amount	meta
1	10	{“type”=> “market_order”, “user_id”=> “9”}

预期更新后的数据:

id	amount	meta
1	20	{ “type”=> “market_order”, “user_id”=> “9”, “changed_attributes”: { “amount” => “10”} }

postgresql version: 14.4

初看并不复杂, 通常情况下, 给JSON字段插入新的Key-value 这么来:

UPDATE orders
SET 
amount = amount * 2
meta = meta || '{"changed_attributes":{"amount":"10"}}';

但是这里 amount 的旧值不是常量, 需要改成 amount 字段的值. 如果直接把10换成amount 执行, 最后存在 meta 中的就是amount 这个字符串, 而不是 amount 的值10.

那该怎么办?

当时Google了半天也没有找到答案, 后面索性厚着脸皮请教了Data Team 的同事, 同事给到的解决方法有些意外, 巧用 || 字符串拼接符号即可.

把 10 换成 amount, 用 || 拼接起来.

meta = meta || '{"changed_attributes":{"amount":"'||amount||'"}}'

拆解下看得更清楚:

{"changed_attributes":{"amount":" || 
amount ||
"}}

但是运行的时候,会报错: the input string ended unexpectedly. 因为符号’的原因, 程序只解析 {"changed_attributes":{"amount": " 剩下的就不会解析. 我们可以用类型转换符包起来.

meta = meta || ('{"changed_attributes":{"amount":"'||amount||'"}}')::jsonb

最后的脚本:

UPDATE orders
SET 
amount = amount * 2
meta = meta || ('{"changed_attributes":{"amount":"'||amount||'"}}')::jsonb;

再次执行, OK!

[原来答案并不复杂, 把问题复杂化反而是给自己挖坑, 换个思路看问题, 其实可以很简单: P]

a nice teammate from Data Team [感谢大佬指点!]