Easy JSON using row_to_json
The simplest JSON generator is row_to_json() which takes in a tuple value and returns the equivalent JSON dictionary.
SELECT row_to_json(employees) FROM employees WHERE employee_id = 1;
The resulting JSON uses the column names for keys, so you get a neat dictionary.
{
"employee_id": 1,
"department_id": 1,
"name": "Paul",
"start_date": "2018-09-02",
"fingers": 10,
"geom": {
"type": "Point",
"coordinates": [
-123.329773,
48.407326
]
}
}
And look what happens to the geometry column! Because PostGIS includes a cast from geometry to JSON, the geometry column is automatically mapped into GeoJSON in the conversion. This is a useful trick with any custom type: define a cast to JSON and you automatically integrate with the native PostgreSQL JSON generators.
Full result sets using json_agg
Turning a single row into a dictionary is fine for basic record access, but queries frequently require multiple rows to be converted.
Fortunately, there’s an aggregate function for that, json_agg, which carries out the JSON conversion and converts the multiple results into a JSON list.
SELECT json_agg(e)
FROM (
SELECT employee_id, name
FROM employees
WHERE department_id = 1
) e;
Note that in order to strip down the data in the record, we use a subquery to make a narrower input to json_agg.
[
{
"employee_id": 1,
"name": "Paul"
},
{
"employee_id": 2,
"name": "Martin"
}
]