1

I have a table with hundred of columns. I would like to write a single SELECT to get the GeoJSON string returning ONLY specific columns values (geometry and some others fields, but not all the columns values).

If I run:

SELECT ST_AsGeoJSON(d.*)
FROM   mytable d 
WHERE  d.id = 76025);

It works, but if I want only some fields like this:

SELECT ST_AsGeoJSON(d.wkb_geometry, d.info, d.address)
FROM   mytable d 
WHERE  d.id = 76025);

Obviously the St_asGeoJSON doesn't work because it has row as parameter and not a list of fields. How can I get only those 3 columns in the resulting geoJSON WRITING A SINGLE STATEMENT?

kiks73
  • 3,718
  • 3
  • 25
  • 52
  • 1
    You may try using `json_build_object`. Few examples are here : https://www.sqliz.com/postgresql-ref/json_build_object/ Documentation: https://www.postgresql.org/docs/current/functions-json.html – Tushar Mar 10 '23 at 09:16

1 Answers1

1

The solution is very simple using subquery:

SELECT ST_AsGeoJSON(p.*) 
FROM   (SELECT d.wkb_geometry, d.info, d.address
        FROM   mytable d 
        WHERE  d.id = 76025) AS p;
kiks73
  • 3,718
  • 3
  • 25
  • 52