How to query all rows of a table into single json Postgresql?

Question

I am trying to generate a single json by querying from a table with id as key and email as value like this

{1: "abc@example.com", 2: "abcd@example.com"}

My attempt to create is:

select json_agg(json_build_object(id, email)) 
from auth_user 
where id in (4733, 3625, 4961, 4237);

which results me

[{4733: "abc@example.com"}, {3625: "abcd@example.com"}]

I think you are looking for json_object_agg not json_agg — a_horse_with_no_name, Aug 23, 2017 at 11:03
@BalasubramanianRaju you probably also want jsonb, not not json — Evan Carroll, Aug 23, 2017 at 13:05
Not sure if this is exactly what you're looking for, but you could try my answer here - be careful of the caveats after the answer itself - esp. with respect to \ (backslashes). — Vérace, Aug 23, 2017 at 18:15
@a_horse_with_no_name Thanks, I was looking for json_object_agg only and now its working. — Balasubramanian Raju, Aug 23, 2017 at 18:20

Evan Carroll · Accepted Answer · 2017-08-23 13:05:40Z

1

Migrating a comment to an answer.

I think you are looking for json_object_agg not json_agg – a_horse_with_no_name 2 hours ago

Links

answered Aug 23, 2017 at 13:05

Evan Carroll

60.4k44 gold badges224 silver badges455 bronze badges

Add a comment |

1 Answer 1