I'm trying to produce GeoJSON from a query in Postgres 13.1:

SELECT json_build_object(
               'type', 'FeatureCollection',
               'features', json_agg(
                       json_build_object(
                               'type', 'Feature',
                               'geometry', ST_AsGeoJSON(geom)::json,
                               'properties', json_build_object(
                                   -- list of fields
                                       'id', hardinfra.id,
--                                    need json_object_agg here
                                       'protections', json_build_object(
                                               p.ptype,
                                               i.pscore
                                           ),
--                                    need json_object_agg here
                                       'responses', json_build_object(
                                               ep.etype,
                                               er.response
                                           ),
                                       'category', c.category
                                   )
                           )
                   )
           )
FROM hardinfra
         JOIN expertresponse er ON hardinfra.id = er.infra_id
         JOIN expert ep ON ep.id = er.expert_id
         JOIN infraprotection i ON hardinfra.id = i.infra_id
         JOIN protection p ON p.id = i.protection_id
         JOIN category c on c.id = hardinfra.category_id
;

However protections and responses should be key-value collections built up from the many-to-many relationships, usually using json_object_agg (see join conditions). How can I get around the lack of nested aggregation calls to produce these?

This is what the GeoJSON output should look like:

Note that the "protections" and "responses" properties can be of arbitrary length as they're coming from the related tables.

{
  "features": [
    {
      "geometry": {
        "coordinates": [
          -6.147880554,
          53.383533145
        ],
        "type": "Point"
      },
      "properties": {
        "id": 90,
        "protections": {
          "Foo": 1,
          "Bar": 2,
          "Baz": 3
        },
        "responses": {
          "A": "response A",
          "B": "response B",
          "C": "response C"
        }
      },
      "type": "Feature"
    }
  ],
  "type": "FeatureCollection"
}

The tables are set up as follows (You'll need PostGIS, or substitute a plaintext field – that aspect isn't important here):

create table hardinfra (
    id serial CONSTRAINT hardinfra_pkey PRIMARY KEY,
    geom geometry(Geometry, 4326)
    );

create table expert
(
    id serial
        constraint expert_pkey
            primary key,
    etype varchar(50)
);

create table expertresponse
(
    infra_id  integer not null
        constraint expertresponse_infra_id_fkey
            references hardinfra
            on delete cascade,
    expert_id integer not null
        constraint expertresponse_expert_id_fkey
            references expert
            on delete cascade,
    response  varchar(280) not null,
    constraint expertresponse_pkey
        primary key (infra_id, expert_id)
);

create table protection
(
    id serial
        constraint protection_pkey
            primary key,
    ptype varchar(200)
);

create table infraprotection
(
    infra_id integer not null
        constraint infraprotection_infra_id_fkey
            references hardinfra
            on delete cascade,
    protection_id integer not null
        constraint infraprotection_protection_id_fkey
            references protection
            on delete cascade,
    pscore integer   default 0 not null,
    constraint infraprotection_pkey
        primary key (infra_id, protection_id)
);

CodePudding user response：

As stated by @a_horse_with_no_name you can't build nested aggregations in just one step, you can try this instead, assuming that there is only one category_id per id in table hardinfra and one category per id in table category :

SELECT json_build_object
           ( 'type', 'FeatureCollection'
           , 'features'
           , json_agg( json_build_object 
                           ( 'type', 'Feature'
                           , 'geometry', ST_AsGeoJSON(geom)::json
                           , 'properties'
                           , json_build_object
                                 ( 'id', hi.id
                                 , 'protections', p.protection_list
                                 , 'responses', r.response_list
                                 , 'category', c.category
                                 )
                           )
                     )
           )
FROM hardinfra AS hi
INNER JOIN
(   SELECT i.infra_id
         , json_build_object ('protections'
                             , json_object_agg(p.ptype, i.pscore)
                             ) AS protection_list
      FROM infraprotection AS i 
      JOIN protection AS p
        ON p.id = i.protection_id
) AS p
ON hi.id = p.infra_id
INNER JOIN
(   SELECT er.infra_id
         , json_build_object ('responses'
                             , json_object_agg(ep.etype, er.response)
                             ) AS response_list
      FROM expertresponse AS er
      JOIN expert AS ep
        ON ep.id = er.expert_id
) AS r
ON hi.id = r.infra_id
INNER JOIN category c
ON c.id = hi.category_id ;

PS : in your json example, there is no "category" key/value ?