时间戳

首页 > 解决方案 > 如何使用 postgreSQL 更新 JSONB 数组上的多个值

json - 如何使用 postgreSQL 更新 JSONB 数组上的多个值

问题描述

今天我正在尝试根据id键更新jsonb数组的多个值。

我在“人”表中有列 uniqueid 和 jsarray。

例如从人中选择*

1,  [{"id": 1101, "val": "testing1", "valuom": "", "description": "Desc_test_1"}, 
     {"id": 1105, "val": "testing2", "valuom": "", "description": "Desc_test_2"},
     {"i d": 1108, "val": "testing2", "valuom": "", "description": "Desc_test_3"}]

我需要根据 id 键更新 Description、val 和 valuom 的值。

例如更新 id= 1101 和 uniqueid=1 的 jsarray 列；需要 o/p 为：

  1  , [{"id":1101,"val":"testing5","valuom":"test5", "description":"Desc_test_5"}, 
        {"id":1105,"val":"testing2","valuom":"","description":"Desc_test_2"} ,
        {"id":1108,"val":"testing2","valuom":"","description":"Desc_test_3"}];

标签： jsonpostgresqljsonbpostgresql-11

解决方案

您可以在其中使用包含jsonb_set()函数的此类查询：

WITH p AS
(
 SELECT ('{'||idx-1||',val}')::text[] AS path_val,
        ('{'||idx-1||',desc}')::text[] AS path_desc
   FROM people 
  CROSS JOIN jsonb_array_elements(jsarray) 
   WITH ORDINALITY arr(j,idx)
  WHERE (j->>'id')::int=1101 
    AND uniqueid = 1
)
UPDATE people
   SET jsarray = jsonb_set(
                           jsonb_set(jsarray,p.path_val,'"testing5"',false),
                                     p.path_desc,'"Desc_test_5"',false
                 )
  FROM p

接着

WITH p AS
(
 SELECT ('{'||idx-1||',val}')::text[] AS path_val,
        ('{'||idx-1||',desc}')::text[] AS path_desc,
        ('{'||idx-1||',valuom}')::text[] AS path_valuom
   FROM people 
  CROSS JOIN jsonb_array_elements(jsarray) 
   WITH ORDINALITY arr(j,idx)
  WHERE (j->>'id')::int=1105 
    AND uniqueid = 1
)
UPDATE people
   SET jsarray = jsonb_set(
                       jsonb_set(
                                jsonb_set(jsarray,p.path_val,'"testing21"',false),
                                 p.path_desc,'"Desc_test_21"',false),
                                 p.path_valuom,'"test21"',false
                 )
  FROM p

对 JSONB 值中的每个相关 ID 值连续。

Demo

推荐阅读