如何使用where子句从PostgreSQL的jsonb列类型中存储的JSON数组中修改或删除特定的JSON对象? [英] How to modify or remove a specific JSON object from JSON array stored in jsonb column type in PostgreSQL using where clause?

问题描述

在我的Postgres数据库中，我的表列之一具有jsonb数据类型.在该列中，我存储了JSON数组.现在，我要删除或修改数组中的特定JSON对象.

In my Postgres database, I have one of the table columns having jsonb datatype. In that column, I am storing the JSON array. Now, I want to remove or modify a specific JSON object inside the array.

我的JSON数组看起来像

My JSON array looks like

[
    {
        "ModuleId": 1,
        "ModuleName": "XYZ"
    },
    {
        "ModuleId": 2,
        "ModuleName": "ABC"
    }
]

现在，我要执行两项操作:

Now, I want to perform two operations:

1. 如何从上面的ModuleId为1的数组中删除JSON对象?
2. 如何修改JSON对象，即将ModuleName更改为ModuleId为1的'CBA'?

有没有一种方法可以直接在JSON数组上执行查询?

Is there a way through which I could perform queried directly on JSON array?

注意:Postgres版本是12.0

Note: Postgres version is 12.0

推荐答案

两个问题都需要取消嵌套并聚合回(已修改的)JSON元素.对于这两个问题，我都会创建一个函数以使其更易于使用.

Both problems require unnesting and aggregating back the (modified) JSON elements. For both problems I would create a function to make that easier to use.

create function remove_element(p_value jsonb, p_to_remove jsonb)
  returns jsonb
as
$$
  select jsonb_agg(t.element order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx)
  where not t.element @> p_to_remove;
$$
language sql
immutable;

该函数可以像这样使用，例如在UPDATE语句中:

The function can be used like this, e.g. in an UPDATE statement:

update the_table
  set the_column = remove_element(the_column, '{"ModuleId": 1}')
where ...

---

对于第二个问题，类似的功能也很方便.

---

For the second problem a similar function comes in handy.

create function change_value(p_value jsonb, p_what jsonb, p_new jsonb)
  returns jsonb
as
$$
  select jsonb_agg(
         case
           when t.element @> p_what then t.element||p_new
           else t.element
         end order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx);
$$
language sql
immutable;

||运算符将覆盖现有密钥，因此这可以有效地用新名称替换旧名称.

The || operator will overwrite an existing key, so this effectively replaces the old name with the new name.

您可以像这样使用它:

update the_table
  set the_column = change_value(the_column, '{"ModuleId": 1}', '{"ModuleName": "CBA"}')
where ...;

我认为传递JSON值比对键进行硬编码要灵活一些，这使得该函数的使用非常有限.第一个功能还可以用于通过比较多个键来删除数组元素.

I think passing the JSON values is a bit more flexible then hardcoding the keys which makes the use of the function very limited. The first function could also be used to remove array elements by comparing multiple keys.

如果不想创建函数，请用函数中的select替换函数调用.

If you don't want to create the functions, replace the function call with the select from the functions.

这篇关于如何使用where子句从PostgreSQL的jsonb列类型中存储的JSON数组中修改或删除特定的JSON对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！