如何基于嵌套键值删除JSONB列中的数组元素? [英] How to delete array element in JSONB column based on nested key value?

问题描述

如何根据对象键之一的值从数组中删除对象?

How can I remove an object from an array, based on the value of one of the object's keys?

该数组嵌套在父对象中.

The array is nested within a parent object.

这是一个示例结构:

{
  "foo1": [ { "bar1": 123, "bar2": 456 }, { "bar1": 789, "bar2": 42 } ],
  "foo2": [ "some other stuff" ]
}

我可以根据bar1的值删除数组元素吗?

Can I remove an array element based on the value of bar1?

我可以使用columnname @> '{ "foo1": [ { "bar1": 123 } ]}'根据bar1值进行查询，但是我没有运气找到从foo1中删除{ "bar1": 123, "bar2": 456 }的方法，同时保持其他所有内容不变.

I can query based on the bar1 value using: columnname @> '{ "foo1": [ { "bar1": 123 } ]}', but I've had no luck finding a way to remove { "bar1": 123, "bar2": 456 } from foo1 while keeping everything else intact.

谢谢

运行PostgreSQL 9.6

Running PostgreSQL 9.6

推荐答案

假定您要使用具有特定值的内部对象搜索特定对象，并且该特定对象可以出现在数组中的任何位置，则需要要解压缩文档和每个数组，请测试内部子文档是否包含并适当删除，然后重新组装数组和JSON文档(未经测试):

Assuming that you want to search for a specific object with an inner object of a certain value, and that this specific object can appear anywhere in the array, you need to unpack the document and each of the arrays, test the inner sub-documents for containment and delete as appropriate, then re-assemble the array and the JSON document (untested):

SELECT id, jsonb_build_object(key, jarray)
FROM (
    SELECT foo.id, foo.key, jsonb_build_array(bar.value) AS jarray
    FROM (  SELECT id, key, value
            FROM my_table, jsonb_each(jdoc) ) foo,
        jsonb_array_elements(foo.value) AS bar (value)
    WHERE NOT bar.value @> '{"bar1": 123}'::jsonb
    GROUP BY 1, 2 ) x
GROUP BY 1;

现在，这似乎有点密集，所以将其分开可以得到:

Now, this may seem a little dense, so picked apart you get:

SELECT id, key, value
FROM my_table, jsonb_each(jdoc)

这在表上使用横向联接以获取JSON文档jdoc，并将其转换为一组行foo(id, key, value)，其中value包含数组. id是表的主键.

This uses a lateral join on your table to take the JSON document jdoc and turn it into a set of rows foo(id, key, value) where the value contains the array. The id is the primary key of your table.

然后我们得到:

SELECT foo.id, foo.key, jsonb_build_array(bar.value) AS jarray
FROM foo,  -- abbreviated from above
     jsonb_array_elements(foo.value) AS bar (value)
WHERE NOT bar.value @> '{"bar1": 123}'::jsonb
GROUP BY 1, 2

这使用另一个横向连接将数组解压缩为bar(value)行.现在可以使用包含运算符搜索这些对象，以从结果集中删除这些对象:WHERE NOT bar.value @> '{"bar1": 123}'::jsonb.在选择列表中，数组由id和key重新组装，但现在没有令人讨厌的子文档.

This uses another lateral join to unpack the arrays into bar(value) rows. These objects can now be searched with the containment operator to remove the objects from the result set: WHERE NOT bar.value @> '{"bar1": 123}'::jsonb. In the select list the arrays are re-assembled by id and key but now without the offending sub-documents.

最后，在主查询中，JSON文档被重新组装:

Finally, in the main query the JSON documents are re-assembled:

SELECT id, jsonb_build_object(key, jarray)
FROM x  -- from above
GROUP BY 1;

要了解的重要一点是PostgreSQL JSON函数仅在您可以明确指示的JSON文档级别上运行.通常，这是文档的顶层，除非您在文档中有指向某层的显式路径(例如{foo1, 0, bar1}，但您没有该路径).在该操作级别上，您可以打开包装进行处理，例如删除对象.

The important thing to understand is that PostgreSQL JSON functions only operate on the level of the JSON document that you can explicitly indicate. Usually that is the top level of the document, unless you have an explicit path to some level in the document (like {foo1, 0, bar1}, but you don't have that). At that level of operation you can then unpack to do your processing such as removing objects.

这篇关于如何基于嵌套键值删除JSONB列中的数组元素?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！