Question 89127 in PostgreSQL Question posted in
The official documentation can be found here.

delete all the records from postgres where jsonb contains list of keys – Postgresql

I’m trying to delete the records where jsonb have one or more keys.

My Data-

id jsonb_col
1 [{"unit": "sale", "group": ["spares"]}]
2 [{"unit": "sale"}]
3 [{"member": "j1"}]

after deleting for key in (unit, group), my data should be like this-

id jsonb_col
3 [{"member": "j1"}]

I have tried with cte query –

WITH keys AS (
  SELECT jsonb_object_agg(key, null) AS keys 
  FROM unnest(ARRAY['unit', 'group']) AS key
) 
DELETE FROM my_table 
WHERE jsonb_col @> (SELECT keys FROM keys)

this gives me DELETE 0.

where my select query alone returns below data-

{"unit": null, "group": null}

is there any other way to delete with list of keys in jsonb without cte?

Tags:

2

Answers


  1. 0

    One option is to use an EXISTS subquery that iterates over the array elements and checks if one of them contains the keys

    delete from the_table t
where exists (select *
              from jsonb_array_elements(t.jsonb_col) as x(item)
              where x.item ?| array['unit','group'])

    Another option is to use a JSON path query

    delete from the_table
where jsonb_col @? '$[*].keyvalue() ? (@.key == "unit" || @.key == "group")'
    Login or Signup to reply.
  2. 0

    You can also use jsonb_path_exists.

    BEGIN;
CREATE temp TABLE test_mult_jsonb_keyt (
    id int,
    jsonb_col jsonb
) ON COMMIT DROP;
INSERT INTO test_mult_jsonb_keyt
    VALUES (1, jsonb'[{"unit": "sale", "group": ["spares"]}]'),
    (2, '[{"unit": "sale"}]'),
    (3, '[{"member": "j1"}]');
DELETE FROM test_mult_jsonb_keyt
WHERE jsonb_path_exists(jsonb_col, '$[*].unit')
    OR jsonb_path_exists(jsonb_col, '$[*].group')
RETURNING
    *;
TABLE test_mult_jsonb_keyt;
END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search