Remove specific key and value from nested json array in PostgreSQL column

Question

I have a json object field in PostgreSQL in mytable.fields as below

{
  "a": "1",
  "b": [
    {
      "c": 2,
      "d": 3
    },
    {
      "c": 4,
      "d": 5
    },
    {
      "c": 6,
      "d": 7
    }
  ]
}

How can I delete all "c" key and value pairs like as below? I found other StackOverflow solutions that remove the whole object, but I couldn't find this.

{
  "a": "1",
  "b": [
    {
      "d": 3
    },
    {
      "d": 5
    },
    {
      "d": 7
    }
  ]
}

Pooya · Accepted Answer · 2021-10-12 12:03:06Z

1

with data as (
  select 
    your_json_col :: jsonb as obj,
    jsonb_array_elements((your_json_col ->> 'b') :: jsonb) :: jsonb - 'c' as new_obj
  from your_table)
select 
  obj || jsonb_build_object('b', jsonb_agg(new_obj))
from data
group by obj;

answered Oct 12, 2021 at 12:03

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

1 Comment

Jason KIM Over a year ago

Is it possible to keep other columns in this table and literally UPDATE only this json column into this table?

Collectives™ on Stack Overflow

Remove specific key and value from nested json array in PostgreSQL column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related