Question 218551 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: removing specific key of each elements in JSONB array

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!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search