Exclude geometry column from properties of ST_AsGeoJSON output

Question

The ST_AsGeoJSON function with the following query:

SELECT ST_AsGeoJSON(t.*) FROM vw_ph t (ph, capture_datetime) LIMIT 1;

Returns the following:

  {
     "type":"Feature",
     "geometry":{
        "type":"Point",
        "coordinates":[
           -88.987035,
           30.161476
        ]
     },
     "properties":{
        "id":1,
        "capture_datetime":"2020-08-26T19:51:05.575-05:00",
        "ph":16,
        "boat_datetime":"2020-08-26T19:51:05.538-05:00",
        "latitude":30.161476,
        "longitude":-88.987035,
        "an_point_geom":{
           "type":"Point",
           "coordinates":[
              -88.987035,
              30.161476
           ]
        }
     }
  }

I am trying to get a geojson output without having the geometry column an_point_geom repeated in the properties field as below as it will add unnecessary size in a large query and polygon geometry.

The closest I have been to this is with the query below.

SELECT ST_AsGeoJSON((t.ph , t.capture_datetime, t.an_point_geom, t.boat_datetime)) FROM vw_ph t LIMIT 1;

However, this returns the result without the geometry column in properties but the columns are named f1, f2... which is not desirable.

  {
     "type":"Feature",
     "geometry":{
        "type":"Point",
        "coordinates":[
           -88.987035,
           30.161476
        ]
     },
     "properties":{
        "f1":16,
        "f2":"2020-08-26T19:51:05.575-05:00",
        "f4":"2020-08-26T19:51:05.538-05:00"
     }
  }

Is there any way to exclude the geometry column from properties without another query?

I am using PostGIS 3.0.2 in PostgreSQL 9.6.

Answer 1

Could it be that you have two geometry columns? With just one, it should not be repeated in the properties.

Anyways, you can keep using the complete record t.*, but filter out columns before:

SELECT ST_AsGeoJSON(t2.*)
FROM 
  (SELECT id, name, geom_aaa --Filter the columns here
    FROM
      (VALUES
      (1, 'one', 'POINT(1 1)'::geometry,'POINT(10 10)'::geometry),
      (2, 'two', 'POINT(2 2)', 'POINT(20 20)'),
      (3, 'three', 'POINT(3 3)', 'POINT(30 30)'))
    AS t(id, name, geom_aaa, geom_bbb)) 
 AS t2;
                                                  st_asgeojson
-----------------------------------------------------------------------------------------------------------------
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}
 {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}