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.