Modify JSON array element

Question

I have a table

id int | data json

With data:

1 | [1,2,3,2]
2 | [2,3,4]

I want to modify rows to delete array element (int) 2

Expected result:

1 | [1,3]
2 | [3,4]

The sample data looks more like that is a "native" array, not a JSON document. Is that really a JSON? And if so, why are you putting a plain array into a JSON document? For a native array can use array_remove() — user330315
– user330315, Commented Apr 18, 2017 at 14:00
> The second row should be [3, 4]? Yes, I fixed it, Thank you — Vitaly Teplyakov
– Vitaly Teplyakov, Commented Apr 18, 2017 at 16:48

klin · Accepted Answer · 2017-04-18 14:18:00Z

1

As a_horse_with_no_name suggests in his comment the proper data type is int[] in this case. However, you can transform the json array to int[], use array_remove() and transform the result back to json:

with my_table(id, data) as (
values
(1, '[1,2,3,2]'::json),
(2, '[2,3,4]')
)

select id, to_json(array_remove(translate(data::text, '[]', '{}')::int[], 2))
from my_table;

 id | to_json 
----+---------
  1 | [1,3]
  2 | [3,4]
(2 rows)

Another possiblity is to unnest the arrays with json_array_elements(), eliminate unwanted elements and aggregate the result:

select id, json_agg(elem)
from (
    select id, elem
    from my_table,
    lateral json_array_elements(data) elem
    where elem::text::int <> 2
    ) s
group by 1;

answered Apr 18, 2017 at 14:18

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Vitaly Teplyakov Over a year ago

Thank you very much. First example works fine. But how fast? =)

klin Over a year ago

The first query should be faster than the second one but much slower than the analogous query on int[] data.

Collectives™ on Stack Overflow

Modify JSON array element

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related