What Is Malloy
Table Transpose
The dashboard style can be invoked on something that will render as a table # dashboard tag. When a query is rendered as a dashboard, dimensions aligned at the top, and agregates and nested queries float within the dashboard.
source: airports is duckdb.table('../data/airports.parquet') extend { measure: airport_count is count() view: by_state_and_county is { limit: 10 group_by: state aggregate: airport_count nest: by_fac_type is { group_by: fac_type aggregate: airport_count } } }
Queries in Malloy are often very complex and multifaceted, which makes them difficult to read in one nested table:
run: airports -> by_state_and_county[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "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_fac_type": [ { "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": "STOLPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_fac_type": [ { "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_fac_type": [ { "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_fac_type": [ { "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 } ] }, { "state": "OH", "airport_count": 749, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 537 }, { "fac_type": "HELIPORT", "airport_count": 201 }, { "fac_type": "STOLPORT", "airport_count": 4 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 1 } ] }, { "state": "IN", "airport_count": 643, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 497 }, { "fac_type": "HELIPORT", "airport_count": 115 }, { "fac_type": "ULTRALIGHT", "airport_count": 17 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "AK", "airport_count": 608, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 474 }, { "fac_type": "SEAPLANE BASE", "airport_count": 104 }, { "fac_type": "HELIPORT", "airport_count": 30 } ] }, { "state": "NY", "airport_count": 576, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 393 }, { "fac_type": "HELIPORT", "airport_count": 156 }, { "fac_type": "SEAPLANE BASE", "airport_count": 23 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 } ] }, { "state": "WI", "airport_count": 543, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 439 }, { "fac_type": "HELIPORT", "airport_count": 85 }, { "fac_type": "SEAPLANE BASE", "airport_count": 16 }, { "fac_type": "ULTRALIGHT", "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_fac_type" FROM __stage0 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10
In such cases, the # dashboard renderer is useful for making the results easier to read:
# dashboard run: airports -> by_state_and_county
[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 1389 }, { "fac_type": "HELIPORT", "airport_count": 435 }, { "fac_type": "STOLPORT", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 8 }, { "fac_type": "GLIDERPORT", "airport_count": 5 } ] }, { "state": "CA", "airport_count": 984, "by_fac_type": [ { "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_fac_type": [ { "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": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_fac_type": [ { "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_fac_type": [ { "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 } ] }, { "state": "OH", "airport_count": 749, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 537 }, { "fac_type": "HELIPORT", "airport_count": 201 }, { "fac_type": "STOLPORT", "airport_count": 4 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 1 } ] }, { "state": "IN", "airport_count": 643, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 497 }, { "fac_type": "HELIPORT", "airport_count": 115 }, { "fac_type": "ULTRALIGHT", "airport_count": 17 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "AK", "airport_count": 608, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 474 }, { "fac_type": "SEAPLANE BASE", "airport_count": 104 }, { "fac_type": "HELIPORT", "airport_count": 30 } ] }, { "state": "NY", "airport_count": 576, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 393 }, { "fac_type": "HELIPORT", "airport_count": 156 }, { "fac_type": "SEAPLANE BASE", "airport_count": 23 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "WI", "airport_count": 543, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 439 }, { "fac_type": "HELIPORT", "airport_count": 85 }, { "fac_type": "SEAPLANE BASE", "airport_count": 16 }, { "fac_type": "ULTRALIGHT", "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_fac_type" FROM __stage0 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10