PostgreSQL: removing specific key of each elements in JSONB array

Question

I am having a table where one column is a JSONB column that has the following payload:

[
  {a: "foo", b: "bar"},
  {a: "bar", b: "baz"}
]

I want to remove the "b" element for each array item:

SELECT json_build_object(
  'id', s.id,
  'key', json_build_object(
    'key', s.column #- '{1, b}'
  )
) FROM table s

This works well but it removes it only from the second element of the array. I would like to remove it for every element of the array (the number of items in the array is not known and cannot be hardcoded.

Is there a way to do that easily?

Thanks!

score 2 · Accepted Answer · answered Jul 28 '23 at 11:39

2

Unnest the jsonb array, subtract the key, and reaggregate:

select t.id, jsonb_agg(e.j - 'b' order by e.n) as some_jsonb
  from some_table t
       cross join lateral jsonb_array_elements(t.some_jsonb) 
         with ordinality as e(j, n)
 group by t.id
;

Working fiddle

answered Jul 28 '23 at 11:39

Mike Organek

11,647
3
11
26

Thanks, this did the trick! I was thinking there could be something simpler! – Michael Gallego Jul 28 '23 at 11:48

score 1 · Answer 2 · answered Jul 29 '23 at 10:37

Try this jsonb_agg function by combining it with lateral join.

SELECT
  json_build_object(
    'id', s.id,
    'key', jsonb_agg(j.item - 'b')
  ) AS result
FROM table s

CROSS JOIN LATERAL jsonb_array_elements(s.your_jsonb_column) AS j(item)
GROUP BY s.id;

Also replace your_jsonb_column with the name of JSONB column that contains the array. Hope it works.

PostgreSQL: removing specific key of each elements in JSONB array

2 Answers2