Cast postgresql jsonb value as array of int and remove element from it

Question

I'm trying to remove int value from jsonb array of ints in json stored in jsonb value type in db.

I have a json value, like:

select '{"a":[1,3]}'::jsonb->'a'

?column?
----------
[1, 3]
(1 row)

And i want to remove '1' from it, like that:

select array_remove(ARRAY[1,1,2,3], 1);
 array_remove
--------------
 {2,3}
(1 row)

But if i do something like that:

select array_remove(array('{"a":[1,3]}'::jsonb->'a'), 1);

or:

select array_remove('{"a":[1,3]}'::jsonb->'a'::text::int[], 1);

I have an error like:

operator does not exist: jsonb -> integer[]

How can i cast json value to array to remove value from it?

Jeremy · Accepted Answer · 2019-08-22 20:07:17Z

0

For now, you basically have to expand the array to individual values, filter those values, and then create a new array:

with data(original) as (VALUES ('{"a":[1,1,2,3]}'::jsonb), ('{"a":[2,3,4,5,1]}'::jsonb))
select original->'a', filtered
from data
JOIN LATERAL (SELECT jsonb_agg(value) as filtered 
              from jsonb_array_elements(original->'a') j(value) 
              WHERE value::int != 1) as filtered ON TRUE;
    ?column?     |   filtered
-----------------+--------------
 [1, 1, 2, 3]    | [2, 3]
 [2, 3, 4, 5, 1] | [2, 3, 4, 5]

In postgres 12, you'll be able to use the jsonb_path functions. The syntax is a bit difficult to parse, but it doesn't involve any joins:

with data(original) as (VALUES ('{"a":[1,1,2,3]}'::jsonb), ('{"a":[2,3,4,5,1]}'::jsonb))
select original->'a', 
       jsonb_path_query_array(original, '$.a[*] ? (@ != $filter)', '{"filter":1}')
from data;
    ?column?     | jsonb_path_query_array
-----------------+------------------------
 [1, 1, 2, 3]    | [2, 3]
 [2, 3, 4, 5, 1] | [2, 3, 4, 5]
(2 rows)

answered Aug 22, 2019 at 20:07

Jeremy

6,78322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fryme Over a year ago

Thank you Jeremy, i tested it and it seems to be worked. But why i can't just use array_remove function? And actually i need to update that array in my original json field, now i'm trying to implement it. If you have some advices it would be useful.

Jeremy Over a year ago

array_remove is for an array type, not json or jsonb. You could expand the json array with jsonb_array_elements, use array_agg, to make an int[] and then use array_remove, but that's just an extra step.

Collectives™ on Stack Overflow

Cast postgresql jsonb value as array of int and remove element from it

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related