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.

Solution 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

Solution 2

Use record_to_json(...) from 9.2, now available backported to 9.1.

Solution 3

On the server install:
sudo apt-get install postgresql-plpython-9.4

Then on your Postgres server:

CREATE EXTENSION IF NOT EXISTS plpythonu;
CREATE LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION prettyprint_json(data text)
 RETURNS json
 AS $$
    import json
    return json.dumps(json.loads(data), indent=4)
 $$ LANGUAGE plpythonu;

Solution 4

The postgresql plpython plugin certainly lets you do this using the python json library.

You can do something like this:

CREATE OR REPLACE FUNCTION myschema.tojsonfunc()
AS $$    

   import json;
   jsonStr = json.dumps(myrecord)

$$ LANGUAGE plpythonu;

View more solutions

Comments

[Ali]

Thanks, the problem is SQL query results (AKA records) are not sql serializable as they are, someone needs to take the time to "clean" them to conform, and if I wanted to do that, I'd better do it in my actual python code, and not bother loading the poor db server with this.

[Ali]

As I understand it, this is support for a JSON type, which holds json formated text and validates it, it does not produce JSON output.

[madth3]

Did you check the examples in the first link? There are functions query_to_json(), array_to_json() and record_to_json() which take input according to their names and turn it into JSON.

[Ali]

You are right, I missed the row_to_json function, this may solve my problem, thanks.

[Vicky Chijwani]

The first 2 links are dead. This is why StackOverflow's answering guidelines advise us to quote the relevant portion from a link.

[Zoltán]

Please update your answer with relevant portions of the solution and update the links as they are dead now.

[Juan Salvador]

Thanks Thanks Thanks working with PostgreSQL11 + Grails 3.3 + React