ST_AsGeoJSONAgg which is aggregate version of ST_AsGeoJSON

[robe]

I was thinking about this some, and realized when I want to use ST_AsGeoJSON, I usually combine it with something like jsonb_buildobject or json_buildobject, cause ST_AsGeoJSON has no version that supports aggregates like ST_AsMVT has.

Wouldn't it be nice if one could do this:

SELECT ST_AsGeoJSONAgg(t.*, id_column => 'id')::json
FROM ( VALUES (1, 'one', 'POINT(1 1)'::geometry),
              (2, 'two', 'POINT(2 2)'),
              (3, 'three', 'POINT(3 3)')
     ) as t(id, name, geom);

and it would output the same result as what this does

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*, id_column => 'id')::json)
    )
FROM ( VALUES (1, 'one', 'POINT(1 1)'::geometry),
              (2, 'two', 'POINT(2 2)'),
              (3, 'three', 'POINT(3 3)')
     ) as t(id, name, geom);

Now sadly we can't overload ST_AsGeoJSON here, for the same reason we couldn't overload ST_Union, cause the above would be ambiguous as to if you want to aggregate or do row by row.

[mdavis]

Seems like a nice enhancement.