Select as JSON object {key: {}}

Question

My table:

ID | something1 | something2 | ...
1  | meow       | 5          |
2  | 4          | KITTIES    |

Is there any way to select data as JSON in format {"1":{"something1":"meow","something2":5},"2":{...}}?

Answer 1

If you don't mind repeating the ID field in the JSON representation of a row, you can do:

SELECT 
  format('{%s}',
    string_agg(
      format(
        '%s:%s',
        to_json(ID::text),
        row_to_json(my_table)
      ), ','
    ), ''
  )::json as json_object
FROM my_table;

This gives you a JSON object containing a sub-object for each row in the table, keyed by the value in the ID field.

SQLFiddle

See this question for more details.

Answer 2

You can use this library to get an API of the database. Then, consume it! This is the fastest and clearest thing I can imagine.