Is there a way to change ST_asGeoJSON return values?

Question

The function ST_asGeoJSON returns something like:

"st_asgeojson":
   {
     "type":"MultiLineString",
     "coordinates":[
        [
          [-3.13512629299993,39.7174353130001],    
          [-19.13510345099996,2.7174767110001]
        ]
      ]
    }

And I wonder if there's a way to simplify or customize the JSON it returns, as I only need the coordinates, to something like:

{
  coordinates: [
    [-9.13512629299993,38.7174353130001],
    [-9.13510345099996,38.7174767110001]
  ]
}

Answer 1

Be aware that the GeoJSON specification states that

The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object.

http://geojson.org/geojson-spec.html#geojson-objects

If you decide that you do not want/need to be compliant with the GeoJSON spec, you can use the - (minus) operator as of postgresql 9.5.

ST_AsGeoJSON(the_geom)::jsonb - 'type'

as of postgres 9.3 you can extract value with ->

ST_AsGeoJSON(the_geom)::json->'coordinates'

so you can do some string magic (assuming you still want the coordinates in quotes to stay json compatible)

CONCAT('{"coordinates":', ST_AsGeoJSON(the_geom)::json->'coordinates', '}')

For postgres <= 9.2 I think the answer by Michal Zimmermann is the best approach.

---

To reduce the dimensionality of the json output you can use the postgis function ST_LineMerge before passing it to ST_AsGeoJSON.

ST_AsGeoJSON(ST_LineMerge(the_geom))

If you have to do this, you should as well consider the impact on MultiLineStrings that actually DO contain multiple linestrings (which is not the case in your example) or if you do not have linestrings with multiple parts (and do not plan to handle such), you should directly save your data as LineString instead of MultiLineString.

Answer 2

SELECT concat('{', substring(
    ST_AsGeoJSON(wkb_geometry) from 
    position(',' in ST_AsGeoJSON(wkb_geometry)) + 1
    ))
FROM table_name;

It simply finds the first , character, and extracts the substring from its position to the end of the geojson.

Answer 3

Tiny bit simpler version of Michal Zimmerman's for people on 9.2

Uses regex of the first and last square brackets and returns them and everything in between.

if you really want only the coordinates you could use

SELECT substring(ST_AsGeoJSON(wkb_geometry)::json::text, '\\[.*\\]') FROM table_name;

This should return just

"[[[-3.13512629299993,39.7174353130001], [-19.13510345099996,2.7174767110001]]]"

as a string.