Malloy Documentation
search

Nested views are views included in other views. When a nested view is used in a query, it generates an aggregating subquery, which produces a subtable per row in the view in which it is embedded. The term nested query is often used to refer to a query which uses a nested view.

document
run: airports -> {  
  group_by: state
  aggregate: airport_count
  nest: by_facility is {
    group_by: fac_type
    aggregate: airport_count
  }
}
QUERY RESULTS
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1389
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 435
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 8
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 8
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 5
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 569
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 396
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 12
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 625
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 245
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 8
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 6
      },
      {
        "fac_type": "BALLOONPORT",
        "airport_count": 2
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 511
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 280
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 43
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 13
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 5
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 4
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 468
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 307
      },
      {
        "fac_type": "ULTRALIGHT",
        "airport_count": 13
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 10
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 3
      },
      {
        "fac_type": "GLIDERPORT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      base."fac_type"
      END as "fac_type__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "airport_count__1"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "state__0" as "state",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "fac_type": "fac_type__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_facility"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Nesting Nested Views

Aggregating subqueries can be nested infinitely, meaning that a nested view can contain another nested view.

document
run: airports -> {
  group_by: state
  aggregate: airport_count
  nest: top_5_counties is {
    limit: 5
    group_by: county
    aggregate: airport_count
    nest: by_facility is {
      group_by: fac_type
      aggregate: airport_count
    }
  }
}
QUERY RESULTS
[
  {
    "state": "TX",
    "airport_count": 1845,
    "top_5_counties": [
      {
        "county": "HARRIS",
        "airport_count": 135,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 110
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 25
          }
        ]
      },
      {
        "county": "TARRANT",
        "airport_count": 63,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 35
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 26
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "DENTON",
        "airport_count": 53,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "DALLAS",
        "airport_count": 42,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 32
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 9
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "BEXAR",
        "airport_count": 40,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 24
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          }
        ]
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "top_5_counties": [
      {
        "county": "LOS ANGELES",
        "airport_count": 176,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 151
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 23
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 71,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 53,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 47
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "SAN DIEGO",
        "airport_count": 49,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 30
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 17
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "KERN",
        "airport_count": 49,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 41
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "top_5_counties": [
      {
        "county": "COOK",
        "airport_count": 51,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 44
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 7
          }
        ]
      },
      {
        "county": "LA SALLE",
        "airport_count": 39,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 35
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 4
          }
        ]
      },
      {
        "county": "MC HENRY",
        "airport_count": 29,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 20
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "DE KALB",
        "airport_count": 27,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 24
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 3
          }
        ]
      },
      {
        "county": "WINNEBAGO",
        "airport_count": 24,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 9
          }
        ]
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "top_5_counties": [
      {
        "county": "PALM BEACH",
        "airport_count": 45,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 30
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "DADE",
        "airport_count": 44,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 27
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 12
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "POLK",
        "airport_count": 43,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 18
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 9
          }
        ]
      },
      {
        "county": "MARION",
        "airport_count": 37,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 27
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 36,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 4
          }
        ]
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "top_5_counties": [
      {
        "county": "BUCKS",
        "airport_count": 55,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 32
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 19
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "MONTGOMERY",
        "airport_count": 44,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 29
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ALLEGHENY",
        "airport_count": 31,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 22
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "CHESTER",
        "airport_count": 27,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 11
          }
        ]
      },
      {
        "county": "PHILADELPHIA",
        "airport_count": 26,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 22
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 4
          }
        ]
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "airport_count__0",
    CASE WHEN group_set IN (1,2) THEN
      base."county"
      END as "county__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "airport_count__1",
    CASE WHEN group_set=2 THEN
      base."fac_type"
      END as "fac_type__2",
    CASE WHEN group_set=2 THEN
      COUNT(1)
      END as "airport_count__2"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  GROUP BY 1,2,4,6
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1 ELSE group_set END as group_set,
    "state__0" as "state__0",
    FIRST("airport_count__0") FILTER (WHERE "airport_count__0" IS NOT NULL) as "airport_count__0",
    CASE WHEN group_set IN (1,2) THEN
      "county__1"
      END as "county__1",
    FIRST("airport_count__1") FILTER (WHERE "airport_count__1" IS NOT NULL) as "airport_count__1",
    COALESCE(LIST({
      "fac_type": "fac_type__2", 
      "airport_count": "airport_count__2"}  ORDER BY  "airport_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_facility__1"
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  "state__0" as "state",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "county": "county__1", 
    "airport_count": "airport_count__1", 
    "by_facility": "by_facility__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:5],[]) as "top_5_counties"
FROM __stage1
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Filtering Nested Views

Filters can be applied at any level within nested views.

document
run: airports -> {
  where: state ? 'CA' | 'NY' | 'MN'
  group_by: state
  aggregate: airport_count
  nest: top_5_counties is {
    limit: 5
    group_by: county
    aggregate: airport_count
    nest: major_facilities is  {
      where: major = 'Y'
      group_by: name is concat(code, ' - ', full_name)
    }
    nest: by_facility is  {
      group_by: fac_type
      aggregate: airport_count
    }
  }
}
QUERY RESULTS
[
  {
    "state": "CA",
    "airport_count": 984,
    "top_5_counties": [
      {
        "county": "LOS ANGELES",
        "airport_count": 176,
        "major_facilities": [
          {
            "name": "BUR - BURBANK-GLENDALE-PASADENA"
          },
          {
            "name": "LAX - LOS ANGELES INTL"
          },
          {
            "name": "LGB - LONG BEACH /DAUGHERTY FIELD/"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 151
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 23
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 71,
        "major_facilities": [
          {
            "name": "ONT - ONTARIO INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 53,
        "major_facilities": [
          {
            "name": "SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 47
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "SAN DIEGO",
        "airport_count": 49,
        "major_facilities": [
          {
            "name": "SAN - SAN DIEGO INTL-LINDBERGH FLD"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 30
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 17
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "KERN",
        "airport_count": 49,
        "major_facilities": [
          {
            "name": "BFL - MEADOWS FIELD"
          },
          {
            "name": "IYK - INYOKERN"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 41
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 576,
    "top_5_counties": [
      {
        "county": "SUFFOLK",
        "airport_count": 34,
        "major_facilities": [
          {
            "name": "ISP - LONG ISLAND MAC ARTHUR"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 18
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ERIE",
        "airport_count": 26,
        "major_facilities": [
          {
            "name": "BUF - BUFFALO NIAGARA INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 18
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 8
          }
        ]
      },
      {
        "county": "NIAGARA",
        "airport_count": 20,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          }
        ]
      },
      {
        "county": "DUTCHESS",
        "airport_count": 20,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 12
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          }
        ]
      },
      {
        "county": "ONONDAGA",
        "airport_count": 18,
        "major_facilities": [
          {
            "name": "SYR - SYRACUSE HANCOCK INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 13
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 4
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "state": "MN",
    "airport_count": 507,
    "top_5_counties": [
      {
        "county": "ST LOUIS",
        "airport_count": 28,
        "major_facilities": [
          {
            "name": "DLH - DULUTH INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 13
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 10
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          }
        ]
      },
      {
        "county": "HENNEPIN",
        "airport_count": 23,
        "major_facilities": [
          {
            "name": "MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 9
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 8
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "CROW WING",
        "airport_count": 17,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 6
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "DAKOTA",
        "airport_count": 17,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 3
          }
        ]
      },
      {
        "county": "ANOKA",
        "airport_count": 14,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 9
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 3
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 2
          }
        ]
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "airport_count__0",
    CASE WHEN group_set IN (1,2,3) THEN
      base."county"
      END as "county__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "airport_count__1",
    CASE WHEN group_set=2 THEN
      CONCAT(base."code",' - ',base."full_name")
      END as "name__2",
    CASE WHEN group_set=3 THEN
      base."fac_type"
      END as "fac_type__3",
    CASE WHEN group_set=3 THEN
      COUNT(1)
      END as "airport_count__3"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set  ) as group_set
  WHERE (base."state" IN ('CA','NY','MN'))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND base."major"='Y')))
  GROUP BY 1,2,4,6,7
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1 WHEN group_set=3 THEN 1 ELSE group_set END as group_set,
    "state__0" as "state__0",
    FIRST("airport_count__0") FILTER (WHERE "airport_count__0" IS NOT NULL) as "airport_count__0",
    CASE WHEN group_set IN (1,2,3) THEN
      "county__1"
      END as "county__1",
    FIRST("airport_count__1") FILTER (WHERE "airport_count__1" IS NOT NULL) as "airport_count__1",
    COALESCE(LIST({
      "name": "name__2"}  ORDER BY  "name__2" asc NULLS LAST) FILTER (WHERE group_set=2),[]) as "major_facilities__1",
    COALESCE(LIST({
      "fac_type": "fac_type__3", 
      "airport_count": "airport_count__3"}  ORDER BY  "airport_count__3" desc NULLS LAST) FILTER (WHERE group_set=3),[]) as "by_facility__1"
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  "state__0" as "state",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "county": "county__1", 
    "airport_count": "airport_count__1", 
    "major_facilities": "major_facilities__1", 
    "by_facility": "by_facility__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:5],[]) as "top_5_counties"
FROM __stage1
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Nesting Named Views

Any views defined in a source can be used by name in a nest: clause or a query.

document
source: airports_with_views is airports extend {
  view: by_facility is {
    group_by: fac_type
    aggregate: airport_count
  }

  view: major_facilities is {
    where: major = 'Y'
    group_by: name is concat(code, ' - ', full_name)
  }

  view: top_5_counties is {
    limit: 5
    group_by: county
    nest: 
      major_facilities
      by_facility
  }
}

run: airports_with_views -> top_5_counties
QUERY RESULTS
[
  {
    "county": "--PUERTO RICO",
    "major_facilities": [
      {
        "name": "BQN - RAFAEL HERNANDEZ"
      },
      {
        "name": "SJU - LUIS MUNOZ MARIN INTL"
      }
    ],
    "by_facility": [
      {
        "fac_type": "HELIPORT",
        "airport_count": 20
      },
      {
        "fac_type": "AIRPORT",
        "airport_count": 19
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 1
      }
    ]
  },
  {
    "county": "-TRUST TERR. OF PAC-",
    "major_facilities": [],
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "county": "-VIRGIN ISLANDS-",
    "major_facilities": [
      {
        "name": "STT - CYRIL E KING"
      },
      {
        "name": "STX - HENRY E ROHLSEN"
      }
    ],
    "by_facility": [
      {
        "fac_type": "HELIPORT",
        "airport_count": 4
      },
      {
        "fac_type": "SEAPLANE BASE",
        "airport_count": 3
      },
      {
        "fac_type": "AIRPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "county": "ABBEVILLE",
    "major_facilities": [],
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 3
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 1
      }
    ]
  },
  {
    "county": "ACADIA",
    "major_facilities": [],
    "by_facility": [
      {
        "fac_type": "AIRPORT",
        "airport_count": 9
      },
      {
        "fac_type": "HELIPORT",
        "airport_count": 3
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."county" as "county__0",
    CASE WHEN group_set=1 THEN
      CONCAT(base."code",' - ',base."full_name")
      END as "name__1",
    CASE WHEN group_set=2 THEN
      base."fac_type"
      END as "fac_type__2",
    CASE WHEN group_set=2 THEN
      COUNT(1)
      END as "airport_count__2"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  WHERE (group_set NOT IN (1) OR (group_set IN (1) AND base."major"='Y'))
  GROUP BY 1,2,3,4
)
SELECT
  "county__0" as "county",
  COALESCE(LIST({
    "name": "name__1"}  ORDER BY  "name__1" asc NULLS LAST) FILTER (WHERE group_set=1),[]) as "major_facilities",
  COALESCE(LIST({
    "fac_type": "fac_type__2", 
    "airport_count": "airport_count__2"}  ORDER BY  "airport_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_facility"
FROM __stage0
GROUP BY 1
ORDER BY 1 asc NULLS LAST
LIMIT 5