如何根据嵌套键值删除JSONB列中的数组元素？

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

该数组嵌套在父对象内。

这是一个示例结构：

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

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

我可以根据bar1值使用：columnname @> '{ "foo1": [ { "bar1": 123 } ]}'，但我没能找到删除的方法{ "bar1": 123, "bar2": 456 } from foo1同时保持其他一切完好无损。

Thanks

运行 PostgreSQL 9.6

假设您要搜索具有特定值的内部对象的特定对象，并且该特定对象可以出现在数组中的任何位置，则需要解压文档和每个数组，测试内部子文档是否存在根据需要包含并删除，然后重新组装数组和 JSON 文档（未经测试）：

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;

现在，这可能看起来有点密集，所以拆开你会得到：

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

这使用表上的横向连接来获取 JSON 文档jdoc并将其变成一组行foo(id, key, value)哪里的value包含数组。这id是表的主键。

然后我们得到：

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 and key但现在没有了有问题的子文件。

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

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

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