CODEWITHSUNDEEP
sqljsonpostgresqljpasql-delete
volcano
volcano

Reputation: 1

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

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?

Upvotes: 0

Views: 376

Answers (2)

jian
jian

Reputation: 4824

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;

Upvotes: 0

user330315
user330315

Reputation:

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")'

Upvotes: 0

Related Questions