GeoJson - return a collection inside feature properties

I have some Java code which is returning a features object in a geojson and as part of the properties, I am returning some specific elementls like id etc. What I would like to do is be able to run another select query which returns not just one value but a list and be able to return that for every row in my properties object in the geojson. I tried to do something like this but this wouldn't work. Any ideas on how this can be achieved?

SELECT json_build_object(              'type', 'FeatureCollection',               'features', json_agg(                  json_build_object(                      'type',       'Feature',                      'id',         feature_id ,                      'geometry',   ST_AsGeoJSON(f.geom)::json,                      'properties', json_build_object(                          'myid', f.myid,                          'second', f.second,                          ...                           'test', json_agg(row_to_json(                                                select e.event_year, count(e.event_year) from events e where e.cond = f.cond group by e.event_year                          ))                      )  

So in this example, myid, second etc return fine but when I add test to the mix, that fails. I want to return the response of the query for every property as each one has a specific value.

https://stackoverflow.com/questions/67362418/geojson-return-a-collection-inside-feature-properties May 03, 2021 at 09:02AM