我试图更新jsonb列 media ,有两个键,即 default
**是jsonb类型, image_set
是jsonb数组 .
是否有单一选择更新语句的解决方案来更新两个密钥 . test_media表
id | media | name
----+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------
2 | {"default": {"w1": "fff", "w2": "aaa", "w3": "ddd"}, "image_set": [{"w1": "fff", "w2": "aaa", "w3": "ddd"}, {"w1": "bbb", "w2": "rrr", "w3": "vvv"}]} | pooja
更新图像集
Update test_media
set media = media #- ('{image_set,'||(select pos-1 from test_media, jsonb_array_elements(media->'image_set') with ordinality arr(value, pos) where name='pooja' and value->>'w1'='fff')
|| '}')::text[]
|| jsonb_set(media, '{default}', '{"w1": "bbb", "w2": "rrr", "w3": "vvv"}' )
where name='pooja';
在这里,基于删除,我想更新默认值和image_set一起取决于不同的条件 . default jsonb值来自 image_set 数组 . 我尝试使用case语句,但它没有正常工作 . 不同的删除条件是:
-
当我要删除的jsonb值是默认值以及image_set时,它应该从图像集中删除该值,并使用图像集中的其他值更新默认值 .
-
如果不是这样,它将不会更新默认值,只会删除image_set值 .
-
如果array的值为image_set为1,则media = '{}'更新为null json .
尝试分别更新两件事,默认和image_set .
Update test_media
set media = ( CASE
WHEN jsonb_array_length(media->'image_set')::int > 1
THEN (Select media #- ('{image_set,'||(select pos-1 from test_media , jsonb_array_elements(media->'image_set') with ordinality arr(value, pos) where name='pooja' and value->>'w1'='fff') || '}')::text[])
ELSE media = '{}'
END IF
)
where name='pooja';
在这里,我得到了错误:CASE类型boolean和jsonb无法匹配其次,
update test_media
set media = jsonb_set(media, '{default}', (select from (select CASE WHEN media->'default'->>'w1'='fff' AND jsonb_array_length(media->'image_set')::int >0 THEN (select media->'image_set'->0 from test_media where name='pooja' ) WHEN media->'default'->>'w1'='fff' AND jsonb_array_length(media->'image_set')::int = 0 THEN (select media - 'default' from test_media where name = 'pooja') END) As Sub), True)
where name='pooja';
如果我使用select update获得case case语句的支持,我将感激不尽 . 希望得到积极的回应 . 谢谢 .