从JsonB数组中删除数值项

Question

我有一个带有嵌套JSON数组的jsonb值，并需要删除其中的一个元素:

{"values": ["11", "22", "33"]}

jsonb_set(column_name, '{values}', ((column_name -> 'values') - '33')) -- WORKS!

我也有一个类似的 jsonb 值，其中包含数字，而非字符串：

{"values": [11, 22, 33]}

jsonb_set(column_name, '{values}', ((column_name -> 'values') - 33))  -- FAILS! 

在这种情况下，33被用作数组的索引。

如何从JSON数组中删除数字项？

- Tomas Marik

Answer 1

两个断言：

1. 许多Postgres JSON函数和操作符针对key/value对中的key。JSON数组中的字符串（"abc"或"33"）被视为没有值的键。但是数字（33或123.45）数组元素被视为值。

2. 目前有三个变体的-操作符。其中两个适用于此处。如最近澄清的手册所述（当前版本/devel）：

   操作符
         描述
         示例
   :---------------------
   jsonb - text → jsonb
         从JSON对象中删除一个键（及其值），或从JSON数组中删除匹配的字符串值。
         '{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}
      '["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]
   ...
   jsonb - integer → jsonb
         删除具有指定索引的数组元素（负整数从末尾开始计数）。
         如果JSON值不是数组，则抛出错误。
         '["a", "b"]'::jsonb - 1 → ["a"]

当右操作数是一个数字文字时，Postgres 运算符类型解析 将会采用后一种变体。

不幸的是，由于断言1，我们不能一开始就使用前一种变体。

因此，我们必须使用解决方法，例如：

SELECT jsonb_set(column_name
               , '{values}'
               , (SELECT jsonb_agg(val)
                  FROM   jsonb_array_elements(t.column_name -> 'values') x(val)
                  WHERE  val <> jsonb '33')
                 ) AS column_name
FROM   tbl t;

db<>fiddle 这里 -- 带有扩展测试用例

不要将未嵌套的元素转换为integer（就像另一个答案建议的那样）。

• 数字值可能不适合integer。
• JSON数组（与Postgres数组不同）可以容纳混合元素类型。因此，一些数组元素可以是numeric，但其他元素可以是string等。
• 将所有数组元素进行类型转换（在左侧）更加昂贵。只需将要替换的值进行类型转换（在右侧）即可。

因此，这适用于任何类型，而不仅仅是整数（JSON数字）。例如：

'{"values": ["abc", "22", 33]}') 

- Erwin Brandstetter

---

使用 jsonb '33' 相对于 to_jsonb(33) 有什么优势吗？ - user330315

@a_horse_with_no_name：第一个跳过类型解析，并通过相关的输入函数将字符串字面量'33'强制转换为jsonb。第二个处理数字字面量33，类型解析到达integer，因此使用相关的输入函数将其强制转换为integer；然后查找具有一个参数的名为to_jsonb的函数，找到一个函数并且函数类型解析接受它，因为它采用anyelement，然后执行该函数并将integer转换为jsonb。所以，是的，第一个更快，更少出错。也更短，更清晰（在我看来）。 - Erwin Brandstetter

@a_horse_with_no_name: '33'::jsonb 和 jsonb '33' 是一样的。但后者少了一个字符。 :) - Erwin Brandstetter

我更多地是指结果，而不是效率。 - user330315

在这种情况下，结果是相同的。 - Erwin Brandstetter

Answer 2

不幸的是，Postgres json运算符-只支持字符串值，如文档中所述：

操作数：-

右操作数类型：text

描述：从左操作数中删除键/值对或字符串元素。键/值对基于它们的键值进行匹配。

另一方面，如果您将整数值作为右操作数传递，Postgres会将其视为需要删除的数组元素的索引。

另一种选择是使用jsonb_array_elements()和一个侧向连接来展开数组，过滤掉不需要的值，然后重新聚合：

select jsonb_set(column_name, '{values}', new_values) new_column_name
from mytable t
left join lateral (
    select jsonb_agg(val) new_values
    from jsonb_array_elements(t.column_name -> 'values') x(val)
    where val::int <> 33
) x on 1 = 1

"

在DB Fiddle上的演示:

"

with mytable as (select '{"values": [11, 22, 33]}'::jsonb column_name)
select jsonb_set(column_name, '{values}', new_values) new_column_name
from mytable t
left join lateral (
    select jsonb_agg(val) new_values
    from jsonb_array_elements(t.column_name -> 'values') x(val)
    where val::int <> 33
) x on 1 = 1

新列名： | new_column_name | | :------------------- | | {"values": [11, 22]} |

- GMB

Answer 3

PostgreSQL 12+

从这个版本开始，它具有使用jsonpath语法处理JSONB的强大功能。这里有一篇由该功能的作者撰写的文章，比官方文档更好地描述了所有用例。

jsonb_path_query_array

获取除33之外的所有数组项：

SELECT jsonb_path_query_array(
        '{"values": [11, 22, 33]}'::jsonb,
        '$.values[*] ? (@ <> 33)'
)
-- [11, 22]

通过使用jsonb_set的组合，它可以精确地给出我们想要的结果：

jsonb_set(column_name, '{values}', jsonb_path_query_array(column_name, '$.values[*] ? (@ <> 33)'))

- sigra