Good to know: PostgreSQL records to single JSON string

To convert a set of results to a single JSON string, use:

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     [ your query here ]
) ROW;

Tip: In the query, if you specify field names or table.*, you will not get an object hence the JSON will not have your schema sub grouped by object name. i.e.

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     SELECT mytable WHERE [ ... ]
) ROW;

 ... is not the same as ...

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     SELECT mytable.* WHERE [ ... ]
) ROW;



Kudos: http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql