如何在PostgreSQL中删除jsonb整数数组中的元素

Question

在Postgres上，在一个名为"photo"我有一个json列名为id_us"包含一个json整数数组，就像下面这个[1,2,3,4]例如，我想找到删除元素3的查询。最接近的是

SELECT jsonb_set(id_us, ''
, (SELECT jsonb_agg(val)
FROM   jsonb_array_elements(p.id_us) x(val)
WHERE  val <> jsonb '3')
) AS id_us
FROM   photo p;

你知道怎么解决这个问题吗?谢谢你！

Answer 1

您可以使用包含JSONB_AGG()函数的子查询，同时过滤出索引值3(通过从1开始索引)，例如

WITH p AS
(
SELECT JSONB_AGG(j) AS js 
FROM photo 
CROSS JOIN JSONB_ARRAY_ELEMENTS(id_us) 
WITH ORDINALITY arr(j,idx)
WHERE idx != 3
)
UPDATE photo
SET id_us = js
FROM p 

编辑:如果您需要删除值，但不像评论中提到的那样删除索引，只需使用变量j转换为数字

WITH p AS
(
SELECT JSONB_AGG(j) AS js 
FROM photo 
CROSS JOIN JSONB_ARRAY_ELEMENTS(id_us) 
WITH ORDINALITY arr(j,idx)
WHERE j::INT != 18
)
UPDATE photo
SET id_us = js
FROM p 

p。S:使用JSONB_SET()，被删除的元素的逗号分隔的位置以及引号将仍然以如下方式保留

WITH p AS
(
SELECT ('{'||idx-1||'}')::TEXT[] AS idx 
FROM photo 
CROSS JOIN JSONB_ARRAY_ELEMENTS(id_us) 
WITH ORDINALITY arr(j,idx)
WHERE j::INT = 18
)
UPDATE photo
SET id_us = JSONB_SET(id_us,idx,'""')
FROM p; 
SELECT * FROM photo;
id_us
-----------------
[127, 52, "", 44]

Answer 2

我遇到过类似的问题，它源于-操作符。此操作符被重载以接受文本或整数，但每种类型的操作方式不同。使用文本将按值删除，使用整数将按索引删除。但是如果你的值是一个整数呢?那你真他妈倒霉…

如果可能的话，您可以尝试将jsonb整数数组更改为jsonb字符串数组(整数)，然后-运算符应该可以顺利工作。

。

'{1,2,3}' - 2 = '{1,2}'  -- removes index 2
'{1,2,3}' - '2' = '{1,2,3}' -- removes values == '2' (but '2' != 2)
'{"1","2","3"}' - 2 = '{"1","2"}' -- removes index 2
'{"1","2","3"}' - '2' = '{"1","3"}' -- removes values == '2'