How to remove all JSON attributes with certain value in PostgreSQL-CodePudding

given this table

parent	payload
1	`{ a: 7, b: 3 }`
2	`{ a: 7, c: 3 }`
1	`{ d: 3, e: 1, f: 3 }`

I want to update children of 1 and remove any attribute X where payload->X is 3.

after executing the query the records should look like this:

parent	payload
1	`{ a: 7 }`
2	`{ a: 7, c: 3 }`
1	`{ e: 1 }`

    update records set payload=?? where parent = 1 and ??

CodePudding user response：

There is no built-inf function for this, but you can write your own:

create function remove_keys_by_value(p_input jsonb, p_value jsonb)
  returns jsonb
as
$$
  select jsonb_object_agg(t.key, t.value)
  from jsonb_each(p_input) as t(key, value)
  where value = p_value;
$$
language sql
immutable;

Then you can do:

update records
  set payload = remove_key_by_value(payload, to_jsonb(3))
where parent = 1;

This assumes that payload is defined as jsonb (which it should be). If it's not, you have to cast it: payload::jsonb

CodePudding user response：

Try this

update records
set payload = payload - 'x'
where parent = 1 and payload->>'x'::int = 3