How to get around lack of nested aggregate calls to generate JSON from Postgres

Question

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)
);

This is typically done by not aggregating everything in a single step, but using derived tables/CTE were one builds on the other — user330315
– user330315, Commented Oct 29, 2021 at 15:41
@a_horse_with_no_name I had a feeling that or nested SELECT would be the case, but I'm struggling to write it. — urschrei
– urschrei, Commented Oct 29, 2021 at 15:48
I believe you would want to put those tables directly in your json_build_object(...) in a nested fashion rather than joining them (selecting json_build_object(), row_to_json() or json_agg()). — Cetin Basoz
– Cetin Basoz, Commented Oct 29, 2021 at 16:17

Edouard · Accepted Answer · 2021-10-29 17:56:17Z

1

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 ?

answered Oct 29, 2021 at 17:56

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

urschrei Over a year ago

Some very minor tweaks, and this works perfectly (changed geometry column to integer for the fiddle, and removed category as it wasn't needed): sqlfiddle.com/#!17/fbddc/7/0 – Thank you!

Cetin Basoz · Accepted Answer · 2021-10-30 08:31:56Z

Here is the simply nested selections, you don't need inner joins as I initially said:

SELECT json_build_object(
               'type', 'FeatureCollection',
               'features', json_agg(
                       json_build_object(
                               'type', 'Feature',
                               'geometry', ST_AsGeoJSON(geom)::json,
                               'properties', (
                                   select json_build_object(
                                                  'id', hardinfra.id,
                                                  'responses',
                                                  (
                                                      select json_object_agg(e.etype, er.response)
                                                      from expert e
                                                               inner join expertresponse er on e.id = er.expert_id
                                                      where infra_id = hardinfra.id
                                                  ),
                                                  'protections',
                                                  (
                                                      select json_object_agg(p.ptype, i.pscore)
                                                      from protection p
                                                               inner join infraprotection i on p.id = i.protection_id
                                                      where infra_id = hardinfra.id
                                                  )
                                              ))
                           )
                   ))
from hardinfra;

And here is DBFiddle demo.

Note: In DBFiddle I couldn't create extension postgis, so I commented geometry types and instead simulated with text (with geometry type, it would output like:

"geometry": {
        "type": "Point",
        "coordinates": [
          -6.147880554,
          53.383533145
        ]
      }

Collectives™ on Stack Overflow

How to get around lack of nested aggregate calls to generate JSON from Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related