Postgres get multiple rows into a single json object

Question

I have a users table with columns like id, name, email, etc. I want to retrieve information of some users in the following format in a single json object:

{
    "userId_1" : {"name" : "A", "email": "[email protected]"},
    "userId_2" : {"name" : "B", "email": "[email protected]"}
}

Wherein the users unique id is the key and a json containing his information is its corresponding value.

I am able to get this information in two separate rows using json_build_object but I would want it get it in a single row in the form of one single json object.

GMB · Accepted Answer · 2020-06-13 13:02:22Z

9

You can use json aggregation functions:

select jsonb_object_agg(id, to_jsonb(t) - 'id') res
from mytable t

jsonb_object_agg() aggregates key/value pairs into a single object. The key is the id of each row, and the values is a jsonb object made of all columns of the table except id.

Demo on DB Fiddle

Sample data:

id       | name | email      
:------- | :--- | :----------
userid_1 | A    | [email protected]
userid_2 | B    | [email protected]

Results:

| res                                                                                                    |
| :----------------------------------------------------------------------------------------------------- |
| {"userid_1": {"name": "A", "email": "[email protected]"}, "userid_2": {"name": "B", "email": "[email protected]"}} |

edited Jun 13, 2020 at 13:02

answered Jun 13, 2020 at 12:57

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

dsk · Accepted Answer · 2020-06-13 12:15:48Z

1

try -

select row_to_json(col) from T

link below might help https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

answered Jun 13, 2020 at 12:15

dsk

2,0272 gold badges12 silver badges13 bronze badges

Comments

Igor Borisenko · Accepted Answer · 2022-10-31 14:40:53Z

1

Try this:

SELECT json_object(array_agg(id), array_agg(json::text)) FROM (
SELECT id, json_build_object('name', name, 'email', email) as json
                     FROM users_table
) some_alias_name

If your id is not of text type then you have to cast it to text too.

edited Oct 31, 2022 at 14:40

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

answered Jun 13, 2020 at 12:48

Sharan V K

1812 silver badges9 bronze badges

1 Comment

Basavraj Over a year ago

Thanks, gives the desired output.

Collectives™ on Stack Overflow

Postgres get multiple rows into a single json object

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related