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}

CodePudding user response：

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

Db<>fidlle.

CodePudding user response：

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}