PostgreSQL+PostGIS creating GeoJSON with ST_MakeLine

Question

Trying to get a valid geojson response from my PostgreSQL(9.6.3) + PostGIS (2.3.2) to track routes. I have a table of GPS positions of the form:

CREATE TABLE public.gps
(
 id integer NOT NULL DEFAULT nextval('gps_id_seq'::regclass),
 device_id integer, -- Device ID of GPS Device
 timecol bigint, -- Timestamp we use for linking video and GPS
 distance_from_hub double precision, -- Distance from nearest hub
 distance_from_hub_id integer, -- ID of nearest hub
 itudes_3d geography(PointM,4326)
)

I am trying to get a valid GeoJSON with the route as a LineString. The coordinates of each point are stored in the itudes_3d. I am querying with:

select jsonb_build_object(
 'type', 'FeatureCollection',
 'features', jsonb_agg(feature)) AS route
 FROM ( select jsonb_build_object(
 'type','Feature',
 'properties',jsonb_build_object(
 'opa', 1,
 'fillopa', 0.5,
 'type', 0,
 'weight', 1,
 'start_time', :start_time:,
 'end_time', :end_time:,
 'colour', 
 '#333'),
 'geometry', geometry.geoline::json) 
 FROM (select ST_AsGeoJSON(ST_MakeLine(geom.itudes_3d))::jsonb as geoline 
 FROM (select itudes_3d::geometry, ST_M(itudes_3d::geometry) as gtime
 FROM gps 
 WHERE device_id=:device_id: AND 
 timecol > :start_time: AND 
 timecol < :end_time: 
 ORDER BY timecol ASC) 
 AS geom) 
 AS geometry)
 AS feature

where :start_time:,:end_time: and ":device_id:" are placeholders for this explanation.

What I get back is:

{"type": "FeatureCollection", "features": [{"jsonb_build_object": {"type": "Feature", "geometry": {"type": "LineString", "coordinates": [[174.821949, -35.829088], [174.820518, -35.82855], [174.818997, -35.827884], [174.818997, -35.827884], [174.818696, -35.827857], [174.818363, -35.827694], [174.816604, -35.826887], [174.816275, -35.826737], [174.815868, -35.826569], [174.815188, -35.826255], etc...

The problem I have is the {"jsonb_build_object": label in the features... really I want:

{"type": "FeatureCollection", "features": [{"type": "Feature", "geometry": {"type": "LineString", "coordinates": [[174.821949, -35.829088], [174.820518, -35.82855], [174.818997, -35.827884], [174.818997, -35.827884], [174.818696, -35.827857], [174.818363, -35.827694], [174.816604, -35.826887], [174.816275, -35.826737], [174.815868, -35.826569], [174.815188, -35.826255], etc...

Can anyone identify where I have gone wrong in constructing this?

To me it looks like the subquery is being named in the output.

Answer 1

Try changing:

'features', jsonb_agg(feature)) AS route

to

'features', jsonb_agg(feature.jsonb_build_object)) AS route