Merging aggregates into a single array rather than multiple rows

Question

With the following query I've almost achieved what I want in terms of the structure:

SELECT
    t.id,
    t.name,
    json_agg(places) AS places
FROM
    trips t

    -- trips_cities
    INNER JOIN (
        SELECT
            tc.city_id,
            tc.trip_id,
            json_agg(json_build_object(
                'city_id', tc.city_id,
                'airports', airports
            )) AS places
        FROM
            trips_cities tc

            -- airports 
            LEFT JOIN (
                SELECT
                    a.id,
                    a.country_id,
                    json_agg(json_build_object(
                        'airport_id', a.id
                    )) AS airports
                FROM airports a
                GROUP BY a.id
            ) a ON a.id = ANY(tc.airport_ids)
            -- /airports

        GROUP BY 1, 2
    ) tc ON tc.trip_id = t.id
    -- /trips_cities

GROUP BY 1

However instead of airports being an array, it returns duplicate cities:

[
  [
    {
      "city_id": 20,
      "airports": [
        {
          "airport_id": 2
        }
      ]
    },
    {
      "city_id": 20,
      "airports": [
        {
          "airport_id": 1
        }
      ]
    }
  ]
]

Instead of how I'd like it to be, which I can't seem to figure out the grouping for:

[
  [
    {
      "city_id": 20,
      "airports": [
        {
          "airport_id": 2
        },
        {
          "airport_id": 1
        }
      ]
    }
  ]
]

Some data:

INSERT INTO trips (id, name)
VALUES (1, 'My First Trip'),
       (2, 'My Second Trip');

INSERT INTO trips_cities (trip_id, city_id, airport_ids)
VALUES (1, 'London', {1,2}),
       (2, 'Paris', {1}),    
       (3, 'Berlin', {2});     

INSERT INTO airports(id, name)
VALUES (1, 'Heathrow'),
       (2, 'Gatwick');

Table structures are as follows:

trips

id

trips_cities

trip_id
city_id
airport_ids[]

airports

id

In summary:

For each trip join trips_cities
For each trips_cities join all airports
Create a nested agg of the results as in my final JSON example above

Sample data, desired results, and an explanation of what you want to do are all called for. — Gordon Linoff
– Gordon Linoff, Commented Apr 17, 2020 at 11:02

S-Man · Accepted Answer · 2020-04-17 13:04:02Z

First: Your sample data does not fit your INSERT statements. E.g., your city_ids are text instead of integer values. In my example, I used the INSERT statements.

Second: Please don't store arrays if it is not really neccessary. Please normalize your data. Storing arrays leads in many disadvantages: Searching for values, indexing values, joining values, etc. is really nasty. In most cases you need to unnest these values at the very beginning.

I am not quite sure what the two nested outer arrays should be but, the city/airports object can be created like this:

Click: demo:db<>fiddle

SELECT
    trip_id,
    json_build_object(                     -- 4
        'city_id', city_id,
        'airports', json_agg(json_build_object('airport_id', airport_id)) -- 2,3
    )
FROM trips_cities,
   unnest(airport_ids) as airport_id       -- 1
GROUP BY trip_id, city_id                  -- 3

unnest your arrays to get one element per row
creating an airport JSON object with an id attribute
aggregating the airports for trips and cities
creating a city/airports object out of the city data and the airports JSON array.

If you need some data from the other tables, e.g. the airport name, of course, you can join them:

SELECT
    trip_id,
    json_build_object(
        'city_id', city_id,
        'airports', json_agg(
            json_build_object(
                'airport_id', airport_id,
                'name', a.name
            )
        )
    )
FROM trips_cities,
   unnest(airport_ids) as airport_id
JOIN airports a ON a.id = airport_id
GROUP BY trip_id, city_id

Collectives™ on Stack Overflow

Merging aggregates into a single array rather than multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related