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_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,
    base."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
      base."state"
      END as "state__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
  "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_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": "WQ",
        "airport_count": 1
      },
      {
        "state": "MQ",
        "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,
    base."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
      base."state"
      END as "state__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
  "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_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": "WQ",
        "airport_count": 1
      },
      {
        "state": "MQ",
        "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,
    base."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
      base."state"
      END as "state__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
  "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