Remove key from json list in PostgreSQL

Question

I have json list in table like:

[{ "a": 1, "b": 2, "c": 3 }, {"a":1, "b":2, "a":3}]

And i want remove all "a" keys. How i can do it?

I tried this:

UPDATE mytable
SET list = list::jsonb - 'a'

But it is not working.

I want this result:

[{"b": 2, "c": 3 }, {"b":2}]

Can you add , {"a";3} to the input data, and also adjust the expected result (if needed.) — jarlh
– jarlh, Commented Jan 21, 2020 at 8:36

Enlico · Accepted Answer · 2021-01-13 20:38:35Z

2

This will do it.

UPDATE mytable
SET list=(
  SELECT jsonb_agg(elem - 'a')::json
  FROM jsonb_array_elements(list::jsonb) elem
)

Enlico

30.2k10 gold badges72 silver badges162 bronze badges

answered Jan 21, 2020 at 8:47

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

2 Comments

"function jsonb_array_elements(json) does not exist"

Welcome. I was working with jsonb field on first post and edited for json when realizing.