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

Answer 1

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

1. unnest your arrays to get one element per row
2. creating an airport JSON object with an id attribute
3. aggregating the airports for trips and cities
4. 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