Postgres select as json hash

Question

I can select rows as JSON from Postgres e.g.

select row_to_json(t)
    from (
      select id, name from accounts
    ) t

Which returns each row in this format:

{"id":6001,"name":"Foo"}

I would like to get them in this format:

{ 6001: {"name":"Foo"} }

Is this possible?

Edit:

This will get me the proper format for one row:

select json_build_object(id,json_build_object('name',name)) from accounts

But what I'm ultimately after is all rows in a single hash indexed by key, e.g.

{ 
  6001: {"name":"Foo"},
  6002: {"name":"Bar"},
  6003: {"name":"Baz"},
}

This gets me closer:

select array_to_json(array_agg(json_build_object(id,json_build_object('name',name))))
from accounts

But it's still not quite there, producing an array of hashes:

[
  { 6001: {"name":"Foo"} },
  { 6002: {"name":"Bar"} },
  { 6003: {"name":"Baz"} },
]

Answer 1

You're trying to aggregate, so use the aggregate function json_object_agg(), which "aggregates name/value pairs as a JSON object". The id is your key, and the value you can create with json_build_object():

select json_object_agg(id, json_build_object('name', name))
from accounts