JSON output in Postgresql

Question

I hope I am not missing something very obvious here,
I want to get JSON output from a postgres function (I imagine many other had already needed this) and I'd be happy to install an extension of contrib functions on my server,

Is there any way to get JSON output from sql or plpgsql functions (or with help of db-server-side python)? Specifically I want to get my record[] results as JSON.

Answer 1

There is built-in support for JSON since PostgreSQL 9.2 and it has increased with many other features in more recent versions(For example: JSON functions in PostgreSQL 0.4).

Specially the row_to_json converts a record into a JSON object and the array_to_json turns arrays into JSON arrays.

For example, both functions can be combined to easily turn the results of a SELECT query into JSON:

SELECT array_to_json(array_agg(row_to_json(t))) FROM 
    (SELECT col1, col2, col3 FROM example_table) t