从jsonb数组中删除元素

Question

我有以下JSONB数据。我想要从数组pages中移除名为'pageb'的元素。类似问题提供的解决方案对我不起作用。

'{
  "data": {
    "id": "a1aldjfg3f",
    "pages": [
      {
        "type": "pagea"
      },
      {
        "type": "pageb"
      }                                
    ],
    "activity": "test"
  }
}'

目前我的脚本看起来像这样。它没有返回任何错误，但元素不会被移除。

  UPDATE database
  SET reports = jsonb_set(reports, '{data,pages}', (reports->'data'->'pages') - ('{"type":"pageb"}'), true)
  WHERE reports->'data'->'pages'  @> '[{"type":"pageb"}]';

- Battalgazi

---

可能是 删除jsonb数组中的元素 的重复问题。 - Spasa Mihajlovic

那个问题中的解决方案对我来说根本不起作用。 - Battalgazi

Answer 1

由于右操作数是一个定义键的字符串，因此无法在此处应用-运算符，根据文档:：

从左操作数中删除键/值对或字符串元素。键/值对基于其键值进行匹配。

要从json数组中删除json对象，可以通过拆包数组并找到对象的索引来完成。使用这种方法的查询可能过于复杂，因此在这种情况下定义自定义函数非常方便。

create or replace function jsonb_remove_array_element(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select arr- (
        select ordinality- 1
        from jsonb_array_elements(arr) with ordinality
        where value = element)::int
$$;

更新内容：

update my_table
set reports = 
    jsonb_set(
        reports, 
        '{data,pages}', 
        jsonb_remove_array_element(reports->'data'->'pages', '{"type":"pageb"}')
        )
where reports->'data'->'pages'  @> '[{"type":"pageb"}]';

rextester上的工作示例。

- klin

Answer 2

以下是可靠删除数组元素的答案和PostgreSQL使用数据修改WITH语句的组合，但需要一个标识列（在我的test表中为id）才能工作，因为需要相关性：

WITH new_reports AS (
    SELECT
        id,
        reports #- array['data','pages',(position - 1)::text] AS new_value
    FROM
        test,
        jsonb_array_elements(reports->'data'->'pages') WITH ORDINALITY arr(item, position)
    WHERE
        test.reports->'data'->'pages' @> '[{"type":"pageb"}]'
        AND
        item->>'type' = 'pageb'
    )
UPDATE test SET reports = new_reports.new_value FROM new_reports WHERE test.id = new_reports.id;

我使用的测试数据：

SELECT reports FROM test;
                                               reports                                               
-----------------------------------------------------------------------------------------------------
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pagec"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pageb"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pageb"}, {"type": "pagec"}], "activity": "test"}}
(3 rows)

...执行查询后：

SELECT reports FROM test;
                                               reports                                               
-----------------------------------------------------------------------------------------------------
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pagec"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagec"}], "activity": "test"}}
(3 rows)

我希望那对你有用。

- Ancoron

Answer 3

这是 klin 答案的增强版本，支持数组中不存在搜索元素的情况

CREATE OR REPLACE FUNCTION jsonb_remove_array_element(arr jsonb, element jsonb) 
RETURNS jsonb LANGUAGE plpgsql IMMUTABLE AS $$
  DECLARE _idx integer;
  DECLARE _result jsonb;
  BEGIN
    _idx := (SELECT ordinality - 1 FROM jsonb_array_elements(arr) WITH ordinality WHERE value = element);
    IF _idx IS NOT NULL 
    THEN 
      _result := arr - _idx;
    ELSE
      _result := arr;
    END IF;
    RETURN _result;
  END;
$$;

- Denis N

Answer 4

就是这样

do $$
declare newvar jsonb;
begin
    newvar := jsonb '{ "customer": "John Doe", "buy": [{"product": "Beer","qty": 6},{"product": "coca","qty": 5}]}';
    newvar := jsonb_set(newvar,'{buy}', jsonb_remove((newvar->>'buy')::jsonb,'{"product": "Beer"}'));
    newvar := jsonb_set(newvar,'{buy}', jsonb_add((newvar->>'buy')::jsonb,'{"product": "cofe","qty": 6}'));
    RAISE NOTICE '%', newvar; 
end $$

create or replace function jsonb_remove(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select ('['||coalesce(string_agg(r::text,','),'')||']')::jsonb from jsonb_array_elements(arr) r where r @> element=false
$$;

create or replace function jsonb_add(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select arr||element
$$;

- Hung Tran DI