Postgres get multiple rows into a single json object

Solution 1:

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]"}} |