postgresql 如何更新json数组中的每个json对象？

wa7juj8i 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(2)|浏览(220)

我有简单的表table_b
| id（整数）|数据（json）|文本（文本）|
| - ------|- ------|- ------|
| 1个|{}|是的|
| 第二章|{}|没有|
Json看起来像

{"types": [{"key": "first_event", "value": false}, {"key": "second_event", "value": false}, {"key": "third_event", "value": false}...]}

我只想修改数据并添加到array ["test1"，"test2"]中的每个JSON对象，看起来像这样：

{"types": [{"key": "first_event", "value": false, "can":["test1", "test2"] }, {"key": "second_event", "value": false , "can":["test1", "test2"]}, {"key": "third_event", "value": false , "can":["test1", "test2"]}...]}

当然，只有像"是"这样的文字我才尝试过：

UPDATE table_b
SET data = jsonb_set(data , '{types,can}', '["test1", "test2"]'::jsonb, true)
where text like 'yes';

但是它不起作用，我怎么循环表_b和数据列？

postgresql

来源：https://stackoverflow.com/questions/75436706/how-to-update-each-json-object-in-json-array

2条答案

按热度按时间

tcbh2hod1#

JSON对象的数组值是可以修改的，只需修改给定索引中的值即可。

赞(0）回复(0）举报 2023-02-15

carvr3hs2#

使用此示例数据

create table table_b as
select * from (values
(1,'{"types": [{"key": "first_event", "value": false}, {"key": "second_event", "value": false}, {"key": "third_event", "value": false}]}'::jsonb,'yes'),
(2,'{}'::jsonb,'no'),
(3,'{"types": [{"key": "first_event", "value": false}]}'::jsonb,'yes')
) table_b(id,data,txt)

此查询更新每个数组元素，并保持ORDINALITY列中的顺序

with table_b2 as (
select 
  id,item, index,
  jsonb_set(item,('{"can"}')::TEXT[],'["test1", "test2"]') new_item
from table_b  cross join 
jsonb_array_elements(data -> 'types') WITH ORDINALITY arr(item, index) 
where txt = 'yes')
select * from table_b2
|
id|item                                   |index|new_item                                                          |
--+---------------------------------------+-----+------------------------------------------------------------------+
 1|{"key": "first_event", "value": false} |    1|{"can": ["test1", "test2"], "key": "first_event", "value": false} |
 1|{"key": "second_event", "value": false}|    2|{"can": ["test1", "test2"], "key": "second_event", "value": false}|
 1|{"key": "third_event", "value": false} |    3|{"can": ["test1", "test2"], "key": "third_event", "value": false} |
 3|{"key": "first_event", "value": false} |    1|{"can": ["test1", "test2"], "key": "first_event", "value": false} |

下一个查询将更新的元素连接回数组（保持正确的顺序）并执行简单的更新

with table_b2 as (
select 
  id,item, index,
  ('{types,' || index - 1 || ',"can"}')::TEXT[] AS path,
  jsonb_set(item,('{"can"}')::TEXT[],'["test1", "test2"]') new_item
from table_b  cross join 
jsonb_array_elements(data -> 'types') WITH ORDINALITY arr(item, index) 
where txt = 'yes'),
table_b3 as (
select 
 id,
 jsonb_agg(new_item order by index) new_data
from table_b2
group by id)
update table_b t
set data = table_b3.new_data
from table_b3
where t.id = table_b3.id

select * from table_b order by id;

id|data                                                                                                                                                                                                      |txt|
--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---+
 1|[{"can": ["test1", "test2"], "key": "first_event", "value": false}, {"can": ["test1", "test2"], "key": "second_event", "value": false}, {"can": ["test1", "test2"], "key": "third_event", "value": false}]|yes|
 2|{}                                                                                                                                                                                                        |no |
 3|[{"can": ["test1", "test2"], "key": "first_event", "value": false}]                                                                                                                                       |yes|

赞(0）回复(0）举报 2023-02-15

我来回答

postgresql 如何更新json数组中的每个json对象？

2条答案

相关问题

热门标签

最新问答