CODEWITHSUNDEEP
sqlarraysjsonpostgresql
Jason KIM
Jason KIM

Reputation: 147

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

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
    }
  ]
}

Upvotes: 0

Views: 397

Answers (1)

Pooya
Pooya

Reputation: 3183

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;

Upvotes: 1

Related Questions