Malloy Documentation
search
previousJoins
SQL Sourcesnext

Nested Views

VSCode document

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
stateairport_​countby_​facility
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
ULTRALIGHT8
STOLPORT8
GLIDERPORT5
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
ULTRALIGHT2
STOLPORT2
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
BALLOONPORT2
GLIDERPORT2
STOLPORT2
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
[
  {
    "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,
    airports."state" as "state__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."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 airports
  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
stateairport_​counttop_​5_​counties
TX1,845
countyairport_​countby_​facility
HARRIS135
fac_​typeairport_​count
HELIPORT110
AIRPORT25
TARRANT63
fac_​typeairport_​count
HELIPORT35
AIRPORT26
ULTRALIGHT1
STOLPORT1
DENTON53
fac_​typeairport_​count
AIRPORT47
HELIPORT6
DALLAS42
fac_​typeairport_​count
HELIPORT32
AIRPORT9
STOLPORT1
BEXAR40
fac_​typeairport_​count
AIRPORT24
HELIPORT16
CA984
countyairport_​countby_​facility
LOS ANGELES176
fac_​typeairport_​count
HELIPORT151
AIRPORT23
SEAPLANE BASE2
SAN BERNARDINO71
fac_​typeairport_​count
AIRPORT47
HELIPORT24
ORANGE53
fac_​typeairport_​count
HELIPORT47
AIRPORT6
SAN DIEGO49
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
KERN49
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
IL890
countyairport_​countby_​facility
COOK51
fac_​typeairport_​count
HELIPORT44
AIRPORT7
LA SALLE39
fac_​typeairport_​count
AIRPORT35
HELIPORT4
MC HENRY29
fac_​typeairport_​count
AIRPORT20
HELIPORT7
SEAPLANE BASE2
DE KALB27
fac_​typeairport_​count
AIRPORT24
HELIPORT3
LEE24
fac_​typeairport_​count
AIRPORT18
HELIPORT5
ULTRALIGHT1
FL856
countyairport_​countby_​facility
PALM BEACH45
fac_​typeairport_​count
HELIPORT30
AIRPORT14
GLIDERPORT1
DADE44
fac_​typeairport_​count
HELIPORT27
AIRPORT12
SEAPLANE BASE2
GLIDERPORT2
STOLPORT1
POLK43
fac_​typeairport_​count
AIRPORT18
HELIPORT16
SEAPLANE BASE9
MARION37
fac_​typeairport_​count
AIRPORT27
HELIPORT7
SEAPLANE BASE2
STOLPORT1
ORANGE36
fac_​typeairport_​count
HELIPORT24
AIRPORT8
SEAPLANE BASE4
PA804
countyairport_​countby_​facility
BUCKS55
fac_​typeairport_​count
AIRPORT32
HELIPORT19
ULTRALIGHT2
STOLPORT1
GLIDERPORT1
MONTGOMERY44
fac_​typeairport_​count
HELIPORT29
AIRPORT14
SEAPLANE BASE1
ALLEGHENY31
fac_​typeairport_​count
HELIPORT22
AIRPORT8
SEAPLANE BASE1
CHESTER27
fac_​typeairport_​count
HELIPORT16
AIRPORT11
PHILADELPHIA26
fac_​typeairport_​count
HELIPORT22
AIRPORT4
[
  {
    "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": "LEE",
        "airport_count": 24,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 18
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "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,
    airports."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
      airports."county"
      END as "county__1",
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as "airport_count__1",
    CASE WHEN group_set=2 THEN
      airports."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 airports
  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
stateairport_​counttop_​5_​counties
CA984
countyairport_​countmajor_​facilitiesby_​facility
LOS ANGELES176
name
BUR - BURBANK-GLENDALE-PASADENA
LAX - LOS ANGELES INTL
LGB - LONG BEACH /DAUGHERTY FIELD/
fac_​typeairport_​count
HELIPORT151
AIRPORT23
SEAPLANE BASE2
SAN BERNARDINO71
name
ONT - ONTARIO INTL
fac_​typeairport_​count
AIRPORT47
HELIPORT24
ORANGE53
name
SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY
fac_​typeairport_​count
HELIPORT47
AIRPORT6
SAN DIEGO49
name
SAN - SAN DIEGO INTL-LINDBERGH FLD
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
KERN49
name
BFL - MEADOWS FIELD
IYK - INYOKERN
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
NY576
countyairport_​countmajor_​facilitiesby_​facility
SUFFOLK34
name
ISP - LONG ISLAND MAC ARTHUR
fac_​typeairport_​count
HELIPORT18
AIRPORT15
SEAPLANE BASE1
ERIE26
name
BUF - BUFFALO NIAGARA INTL
fac_​typeairport_​count
AIRPORT18
HELIPORT8
NIAGARA20
name
fac_​typeairport_​count
AIRPORT15
HELIPORT5
DUTCHESS20
name
fac_​typeairport_​count
HELIPORT12
AIRPORT8
ONONDAGA18
name
SYR - SYRACUSE HANCOCK INTL
fac_​typeairport_​count
AIRPORT13
HELIPORT4
SEAPLANE BASE1
MN507
countyairport_​countmajor_​facilitiesby_​facility
ST LOUIS28
name
DLH - DULUTH INTL
fac_​typeairport_​count
AIRPORT13
SEAPLANE BASE10
HELIPORT5
HENNEPIN23
name
MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/
fac_​typeairport_​count
HELIPORT9
SEAPLANE BASE8
AIRPORT6
CROW WING17
name
fac_​typeairport_​count
AIRPORT8
SEAPLANE BASE6
HELIPORT1
STOLPORT1
ULTRALIGHT1
DAKOTA17
name
fac_​typeairport_​count
AIRPORT14
SEAPLANE BASE3
ANOKA14
name
fac_​typeairport_​count
AIRPORT9
SEAPLANE BASE3
HELIPORT2
[
  {
    "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": "HELIPORT",
            "airport_count": 1
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          },
          {
            "fac_type": "ULTRALIGHT",
            "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,
    airports."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
      airports."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(airports."code",' - ',airports."full_name")
      END as "name__2",
    CASE WHEN group_set=3 THEN
      airports."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 airports
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set  ) as group_set
  WHERE ((airports."state"='CA')or((airports."state"='NY')or(airports."state"='MN')))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND airports."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
countymajor_​facilitiesby_​facility
--PUERTO RICO
name
BQN - RAFAEL HERNANDEZ
SJU - LUIS MUNOZ MARIN INTL
fac_​typeairport_​count
HELIPORT20
AIRPORT19
SEAPLANE BASE1
-TRUST TERR. OF PAC-
name
fac_​typeairport_​count
AIRPORT1
-VIRGIN ISLANDS-
name
STT - CYRIL E KING
STX - HENRY E ROHLSEN
fac_​typeairport_​count
HELIPORT4
SEAPLANE BASE3
AIRPORT2
ABBEVILLE
name
fac_​typeairport_​count
AIRPORT3
HELIPORT1
ACADIA
name
fac_​typeairport_​count
AIRPORT9
HELIPORT3
[
  {
    "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,
    airports_with_views."county" as "county__0",
    CASE WHEN group_set=1 THEN
      CONCAT(airports_with_views."code",' - ',airports_with_views."full_name")
      END as "name__1",
    CASE WHEN group_set=2 THEN
      airports_with_views."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 airports_with_views
  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 airports_with_views."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