PostgreSQL 9.2 Native JSON type support

One new welcome feature in PostgreSQL 9.2 is the native json support and companion row_as_json and array_as_json functions. PostGIS also has a json function for outputting geographies and geometries in GeoJSON format which is almost a standard in web mapping.

Here is an example of how you'd use the new feature - create our test table

CREATE TABLE test(gid serial PRIMARY KEY, title text, geog geography(Point, 4326));

INSERT INTO test(title, geog) 
 , ST_GeogFromText('POINT(-71.057811 42.358274)'));

INSERT INTO test(title, geog) 
 , ST_GeogFromText('POINT(42.358274 -71.057811 )'));

Now with a command like this we can output all data as a single json object.

SELECT array_to_json(array_agg(t))
FROM test As t;

But there is a tincy little problem. Our geog outputs don't look anything like GeoJSON format. Our output looks like this:


To follow the GeoJSON standard, our geography object should output like this:


We were hoping it would be a simple matter of defining a cast for geometry and geography something like this:

CREATE OR REPLACE FUNCTION json(geog geography)
$$ SELECT _ST_AsGeoJSON(1, $1, 15, 0)::json; $$
CREATE CAST (geography AS json)
  WITH FUNCTION json(geography)

And with the above CAST, array_to_json would be Clearly I'm missing something here. I have to apply this work-around:

SELECT array_to_json(array_agg(t)) As my_places
FROM (SELECT gid, title, geog::json As geog FROM test) As t;

Which ain't bad and much better than before, but is not quite as nice as being able to just use aray_to_json without care of types of columns and have array_to_json function automatically use a json CAST if a type provides a custom json CAST. I have this ticketed in PostGIS 2.1 as a nice feature to have.