I have a jsonb column in PostgreSQL and what I want to do is retrieve the values in this columns, but exclude an attribute that is inside an array.
From what I can find it's required for an index to be included in the query so the best I've been able to do is remove the attribute from only a single item in the array.
Example of what I currently have
SELECT jsonb '[{"a":1, "b":2}, {"a":3, "b":4}]' #- '{0,"b"}';
Current output
[{"a": 1}, {"a": 3, "b": 4}]
Desired output
{"a": 1}
{"a": 3}
You can use the operator twice:
select jsonb '[{"a":1, "b":2}, {"a":3, "b":4}]' #- '{0,"b"}' #- '{1, "b"}';
If you want to remove an unknown number of "b" attributes, you need to unnest the array and rebuild it:
select jsonb_agg(value - 'b')
from jsonb_array_elements(jsonb '[{"a":1, "b":2}, {"a":3, "b":4}]')
You can consider creating a handy function:
create or replace function jsonb_remove_attribute_from_array(jsonb, text)
returns jsonb language sql immutable as $$
select jsonb_agg(value - $2)
from jsonb_array_elements($1)
$$;
select jsonb_remove_attribute_from_array('[{"a":1, "b":2}, {"a":3, "b":4}]', 'b');
I've figure out what I needed to do.
SELECT jsonb_array_elements(jsonb '[{"a":1, "b":2}, {"a":3, "b":4}]') - 'b';
Output
{"a": 1}
{"a": 3}