How to reformat SQL statement to export GeoJSON from PostGIS table?

Question

So, I realize this question gets asked a lot, but the root of my question is related to the syntax of the SQL statement used to format the result table as GeoJSON. I am currently able to execute a SELECT * statement using this code which I adapted from an example I found online

SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
   , ST_AsGeoJSON(lg.geom)::json As geometry
   , row_to_json((SELECT l FROM (SELECT id, filename, type, \"desc\") As l
     )) As properties
  FROM public.\"Peng\" As lg   ) As f )  As fc;

The majority of examples suggest using the json_build_object function, but the examples I've found are a bit opaque to me, particularly the nested statements and truncated variables. Conceptually, I know the statements are creating the features collection and then the nested features, but it's unclear how to reference my tables and fields correctly. In my case I'm trying to get the results of this query returned as JSON for my app: SELECT * FROM public."Peng", public."Address_Info" WHERE "Peng".id = "Address_Info".id; Eventually, I would be interested pulling specific fields, but for now I'd just be happy to understand how to adapt either my, or a json_build_object example to my use case. Additionally, any insight into how this syntax works, and the shortened variables and such.

Answer 1

You'd want to run:

SELECT JSONB_BUILD_OBJECT(     --aggregate features into FeatureCollection
         'type',     'FeatureCollection',
         'features', JSONB_AGG(features.feature)
       ) AS featurecollection
FROM   (
  --subquery to generate GeoJSON Features from "table_rows", aliased "AS features"
  SELECT JSONB_BUILD_OBJECT(   --create a Feature per row
           'type',       'Feature',
           'id',         table_rows.id,  --you don't need this, but you can have it
           'geometry',   ST_AsGeoJSON(table_rows.geom)::JSONB,
           'properties', TO_JSONB(table_rows.*) - 'geom' - 'id'  --aggregate all (table_rows.*) columns from "table_rows" into 'properties' object, but remove 'id' and 'geom'
         ) AS feature
  FROM   (
    --sub(sub)query to fetch desired rows from tables, aliased "AS table_rows"
    SELECT *  -- make sure this won't return two columns called "id"
    FROM   public."Peng" AS p   --example for aliasing (naming) a table for convenience: public."Peng" is now called p
    JOIN   public."Address_Info" AS i
      ON   p.id = i.id          --use the given aliases throughout the query

  ) AS table_rows

) AS features
;

or, if you had that mentioned function set installed:

SELECT ST_AsFeatureCollection(table_rows.*) AS geojson
FROM   (
    SELECT *
    FROM   public."Peng" AS p
    JOIN   public."Address_Info" AS i
      ON   p.id = i.id
) AS table_rows
;