GeoJSON export in PostgreSQL without PostGIS

Here is a handy SQL query I used at work to export geographic point data as GeoJSON from a plain, "spatially-dumb" PostgreSQL database. By that, I mean without the awesome PostGIS extension which offers native GeoJSON support.

It makes use of high-level JSON functions that appeared in PostgreSQL 9.4, so this assumes you use at least this version.

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(json_build_object(
        'type', 'Feature',
        'id', p.id,
        'geometry', json_build_object(
            'type', 'Point',
            'coordinates', json_build_array(p.longitude, p.latitude),
            -- assuming your coordinates are stored like that.
        ),
        'properties', json_build_object(
            'name', p.name,
            'address', p.address
            -- etc. Those are just examples of fields in the table.
        )
    )
))
FROM my_places p;

With this indenting and thanks to the simple function signatures (lists of alternating keys and values), you can see the final JSON shape directly in the query. The json_agg function is an SQL aggregate (like avg, min, etc.) which reduces multiple values to a single JSON array.

Saved in a UTF-8 text file, the result is a valid GeoJSON FeatureCollection of Point features. It can be opened in QGIS, geojson.io, and a multitude of other tools.