SQL的一些小知识呀（8）-更新字段中的json

当数据库所存的某一个字段是json时，如何更新其中的某个value呢？

select data from table where id=1；
data字段所存为json数据，如下
{
  "key1": [
    { "m": "ABC", "s": 0 },
    { "m": "DEF", "s": 33 }
  ],
  "key2": [
    {
      "m1": 3,
      "m2": 4
    }
  ]
}

以上述数据为例，想要更新{ "m": "DEF", "s": 33 }，把s的值在原基础上加1，可通过下方语句实现：
第1步：拿出 "key1"

select "data" #> '{key1}' key1_json
from table;

得到的结果：

key1_json

第2步：把json数组进行拆解

select json_array_elements(key1_json) -> 'm' m, json_array_elements(key1_json) -> 's' s
from (
        select "data" #> '{key1}' key1_json
from table
     )  t1;

得到的结果：

拆解json
第3步：拿到{ "m": "DEF", "s": 33 },及单独的s值

select '{"m": "DEF", "s": ' || s || '}' total, s::TEXT::float s
from (
select json_array_elements(key1_json) -> 'm' m, json_array_elements(key1_json) -> 's' s
from (
        select "data" #> '{key1}' key1_json
from table
     )  t1）t2
where m::TEXT= '"DEF"';

得到的结果：

需要进行替换前的元素

第4步：更新s值

update table
set "data" = replace("data"::text, t3.total, '{"m": "DEF", "s": ' || s + 1 || '}')::json
from (
         select '{"m": "DEF", "s": ' || s || '}' total, s::TEXT::float s
         from (
                  select json_array_elements(key1_json) -> 'm' m, json_array_elements(key1_json) -> 's' s
                  from (
                           select key3.key #> '{key1}' key1_json
                           from (
                                    select ('{
  "key1": [
    { "m": "ABC", "s": 0 },
    { "m": "DEF", "s": 33 }
  ]
}')::json as key) key3) t1) t2
         where m::TEXT = '"DEF"') t3;

大结局，就完成更新了~

long time no see，自从上次emo之后，接了新的工作，其实与测试的相关性没有那么强，但是仍然是在不断接触学习新东西。甚至经历了漫长的疫情复工后，直到今天才有时间来这里更新...这个sql也是最近工作接触到的，觉得有点子难...虽然我当时是直接copy就可以了，但仍然想记录下来，就暂时达到一个能看懂的目的吧~

因为不常测试，最近对于新提交的东西甚至忘了测试...也是有些迷，接下来就不要酱紫了吧

下半年已经开始了，今天更新是一个好的开始，希望下半年能开心充实的过吧~~

自我记录，有错误欢迎指正~