Remove Key Value pair from jsonb nested array in postgresql

Question

I have jsonb data as

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

I want to remove c key from nested array in both "a" and "g" keys. Is there a single query to perform this?

not sure you can update it as is (unless you create a function for specifically this. You can recreate your jsonb by expanding it, removing unnecessary elements and aggregate it back - dbfiddle.uk/… — roman
– roman, Commented Nov 11, 2019 at 13:54

S-Man · Accepted Answer · 2019-11-11 14:22:36Z

2

step-by-step demo: db<>fiddle

SELECT
    jsonb_object_agg(key, a)                                -- 5
FROM (
    SELECT
        mydata,
        key,
        jsonb_agg(a_elems.value - 'c') as a                   -- 3/4
    FROM
        mytable,
        jsonb_each(mydata) elems,                             -- 1
        jsonb_array_elements(elems.value) AS a_elems          -- 2
    GROUP BY mydata, key                                      -- 4
) s
GROUP BY mydata                                               -- 5

Expand the JSON elements into one row each. This generates two columns: One for the key and one for the value (the JSON array)
Expand the JSON array into one row each (which separates the aggregated JSON object from which you want to remove the c element)
You can use the - operator to remove the element.
To reaggregate the original JSON object, you need to group it backwards. jsonb_agg() reaggregates the arrays
Finally you need to rebuild the original JSON object with jsonb_object_agg() using the previously generated key column and the new array column.

edited Nov 11, 2019 at 14:22

answered Nov 11, 2019 at 13:48

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

8 Comments

GMB Over a year ago

OP has jsonb data, so you probably want to change all these json_* functions to their equivalent jsonb_*.

S-Man Over a year ago

@GMB Thank you. I really missed it. Changed!

GMB Over a year ago

Looks good now. Feel free to add this db fiddle to your answer.

GMB Over a year ago

Oh sorry did not notice the link.

Santhosh Chandrasekharan Over a year ago

Looks good. But I am using Postgres 9.4. How can we achieve same result for this version?. As per docs, jsonb_agg does not exist

|

Collectives™ on Stack Overflow

Remove Key Value pair from jsonb nested array in postgresql

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related