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

Postgres – How to aggregate json rows into a single json object – Postgresql

I have this rows of json like so:

1. {"0": 2}
2. {"2": 0}
3. {"3": 0}
4. {"4": 1}
5. {"13": 0}

How to convert it into a single object?

{
  "0": 2,
  "2": 0,
  "3": 0,
  "4": 1,
  "13": 0
}

I tried experimenting with jsonb_agg but not working

Tags:

2

Answers


  1. 0

    I think the easiest is to define an aggregate that does this:

    create aggregate jsonb_concat_agg(jsonb) 
(
  sfunc = jsonb_concat(jsonb, jsonb),
  stype = jsonb
);

    Then you can do:

    select jsonb_concat_agg(the_column)
from the_table;
    Login or Signup to reply.
  2. 0

    You can do it using jsonb_each to split object into a set of key/value pairs. and jsonb_object_agg to returns the aggregated key–value pairs as a jsonb object :

    select jsonb_object_agg(c.key, c.value) as myJson
FROM mytable, jsonb_each(myjson) as c

    Demo here

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

Back To Top
Search