Malloy Documentation
search
previousDashboards
Bar Chartsnext

Lists

VSCode document

Tables with one or two elements can be rendered as lists. Lists improve information density.

The examples below use the following models

document
source: airports is duckdb.table('../data/airports.parquet') extend {
  measure: airport_count is count()
}

Normal Table

document
run: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest: by_state is  {
    group_by: state
    aggregate: airport_count 
  }
}
QUERY RESULTS
faa_​regionairport_​countby_​state
AGL4,437
stateairport_​count
IL890
OH749
IN643
WI543
MN507
MI489
ND436
SD180
ASW3,268
stateairport_​count
TX1,845
LA500
OK443
AR299
NM181
ASO2,924
stateairport_​count
FL856
GA440
NC400
TN285
AL260
MS243
KY202
SC189
PR40
VI9
AEA2,586
stateairport_​count
PA804
NY576
VA421
NJ378
MD229
WV116
DE42
DC20
ANM2,102
stateairport_​count
WA484
OR441
CO425
MT259
ID238
UT140
WY115
ACE1,579
stateairport_​count
MO537
KS415
IA319
NE308
AWP1,503
stateairport_​count
CA984
AZ319
NV128
HI52
CQ11
AS4
GU3
MQ1
WQ1
ANE763
stateairport_​count
MA225
ME164
CT153
NH112
VT81
RI28
AAL608
stateairport_​count
AK608
∅23
stateairport_​count
∅23
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  },
  {
    "faa_region": "ACE",
    "airport_count": 1579,
    "by_state": [
      {
        "state": "MO",
        "airport_count": 537
      },
      {
        "state": "KS",
        "airport_count": 415
      },
      {
        "state": "IA",
        "airport_count": 319
      },
      {
        "state": "NE",
        "airport_count": 308
      }
    ]
  },
  {
    "faa_region": "AWP",
    "airport_count": 1503,
    "by_state": [
      {
        "state": "CA",
        "airport_count": 984
      },
      {
        "state": "AZ",
        "airport_count": 319
      },
      {
        "state": "NV",
        "airport_count": 128
      },
      {
        "state": "HI",
        "airport_count": 52
      },
      {
        "state": "CQ",
        "airport_count": 11
      },
      {
        "state": "AS",
        "airport_count": 4
      },
      {
        "state": "GU",
        "airport_count": 3
      },
      {
        "state": "MQ",
        "airport_count": 1
      },
      {
        "state": "WQ",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANE",
    "airport_count": 763,
    "by_state": [
      {
        "state": "MA",
        "airport_count": 225
      },
      {
        "state": "ME",
        "airport_count": 164
      },
      {
        "state": "CT",
        "airport_count": 153
      },
      {
        "state": "NH",
        "airport_count": 112
      },
      {
        "state": "VT",
        "airport_count": 81
      },
      {
        "state": "RI",
        "airport_count": 28
      }
    ]
  },
  {
    "faa_region": "AAL",
    "airport_count": 608,
    "by_state": [
      {
        "state": "AK",
        "airport_count": 608
      }
    ]
  },
  {
    "faa_region": null,
    "airport_count": 23,
    "by_state": [
      {
        "state": null,
        "airport_count": 23
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."faa_region" as "faa_region__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."state"
      END as "state__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
  "faa_region__0" as "faa_region",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_state"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

# list

With a list, just the first element in the table is shown.

document
run: airports -> {
  group_by: faa_region
  aggregate: airport_count
  # list
  nest: by_state is  {
    group_by: state
    aggregate: airport_count
  }
}
QUERY RESULTS
faa_​regionairport_​countby_​state
AGL4,437IL, OH, IN, WI, MN, MI, ND, SD
ASW3,268TX, LA, OK, AR, NM
ASO2,924FL, GA, NC, TN, AL, MS, KY, SC, PR, VI
AEA2,586PA, NY, VA, NJ, MD, WV, DE, DC
ANM2,102WA, OR, CO, MT, ID, UT, WY
ACE1,579MO, KS, IA, NE
AWP1,503CA, AZ, NV, HI, CQ, AS, GU, MQ, WQ
ANE763MA, ME, CT, NH, VT, RI
AAL608AK
∅23∅
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  },
  {
    "faa_region": "ACE",
    "airport_count": 1579,
    "by_state": [
      {
        "state": "MO",
        "airport_count": 537
      },
      {
        "state": "KS",
        "airport_count": 415
      },
      {
        "state": "IA",
        "airport_count": 319
      },
      {
        "state": "NE",
        "airport_count": 308
      }
    ]
  },
  {
    "faa_region": "AWP",
    "airport_count": 1503,
    "by_state": [
      {
        "state": "CA",
        "airport_count": 984
      },
      {
        "state": "AZ",
        "airport_count": 319
      },
      {
        "state": "NV",
        "airport_count": 128
      },
      {
        "state": "HI",
        "airport_count": 52
      },
      {
        "state": "CQ",
        "airport_count": 11
      },
      {
        "state": "AS",
        "airport_count": 4
      },
      {
        "state": "GU",
        "airport_count": 3
      },
      {
        "state": "MQ",
        "airport_count": 1
      },
      {
        "state": "WQ",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANE",
    "airport_count": 763,
    "by_state": [
      {
        "state": "MA",
        "airport_count": 225
      },
      {
        "state": "ME",
        "airport_count": 164
      },
      {
        "state": "CT",
        "airport_count": 153
      },
      {
        "state": "NH",
        "airport_count": 112
      },
      {
        "state": "VT",
        "airport_count": 81
      },
      {
        "state": "RI",
        "airport_count": 28
      }
    ]
  },
  {
    "faa_region": "AAL",
    "airport_count": 608,
    "by_state": [
      {
        "state": "AK",
        "airport_count": 608
      }
    ]
  },
  {
    "faa_region": null,
    "airport_count": 23,
    "by_state": [
      {
        "state": null,
        "airport_count": 23
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."faa_region" as "faa_region__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."state"
      END as "state__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
  "faa_region__0" as "faa_region",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_state"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

# list_detail

With list_detail the element and value are shown.

document
run: airports -> {
  group_by: faa_region
  aggregate: airport_count
  # list_detail
  nest: by_state is  {
    group_by: state
    aggregate: airport_count
  }
}
QUERY RESULTS
faa_​regionairport_​countby_​state
AGL4,437IL(890), OH(749), IN(643), WI(543), MN(507), MI(489), ND(436), SD(180)
ASW3,268TX(1,845), LA(500), OK(443), AR(299), NM(181)
ASO2,924FL(856), GA(440), NC(400), TN(285), AL(260), MS(243), KY(202), SC(189), PR(40), VI(9)
AEA2,586PA(804), NY(576), VA(421), NJ(378), MD(229), WV(116), DE(42), DC(20)
ANM2,102WA(484), OR(441), CO(425), MT(259), ID(238), UT(140), WY(115)
ACE1,579MO(537), KS(415), IA(319), NE(308)
AWP1,503CA(984), AZ(319), NV(128), HI(52), CQ(11), AS(4), GU(3), MQ(1), WQ(1)
ANE763MA(225), ME(164), CT(153), NH(112), VT(81), RI(28)
AAL608AK(608)
∅23∅(23)
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  },
  {
    "faa_region": "ACE",
    "airport_count": 1579,
    "by_state": [
      {
        "state": "MO",
        "airport_count": 537
      },
      {
        "state": "KS",
        "airport_count": 415
      },
      {
        "state": "IA",
        "airport_count": 319
      },
      {
        "state": "NE",
        "airport_count": 308
      }
    ]
  },
  {
    "faa_region": "AWP",
    "airport_count": 1503,
    "by_state": [
      {
        "state": "CA",
        "airport_count": 984
      },
      {
        "state": "AZ",
        "airport_count": 319
      },
      {
        "state": "NV",
        "airport_count": 128
      },
      {
        "state": "HI",
        "airport_count": 52
      },
      {
        "state": "CQ",
        "airport_count": 11
      },
      {
        "state": "AS",
        "airport_count": 4
      },
      {
        "state": "GU",
        "airport_count": 3
      },
      {
        "state": "MQ",
        "airport_count": 1
      },
      {
        "state": "WQ",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANE",
    "airport_count": 763,
    "by_state": [
      {
        "state": "MA",
        "airport_count": 225
      },
      {
        "state": "ME",
        "airport_count": 164
      },
      {
        "state": "CT",
        "airport_count": 153
      },
      {
        "state": "NH",
        "airport_count": 112
      },
      {
        "state": "VT",
        "airport_count": 81
      },
      {
        "state": "RI",
        "airport_count": 28
      }
    ]
  },
  {
    "faa_region": "AAL",
    "airport_count": 608,
    "by_state": [
      {
        "state": "AK",
        "airport_count": 608
      }
    ]
  },
  {
    "faa_region": null,
    "airport_count": 23,
    "by_state": [
      {
        "state": null,
        "airport_count": 23
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."faa_region" as "faa_region__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      airports."state"
      END as "state__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
  "faa_region__0" as "faa_region",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_state"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST