Malloy Documentation
search

This guide introduces the basics of querying data and building a semantic model with the Malloy language. By the end of this tutorial, you will understand how to use Malloy to run queries, build re-usable data models, and do analysis on your data that is nearly impossible in SQL.

The easiest way to follow along is by going to the interactive notebook version of this tutorial. The link will launch a browser-based VSCode environment and ask you to install the Malloy extension. Once installed, navigate back to the quickstart notebook file, and dive in.

If you'd like to run Malloy locally on your laptop instead, follow the setup instructions to install the VSCode extension and connect to a database.

A Simple Select Statement

The following query is equivalent to SELECT id, code, city FROM airports LIMIT 10 in SQL:

document
run: duckdb.table('../data/airports.parquet') -> {
  select:
    id
    code
    city
  limit: 10
}
QUERY RESULTS
idcodecity
19,7831Q9MILI ISLAND
19,777Q51KILI ISLAND
19,7873N1TAORA IS MALOELAP ATOLL
19,78903NUTIRIK ISLAND
19,774ANGANGAUR ISLAND
[
  {
    "id": 19783,
    "code": "1Q9",
    "city": "MILI ISLAND"
  },
  {
    "id": 19777,
    "code": "Q51",
    "city": "KILI ISLAND"
  },
  {
    "id": 19787,
    "code": "3N1",
    "city": "TAORA IS  MALOELAP ATOLL"
  },
  {
    "id": 19789,
    "code": "03N",
    "city": "UTIRIK ISLAND"
  },
  {
    "id": 19774,
    "code": "ANG",
    "city": "ANGAUR ISLAND"
  }
]
SELECT 
   base."id" as "id",
   base."code" as "code",
   base."city" as "city"
FROM '../data/airports.parquet' as base
LIMIT 10

Let's break down each part of this query.

  • run: is the opening statement that indicates we're starting to write a query

  • duckdb.table('../data/airports.parquet') defines the source for the query. The table() method creates a source from a table or view in the database.

    • A source is similar to a table or view in SQL, but Malloy sources can include additional information like joins and measures. We'll cover this in depth later on.

  • The -> operator begins the query. Queries take the form source -> { ... }, with the query logic specified inside of the curly braces.

  • select: is equivalent to SELECT in SQL. In this clause, we select the id, code, and city columns from the table.

  • limit: 10 limits the result set of the query to the first 10 items.

Query Operators

In SQL, the SELECT command does two very different things. A SELECT with a GROUP BY aggregates data according to the GROUP BY clause and produces aggregate calculation against every calculation not in the GROUP BY. In Malloy, the query operator for this is group_by:. Calculations involving data in the group are made using aggregate:.

The second type of SELECT in SQL does not perform any aggregation; All rows in the input table, unless filtered in some way, show up in the output table. In Malloy, this command is select:.

Aggregate

In the query below, the data will be grouped by state and county, and will produce an aggregate calculation for airport_count and average_elevation.

document
run: duckdb.table('../data/airports.parquet') -> {
  group_by:
    state
    county
  aggregate:
    airport_count is count()
    average_elevation is avg(elevation)
}
QUERY RESULTS
statecountyairport_​countaverage_​elevation
CALOS ANGELES176689.165
TXHARRIS135106.467
AZMARICOPA1171,395.667
CASAN BERNARDINO712,376.056
TXTARRANT63646.794
[
  {
    "state": "CA",
    "county": "LOS ANGELES",
    "airport_count": 176,
    "average_elevation": 689.1647727272727
  },
  {
    "state": "TX",
    "county": "HARRIS",
    "airport_count": 135,
    "average_elevation": 106.46666666666667
  },
  {
    "state": "AZ",
    "county": "MARICOPA",
    "airport_count": 117,
    "average_elevation": 1395.6666666666667
  },
  {
    "state": "CA",
    "county": "SAN BERNARDINO",
    "airport_count": 71,
    "average_elevation": 2376.056338028169
  },
  {
    "state": "TX",
    "county": "TARRANT",
    "airport_count": 63,
    "average_elevation": 646.7936507936508
  }
]
SELECT 
   base."state" as "state",
   base."county" as "county",
   COUNT( 1) as "airport_count",
   AVG(base."elevation") as "average_elevation"
FROM '../data/airports.parquet' as base
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST

Select

A select: statement produces a list of fields. For every row in the input table, there is a row in the output table. This is similar to a simple SELECT statement in SQL with no aggregations.

document
run: duckdb.table('../data/airports.parquet') -> {
  select: code, full_name, city, county
  where: county = 'SANTA CRUZ'
  limit: 10
}
QUERY RESULTS
codefull_​namecitycounty
2AZ8TUBAC ULTRALIGHT FLIGHTPARKTUBACSANTA CRUZ
OLSNOGALES INTLNOGALESSANTA CRUZ
NSISAN NICOLAS ISLAND NOLFSAN NICOLAS ISLANDSANTA CRUZ
CL77BONNY DOON VILLAGESANTA CRUZSANTA CRUZ
CA37DOMINICAN SANTA CRUZ HOSPITALSANTA CRUZSANTA CRUZ
[
  {
    "code": "2AZ8",
    "full_name": "TUBAC ULTRALIGHT FLIGHTPARK",
    "city": "TUBAC",
    "county": "SANTA CRUZ"
  },
  {
    "code": "OLS",
    "full_name": "NOGALES INTL",
    "city": "NOGALES",
    "county": "SANTA CRUZ"
  },
  {
    "code": "NSI",
    "full_name": "SAN NICOLAS ISLAND NOLF",
    "city": "SAN NICOLAS ISLAND",
    "county": "SANTA CRUZ"
  },
  {
    "code": "CL77",
    "full_name": "BONNY DOON VILLAGE",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ"
  },
  {
    "code": "CA37",
    "full_name": "DOMINICAN SANTA CRUZ HOSPITAL",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ"
  }
]
SELECT 
   base."code" as "code",
   base."full_name" as "full_name",
   base."city" as "city",
   base."county" as "county"
FROM '../data/airports.parquet' as base
WHERE base."county"='SANTA CRUZ'
LIMIT 10

For the most part, operations can be placed in any order within a query. A where can come before or after a project, and limit can be placed anywhere as well. The above query could also be written:

document
run: duckdb.table('../data/airports.parquet') -> {
  limit: 10
  where: county = 'SANTA CRUZ'
  select: code, full_name, city, county
}
QUERY RESULTS
codefull_​namecitycounty
2AZ8TUBAC ULTRALIGHT FLIGHTPARKTUBACSANTA CRUZ
OLSNOGALES INTLNOGALESSANTA CRUZ
NSISAN NICOLAS ISLAND NOLFSAN NICOLAS ISLANDSANTA CRUZ
CL77BONNY DOON VILLAGESANTA CRUZSANTA CRUZ
CA37DOMINICAN SANTA CRUZ HOSPITALSANTA CRUZSANTA CRUZ
[
  {
    "code": "2AZ8",
    "full_name": "TUBAC ULTRALIGHT FLIGHTPARK",
    "city": "TUBAC",
    "county": "SANTA CRUZ"
  },
  {
    "code": "OLS",
    "full_name": "NOGALES INTL",
    "city": "NOGALES",
    "county": "SANTA CRUZ"
  },
  {
    "code": "NSI",
    "full_name": "SAN NICOLAS ISLAND NOLF",
    "city": "SAN NICOLAS ISLAND",
    "county": "SANTA CRUZ"
  },
  {
    "code": "CL77",
    "full_name": "BONNY DOON VILLAGE",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ"
  },
  {
    "code": "CA37",
    "full_name": "DOMINICAN SANTA CRUZ HOSPITAL",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ"
  }
]
SELECT 
   base."code" as "code",
   base."full_name" as "full_name",
   base."city" as "city",
   base."county" as "county"
FROM '../data/airports.parquet' as base
WHERE base."county"='SANTA CRUZ'
LIMIT 10

Everything has a Name

In Malloy, all output fields have names. This means that any time a query includes a field with a calculated value, like a scalar or aggregate function, it must be named (unlike SQL, which allows un-named expressions).

document
run: duckdb.table('../data/airports.parquet') -> {
  aggregate: max_elevation is max(elevation)
}
QUERY RESULTS
max_​elevation
12,442
[
  {
    "max_elevation": 12442
  }
]
SELECT 
   max(base."elevation") as "max_elevation"
FROM '../data/airports.parquet' as base

Notice that Malloy uses the form name is value instead of SQL's value AS name. Having the output column name written first makes it easier for someone reading the code to visualize the resulting query structure.

Named objects, like columns from a table or fields defined in a source, can be included in field lists without an is:

document
run: duckdb.table('../data/airports.parquet') -> {
  select:
    full_name
    elevation
}
QUERY RESULTS
full_​nameelevation
MILI4
KILI5
MALOELAP4
UTIRIK4
ANGAUR AIRSTRIP20
[
  {
    "full_name": "MILI",
    "elevation": 4
  },
  {
    "full_name": "KILI",
    "elevation": 5
  },
  {
    "full_name": "MALOELAP",
    "elevation": 4
  },
  {
    "full_name": "UTIRIK",
    "elevation": 4
  },
  {
    "full_name": "ANGAUR AIRSTRIP",
    "elevation": 20
  }
]
SELECT 
   base."full_name" as "full_name",
   base."elevation" as "elevation"
FROM '../data/airports.parquet' as base

Expressions

Many SQL expressions will work unchanged in Malloy, and many functions available in Standard SQL are usable in Malloy as well. This makes expressions fairly straightforward to understand, given a knowledge of SQL.

document
run: duckdb.table('../data/airports.parquet') -> {
  group_by: county_and_state is concat(county, ', ', state)
  aggregate:
    airport_count is count()
    max_elevation is max(elevation)
    min_elevation is min(elevation)
    avg_elevation is avg(elevation)
}
QUERY RESULTS
county_​and_​stateairport_​countmax_​elevationmin_​elevationavg_​elevation
LOS ANGELES, CA1763,4200689.165
HARRIS, TX1357749106.467
MARICOPA, AZ1173,9957371,395.667
SAN BERNARDINO, CA716,7486312,376.056
TARRANT, TX63895472646.794
[
  {
    "county_and_state": "LOS ANGELES, CA",
    "airport_count": 176,
    "max_elevation": 3420,
    "min_elevation": 0,
    "avg_elevation": 689.1647727272727
  },
  {
    "county_and_state": "HARRIS, TX",
    "airport_count": 135,
    "max_elevation": 774,
    "min_elevation": 9,
    "avg_elevation": 106.46666666666667
  },
  {
    "county_and_state": "MARICOPA, AZ",
    "airport_count": 117,
    "max_elevation": 3995,
    "min_elevation": 737,
    "avg_elevation": 1395.6666666666667
  },
  {
    "county_and_state": "SAN BERNARDINO, CA",
    "airport_count": 71,
    "max_elevation": 6748,
    "min_elevation": 631,
    "avg_elevation": 2376.056338028169
  },
  {
    "county_and_state": "TARRANT, TX",
    "airport_count": 63,
    "max_elevation": 895,
    "min_elevation": 472,
    "avg_elevation": 646.7936507936508
  }
]
SELECT 
   CONCAT(base."county",', ',base."state") as "county_and_state",
   COUNT( 1) as "airport_count",
   max(base."elevation") as "max_elevation",
   min(base."elevation") as "min_elevation",
   AVG(base."elevation") as "avg_elevation"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST

The basic types of Malloy expressions are string, number, boolean, date, and timestamp.

Sources: the Basic Structure for Modeling and Reuse

One of the main benefits of Malloy is the ability to save common calculations into a data model. The data model is made of sources, which can be thought of as tables or views, but with additional information, such as joins, dimensions and measures.

In the example below, we create a source object named airports and add a dimension calculation for county_and_state and measure calculation for airport_count. Dimensions can be used in group_by, project and where. Measures can be used in aggregate and having.

document
source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: county_and_state is concat(county, ', ', state)
  measure: airport_count is count()
  measure: average_elevation is avg(elevation)
}

malloy run: airports -> { group_by: county_and_state aggregate: airport_count }

Sources that are defined in one file can be imported into another using import "path/to/some/file.malloy". For example, if the airports source above were defined in a file called flights.malloy, you could create a new file that imports it and immediately start using the airports source:

import "airports.malloy" 

run: airports -> {
  group_by: county_and_state
  aggregate: average_elevation
}

Sources can also contain named views. These views are useful for building nested queries (covered later) or for saving a query operation so it can re-used again and again without having to rewrite it.

document
source: airports_with_named_query is duckdb.table('../data/airports.parquet') extend {
  dimension: county_and_state is concat(county, ', ', state)
  measure: airport_count is count()
  measure: average_elevation is avg(elevation)

  view: top_county_and_state is {
    group_by: county_and_state
    aggregate: airport_count
    limit:10
  }
}

// The view can now be referenced by name 
// and run without having to rewrite the logic:
run: airports_with_named_query -> top_county_and_state
QUERY RESULTS
county_​and_​stateairport_​count
LOS ANGELES, CA176
HARRIS, TX135
MARICOPA, AZ117
SAN BERNARDINO, CA71
TARRANT, TX63
[
  {
    "county_and_state": "LOS ANGELES, CA",
    "airport_count": 176
  },
  {
    "county_and_state": "HARRIS, TX",
    "airport_count": 135
  },
  {
    "county_and_state": "MARICOPA, AZ",
    "airport_count": 117
  },
  {
    "county_and_state": "SAN BERNARDINO, CA",
    "airport_count": 71
  },
  {
    "county_and_state": "TARRANT, TX",
    "airport_count": 63
  }
]
SELECT 
   CONCAT(airports_with_named_query."county",', ',airports_with_named_query."state") as "county_and_state",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as airports_with_named_query
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10

Joins

Joins are declared as part of a source. When joining a source to another, it brings with it all child joins.

document
source: aircraft_models is duckdb.table('../data/aircraft_models.parquet') extend {
  primary_key: aircraft_model_code
}

source: aircraft is duckdb.table('../data/aircraft.parquet') extend {
  primary_key: tail_num
  join_one: aircraft_models 
    on aircraft_model_code = aircraft_models.aircraft_model_code
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: aircraft on tail_num = aircraft.tail_num
}

run: flights -> {
  where: dep_time ? @2003-01
  group_by: aircraft.aircraft_models.manufacturer
  aggregate:
    flight_count is count()
    aircraft_count is aircraft.count()
    average_seats_per_model is aircraft.aircraft_models.seats.avg()
}
QUERY RESULTS
manufacturerflight_​countaircraft_​countaverage_​seats_​per_​model
BOEING2,43220193.25
AIRBUS INDUSTRIE8778177
MCDONNELL DOUGLAS4865172
EMBRAER388441.333
AEROSPATIALE/ALENIA225176
[
  {
    "manufacturer": "BOEING",
    "flight_count": 2432,
    "aircraft_count": 20,
    "average_seats_per_model": 193.25
  },
  {
    "manufacturer": "AIRBUS INDUSTRIE",
    "flight_count": 877,
    "aircraft_count": 8,
    "average_seats_per_model": 177
  },
  {
    "manufacturer": "MCDONNELL DOUGLAS",
    "flight_count": 486,
    "aircraft_count": 5,
    "average_seats_per_model": 172
  },
  {
    "manufacturer": "EMBRAER",
    "flight_count": 388,
    "aircraft_count": 4,
    "average_seats_per_model": 41.333333333333336
  },
  {
    "manufacturer": "AEROSPATIALE/ALENIA",
    "flight_count": 225,
    "aircraft_count": 1,
    "average_seats_per_model": 76
  }
]
SELECT 
   aircraft_models_0."manufacturer" as "manufacturer",
   COUNT( 1) as "flight_count",
   COUNT(DISTINCT aircraft_0."tail_num") as "aircraft_count",
   (
        SELECT AVG(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:aircraft_models_0."aircraft_model_code", val: aircraft_models_0."seats"})) a
        )
      ) as "average_seats_per_model"
FROM '../data/flights.parquet' as flights
 LEFT JOIN '../data/aircraft.parquet' AS aircraft_0
  ON flights."tail_num"=aircraft_0."tail_num"
 LEFT JOIN '../data/aircraft_models.parquet' AS aircraft_models_0
  ON aircraft_0."aircraft_model_code"=aircraft_models_0."aircraft_model_code"
WHERE (flights."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2003-02-01 00:00:00')
GROUP BY 1
ORDER BY 2 desc NULLS LAST

In this example, the aircraft source is joined to flights, and aircraft_models is joined via aircraft. These examples explicitly name both keys—this same syntax can be used to write more complex joins.

Now, any query that uses the flights source has access to fields in both aircraft and aircraft_models without having to explicitly specify the join condition. The joins are specified once in the source, and usable by any query on flights.

An ad hoc join can also be specified in a query block. In the query below, we join in the airports table using the destination column as a join key, then compute the top 5 destination airports by flight count.

document
source: airports2 is duckdb.table('../data/airports.parquet')

source: flights2 is duckdb.table('../data/flights.parquet') extend {
  join_one: airports2 on destination = airports2.code
}

run: flights2 -> {
  group_by: airports2.full_name
  aggregate: flight_count is count()
  limit: 5
}
QUERY RESULTS
full_​nameflight_​count
THE WILLIAM B HARTSFIELD ATLANTA INTL17,832
DALLAS/FORT WORTH INTERNATIONAL17,776
CHICAGO O'HARE INTL14,213
PHOENIX SKY HARBOR INTL12,477
MC CARRAN INTL11,092
[
  {
    "full_name": "THE WILLIAM B HARTSFIELD ATLANTA INTL",
    "flight_count": 17832
  },
  {
    "full_name": "DALLAS/FORT WORTH INTERNATIONAL",
    "flight_count": 17776
  },
  {
    "full_name": "CHICAGO O'HARE INTL",
    "flight_count": 14213
  },
  {
    "full_name": "PHOENIX SKY HARBOR INTL",
    "flight_count": 12477
  },
  {
    "full_name": "MC CARRAN INTL",
    "flight_count": 11092
  }
]
SELECT 
   airports2_0."full_name" as "full_name",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as flights2
 LEFT JOIN '../data/airports.parquet' AS airports2_0
  ON flights2."destination"=airports2_0."code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Filtering

When working with data, filtering is something you do in almost every query. Malloy provides consistent syntax for filtering everywhere within a query. The most basic type of filter is applied using a where: clause, very similar to a WHERE clause in SQL.

The following query grabs the top 5 counties in California with the highest airport count:

document
run: duckdb.table('../data/airports.parquet') -> {
  where: state = 'CA'
  limit: 5
  group_by: county
  aggregate: airport_count is count()
}
QUERY RESULTS
countyairport_​count
LOS ANGELES176
SAN BERNARDINO71
ORANGE53
SAN DIEGO49
KERN49
[
  {
    "county": "LOS ANGELES",
    "airport_count": 176
  },
  {
    "county": "SAN BERNARDINO",
    "airport_count": 71
  },
  {
    "county": "ORANGE",
    "airport_count": 53
  },
  {
    "county": "SAN DIEGO",
    "airport_count": 49
  },
  {
    "county": "KERN",
    "airport_count": 49
  }
]
SELECT 
   base."county" as "county",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as base
WHERE base."state"='CA'
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Filters can also be applied to sources:

document
source: airports_in_california is duckdb.table('../data/airports.parquet') extend {
  where: state = 'CA'
}

run: airports_in_california -> {
  limit: 5
  group_by: county
  aggregate: airport_count is count()
}
QUERY RESULTS
countyairport_​count
LOS ANGELES176
SAN BERNARDINO71
ORANGE53
SAN DIEGO49
KERN49
[
  {
    "county": "LOS ANGELES",
    "airport_count": 176
  },
  {
    "county": "SAN BERNARDINO",
    "airport_count": 71
  },
  {
    "county": "ORANGE",
    "airport_count": 53
  },
  {
    "county": "SAN DIEGO",
    "airport_count": 49
  },
  {
    "county": "KERN",
    "airport_count": 49
  }
]
SELECT 
   airports_in_california."county" as "county",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as airports_in_california
WHERE airports_in_california."state"='CA'
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Any query run on the airports_in_california source will run against the airports table, and always include the filter in state = 'CA'.

Filtering Measures

A filter on an aggregate calculation (a measure) narrows down the data used in that specific calculation. In the example below, the calculations for airports and heliports are filtered separately.

document
run: duckdb.table('../data/airports.parquet') -> {
  group_by: state
  aggregate:
    airports is count() { where: fac_type = 'AIRPORT' }
    heliports is count() { where: fac_type = 'HELIPORT' }
    total is count()
}
QUERY RESULTS
stateairportsheliportstotal
TX1,3894351,845
IL625245890
CA569396984
OH537201749
FL511280856
[
  {
    "state": "TX",
    "airports": 1389,
    "heliports": 435,
    "total": 1845
  },
  {
    "state": "IL",
    "airports": 625,
    "heliports": 245,
    "total": 890
  },
  {
    "state": "CA",
    "airports": 569,
    "heliports": 396,
    "total": 984
  },
  {
    "state": "OH",
    "airports": 537,
    "heliports": 201,
    "total": 749
  },
  {
    "state": "FL",
    "airports": 511,
    "heliports": 280,
    "total": 856
  }
]
SELECT 
   base."state" as "state",
   COUNT( CASE WHEN base."fac_type"='AIRPORT' THEN 1 END) as "airports",
   COUNT( CASE WHEN base."fac_type"='HELIPORT' THEN 1 END) as "heliports",
   COUNT( 1) as "total"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST

In SQL, this same calculation is often done using CASE statements inside of the aggregates, which is verbose and difficult to read. A query like the above would look like:

SELECT
   state
   , SUM(CASE WHEN fac_type = 'AIRPORT' THEN 1 ELSE 0 END) AS airports
   , SUM(CASE WHEN fac_type = 'HELIPORT' THEN 1 ELSE 0 END) AS heliports
   , COUNT(*) AS total
FROM `malloy-data.faa.airports`
GROUP BY state

Nested Queries

The next several examples will use this simple source definition:

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

Nested Views

In Malloy, views can be nested to produce a nested query with subtables on each output row.

document
run: airports -> {
  group_by: state
  aggregate: airport_count
  nest: by_facility is  {
    group_by: fac_type
    aggregate: airport_count
    limit: 3
  }
}
QUERY RESULTS
stateairport_​countby_​facility
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
ULTRALIGHT8
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
[
  {
    "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
      }
    ]
  },
  {
    "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
      }
    ]
  },
  {
    "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
      }
    ]
  },
  {
    "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
      }
    ]
  },
  {
    "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
      }
    ]
  }
]
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)[1:3],[]) as "by_facility"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Here we can see that the by_facility column of the output table contains a nested subtable on each row. by_facility contains the counts for the top 3 facility types for each state, i.e., the number of airports, heliports, and stolports in Texas, the number of airports, heliports, and seaplane bases in California, etc.

When a view is nested inside another view, each output row of the outer view will have a nested table for the inner view which only includes data limited to that row.

Views can be nested infinitely, allowing for rich, complex output structures. A view may always include another nested view, regardless of depth.

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
KERN49
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
SAN DIEGO49
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
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": "KERN",
        "airport_count": 49,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 41
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      },
      {
        "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
          }
        ]
      }
    ]
  },
  {
    "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 isolated to any level of nesting. In the following example, we limit the major_facilities view to only airports where major is 'Y'. This particular filter applies only to major_facilities, and not to other parts of the outer query.

document
run: airports -> {
  where: state = 'CA'
  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
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
KERN49
name
BFL (MEADOWS FIELD)
IYK (INYOKERN)
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
SAN DIEGO49
name
SAN (SAN DIEGO INTL-LINDBERGH FLD)
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
[
  {
    "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": "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
      }
    ]
  },
  {
    "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
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    airports."county" as "county__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "airport_count__0",
    CASE WHEN group_set=1 THEN
      CONCAT(airports."code",' (',airports."full_name",')')
      END as "name__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
  WHERE (airports."state"='CA')
  AND ((group_set NOT IN (1) OR (group_set IN (1) AND airports."major"='Y')))
  GROUP BY 1,2,4,5
)
SELECT
  "county__0" as "county",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  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 2 desc NULLS LAST

Dates and Timestamps

Working with time in data is often needlessly complex; Malloy has built in constructs to simplify many time-related operations. This section gives a brief introduction to some of these tools, but for more details see the Time Ranges section.

Time Literals

Literals of type date and timestamp are notated with an @, e.g. @2003-03-29 or @1994-07-14 10:23:59. Similarly, years (@2021), quarters (@2020-Q1), months (@2019-03), weeks (@WK2021-08-01), and minutes (@2017-01-01 10:53) can be expressed.

Time literals can be used as values, but are more often useful in filters. For example, the following query shows the number of flights in 2003.

document
run: duckdb.table('../data/flights.parquet') -> {
  where: dep_time ? @2003 
  aggregate: flight_count is count()
}
QUERY RESULTS
flight_​count
58,676
[
  {
    "flight_count": 58676
  }
]
SELECT 
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as base
WHERE (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(base."dep_time"<TIMESTAMP '2004-01-01 00:00:00')

There is a special time literal now, referring to the current timestamp, which allows for relative time filters.

document
run: duckdb.table('../data/flights.parquet') -> {
  where: dep_time > now - 6 hours
  aggregate: flights_last_6_hours is count()
}
QUERY RESULTS
flights_​last_​6_​hours
0
[
  {
    "flights_last_6_hours": 0
  }
]
SELECT 
   COUNT( 1) as "flights_last_6_hours"
FROM '../data/flights.parquet' as base
WHERE base."dep_time">LOCALTIMESTAMP - INTERVAL (6) hour

Truncation

Time values can be truncated to a given timeframe, which can be second, minute, hour, day, week, month, quarter, or year.

document
run: duckdb.table('../data/flights.parquet') -> {
  group_by:
    flight_year is dep_time.year
    flight_month is dep_time.month
  aggregate: flight_count is count()
}
QUERY RESULTS
flight_​yearflight_​monthflight_​count
20052005-025,707
20052005-086,415
20052005-015,997
20052005-035,732
20052005-115,533
[
  {
    "flight_year": "2005-01-01T00:00:00.000Z",
    "flight_month": "2005-02-01T00:00:00.000Z",
    "flight_count": 5707
  },
  {
    "flight_year": "2005-01-01T00:00:00.000Z",
    "flight_month": "2005-08-01T00:00:00.000Z",
    "flight_count": 6415
  },
  {
    "flight_year": "2005-01-01T00:00:00.000Z",
    "flight_month": "2005-01-01T00:00:00.000Z",
    "flight_count": 5997
  },
  {
    "flight_year": "2005-01-01T00:00:00.000Z",
    "flight_month": "2005-03-01T00:00:00.000Z",
    "flight_count": 5732
  },
  {
    "flight_year": "2005-01-01T00:00:00.000Z",
    "flight_month": "2005-11-01T00:00:00.000Z",
    "flight_count": 5533
  }
]
SELECT 
   DATE_TRUNC('year', base."dep_time") as "flight_year",
   DATE_TRUNC('month', base."dep_time") as "flight_month",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1,2
ORDER BY 1 desc NULLS LAST

Extraction

Numeric values can be extracted from time values, e.g. day_of_year(some_date) or minute(some_time). See the full list of extraction functions here.

document
run: duckdb.table('../data/flights.parquet') -> {
  order_by: 1
  group_by: day_of_week is day(dep_time)
  aggregate: flight_count is count()
}
QUERY RESULTS
day_​of_​weekflight_​count
111,265
211,389
311,060
411,264
511,201
611,401
711,512
[
  {
    "day_of_week": 1,
    "flight_count": 11265
  },
  {
    "day_of_week": 2,
    "flight_count": 11389
  },
  {
    "day_of_week": 3,
    "flight_count": 11060
  },
  {
    "day_of_week": 4,
    "flight_count": 11264
  },
  {
    "day_of_week": 5,
    "flight_count": 11201
  },
  {
    "day_of_week": 6,
    "flight_count": 11401
  },
  {
    "day_of_week": 7,
    "flight_count": 11512
  }
]
SELECT 
   EXTRACT(day FROM base."dep_time") as "day_of_week",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 1 ASC NULLS LAST

Time Ranges

Two kinds of time ranges are given special syntax: the range between two times and the range starting at some time for some duration. These are represented like @2003 to @2005 and @2004-Q1 for 6 quarters respectively. These ranges can be used in filters just like time literals.

document
run: duckdb.table('../data/flights.parquet') -> {
  where: dep_time ? @2003 to @2005
  aggregate: flight_count is count()
}
QUERY RESULTS
flight_​count
127,073
[
  {
    "flight_count": 127073
  }
]
SELECT 
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as base
WHERE (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(base."dep_time"<TIMESTAMP '2005-01-01 00:00:00')

Time literals and truncations can also behave like time ranges. Each kind of time literal has an implied duration that takes effect when it is used in a comparison, e.g. @2003 represents the whole of the year 2003, and @2004-Q1 lasts the whole 3 months of the quarter. Similarly, when a time value is truncated, it takes on the timeframe from the truncation, e.g. now.month means the entirety of the current month.

When a time range is used in a comparison, = checks for "is in the range", > "is after", and < "is before." So some_time > @2003 filters dates starting on January 1, 2004, while some_time = @2003 filters to dates in the year 2003.

document
run: duckdb.table('../data/flights.parquet') -> {
  where: dep_time > @2003
  limit: 3
  order_by: departure_date asc
  group_by: departure_date is dep_time.day
  aggregate: flight_count is count()
}
QUERY RESULTS
departure_​dateflight_​count
2004-01-01144
2004-01-02155
2004-01-03134
[
  {
    "departure_date": "2004-01-01T00:00:00.000Z",
    "flight_count": 144
  },
  {
    "departure_date": "2004-01-02T00:00:00.000Z",
    "flight_count": 155
  },
  {
    "departure_date": "2004-01-03T00:00:00.000Z",
    "flight_count": 134
  }
]
SELECT 
   DATE_TRUNC('day', base."dep_time") as "departure_date",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as base
WHERE base."dep_time">=TIMESTAMP '2004-01-01 00:00:00'
GROUP BY 1
ORDER BY 1 asc NULLS LAST
LIMIT 3

Pipelines and Multi-stage Queries

The output from one stage of a query can be passed into another stage using ->. For example, we'll start with this query which outputs, for California and New York, the total number of airports, as well as the number of airports in each county.

document
run: airports -> {
  where: state = 'CA' | 'NY'
  group_by: state
  aggregate: airport_count
  nest: by_county is  {
    group_by: county
    aggregate: airport_count
  }
}
QUERY RESULTS
stateairport_​countby_​county
CA984
countyairport_​count
LOS ANGELES176
SAN BERNARDINO71
ORANGE53
SAN DIEGO49
KERN49
RIVERSIDE46
FRESNO40
SACRAMENTO24
SANTA BARBARA22
SAN JOAQUIN20
VENTURA19
TULARE16
SUTTER16
KINGS15
EL DORADO15
MERCED15
ALAMEDA14
SAN LUIS OBISPO14
SISKIYOU13
MONTEREY13
SONOMA12
IMPERIAL12
MADERA12
SHASTA11
MODOC11
BUTTE11
SANTA CLARA10
STANISLAUS10
NEVADA10
CONTRA COSTA9
TEHAMA9
COLUSA9
YOLO9
INYO9
HUMBOLDT9
MENDOCINO8
SOLANO8
PLACER8
LAKE8
NAPA8
PLUMAS8
SANTA CRUZ8
TUOLUMNE7
TRINITY7
AMADOR7
MONO6
LASSEN6
MARIN6
CALAVERAS5
YUBA5
SAN BENITO4
SAN MATEO4
GLENN4
DEL NORTE3
MARIPOSA3
SAN FRANCISCO3
SIERRA2
ALPINE2
ESMERALDA1
NY576
countyairport_​count
SUFFOLK34
ERIE26
NIAGARA20
DUTCHESS20
ONONDAGA18
ONEIDA18
ULSTER17
ORANGE16
ONTARIO15
NASSAU15
CHAUTAUQUA14
DELAWARE13
ALBANY13
MONROE13
SARATOGA12
OSWEGO12
MONTGOMERY12
CATTARAUGUS11
QUEENS11
WESTCHESTER11
ESSEX10
WAYNE10
SULLIVAN10
WYOMING9
JEFFERSON9
STEUBEN9
SCHOHARIE9
ORLEANS9
CLINTON8
COLUMBIA8
FULTON8
LIVINGSTON8
CHEMUNG8
ST LAWRENCE8
GREENE8
CAYUGA8
TOMPKINS7
RENSSELAER7
BROOME7
SCHUYLER7
SCHENECTADY7
HERKIMER7
HAMILTON7
NEW YORK7
MADISON7
OTSEGO6
GENESEE6
CHENANGO5
WASHINGTON4
ALLEGANY4
SENECA4
YATES4
WARREN4
TIOGA4
ROCKLAND4
CORTLAND2
FRANKLIN2
LEWIS1
PUTNAM1
BRONX1
KINGS1
[
  {
    "state": "CA",
    "airport_count": 984,
    "by_county": [
      {
        "county": "LOS ANGELES",
        "airport_count": 176
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 71
      },
      {
        "county": "ORANGE",
        "airport_count": 53
      },
      {
        "county": "SAN DIEGO",
        "airport_count": 49
      },
      {
        "county": "KERN",
        "airport_count": 49
      },
      {
        "county": "RIVERSIDE",
        "airport_count": 46
      },
      {
        "county": "FRESNO",
        "airport_count": 40
      },
      {
        "county": "SACRAMENTO",
        "airport_count": 24
      },
      {
        "county": "SANTA BARBARA",
        "airport_count": 22
      },
      {
        "county": "SAN JOAQUIN",
        "airport_count": 20
      },
      {
        "county": "VENTURA",
        "airport_count": 19
      },
      {
        "county": "TULARE",
        "airport_count": 16
      },
      {
        "county": "SUTTER",
        "airport_count": 16
      },
      {
        "county": "KINGS",
        "airport_count": 15
      },
      {
        "county": "EL DORADO",
        "airport_count": 15
      },
      {
        "county": "MERCED",
        "airport_count": 15
      },
      {
        "county": "ALAMEDA",
        "airport_count": 14
      },
      {
        "county": "SAN LUIS OBISPO",
        "airport_count": 14
      },
      {
        "county": "SISKIYOU",
        "airport_count": 13
      },
      {
        "county": "MONTEREY",
        "airport_count": 13
      },
      {
        "county": "SONOMA",
        "airport_count": 12
      },
      {
        "county": "IMPERIAL",
        "airport_count": 12
      },
      {
        "county": "MADERA",
        "airport_count": 12
      },
      {
        "county": "SHASTA",
        "airport_count": 11
      },
      {
        "county": "MODOC",
        "airport_count": 11
      },
      {
        "county": "BUTTE",
        "airport_count": 11
      },
      {
        "county": "SANTA CLARA",
        "airport_count": 10
      },
      {
        "county": "STANISLAUS",
        "airport_count": 10
      },
      {
        "county": "NEVADA",
        "airport_count": 10
      },
      {
        "county": "CONTRA COSTA",
        "airport_count": 9
      },
      {
        "county": "TEHAMA",
        "airport_count": 9
      },
      {
        "county": "COLUSA",
        "airport_count": 9
      },
      {
        "county": "YOLO",
        "airport_count": 9
      },
      {
        "county": "INYO",
        "airport_count": 9
      },
      {
        "county": "HUMBOLDT",
        "airport_count": 9
      },
      {
        "county": "MENDOCINO",
        "airport_count": 8
      },
      {
        "county": "SOLANO",
        "airport_count": 8
      },
      {
        "county": "PLACER",
        "airport_count": 8
      },
      {
        "county": "LAKE",
        "airport_count": 8
      },
      {
        "county": "NAPA",
        "airport_count": 8
      },
      {
        "county": "PLUMAS",
        "airport_count": 8
      },
      {
        "county": "SANTA CRUZ",
        "airport_count": 8
      },
      {
        "county": "TUOLUMNE",
        "airport_count": 7
      },
      {
        "county": "TRINITY",
        "airport_count": 7
      },
      {
        "county": "AMADOR",
        "airport_count": 7
      },
      {
        "county": "MONO",
        "airport_count": 6
      },
      {
        "county": "LASSEN",
        "airport_count": 6
      },
      {
        "county": "MARIN",
        "airport_count": 6
      },
      {
        "county": "CALAVERAS",
        "airport_count": 5
      },
      {
        "county": "YUBA",
        "airport_count": 5
      },
      {
        "county": "SAN BENITO",
        "airport_count": 4
      },
      {
        "county": "SAN MATEO",
        "airport_count": 4
      },
      {
        "county": "GLENN",
        "airport_count": 4
      },
      {
        "county": "DEL NORTE",
        "airport_count": 3
      },
      {
        "county": "MARIPOSA",
        "airport_count": 3
      },
      {
        "county": "SAN FRANCISCO",
        "airport_count": 3
      },
      {
        "county": "SIERRA",
        "airport_count": 2
      },
      {
        "county": "ALPINE",
        "airport_count": 2
      },
      {
        "county": "ESMERALDA",
        "airport_count": 1
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 576,
    "by_county": [
      {
        "county": "SUFFOLK",
        "airport_count": 34
      },
      {
        "county": "ERIE",
        "airport_count": 26
      },
      {
        "county": "NIAGARA",
        "airport_count": 20
      },
      {
        "county": "DUTCHESS",
        "airport_count": 20
      },
      {
        "county": "ONONDAGA",
        "airport_count": 18
      },
      {
        "county": "ONEIDA",
        "airport_count": 18
      },
      {
        "county": "ULSTER",
        "airport_count": 17
      },
      {
        "county": "ORANGE",
        "airport_count": 16
      },
      {
        "county": "ONTARIO",
        "airport_count": 15
      },
      {
        "county": "NASSAU",
        "airport_count": 15
      },
      {
        "county": "CHAUTAUQUA",
        "airport_count": 14
      },
      {
        "county": "DELAWARE",
        "airport_count": 13
      },
      {
        "county": "ALBANY",
        "airport_count": 13
      },
      {
        "county": "MONROE",
        "airport_count": 13
      },
      {
        "county": "SARATOGA",
        "airport_count": 12
      },
      {
        "county": "OSWEGO",
        "airport_count": 12
      },
      {
        "county": "MONTGOMERY",
        "airport_count": 12
      },
      {
        "county": "CATTARAUGUS",
        "airport_count": 11
      },
      {
        "county": "QUEENS",
        "airport_count": 11
      },
      {
        "county": "WESTCHESTER",
        "airport_count": 11
      },
      {
        "county": "ESSEX",
        "airport_count": 10
      },
      {
        "county": "WAYNE",
        "airport_count": 10
      },
      {
        "county": "SULLIVAN",
        "airport_count": 10
      },
      {
        "county": "WYOMING",
        "airport_count": 9
      },
      {
        "county": "JEFFERSON",
        "airport_count": 9
      },
      {
        "county": "STEUBEN",
        "airport_count": 9
      },
      {
        "county": "SCHOHARIE",
        "airport_count": 9
      },
      {
        "county": "ORLEANS",
        "airport_count": 9
      },
      {
        "county": "CLINTON",
        "airport_count": 8
      },
      {
        "county": "COLUMBIA",
        "airport_count": 8
      },
      {
        "county": "FULTON",
        "airport_count": 8
      },
      {
        "county": "LIVINGSTON",
        "airport_count": 8
      },
      {
        "county": "CHEMUNG",
        "airport_count": 8
      },
      {
        "county": "ST LAWRENCE",
        "airport_count": 8
      },
      {
        "county": "GREENE",
        "airport_count": 8
      },
      {
        "county": "CAYUGA",
        "airport_count": 8
      },
      {
        "county": "TOMPKINS",
        "airport_count": 7
      },
      {
        "county": "RENSSELAER",
        "airport_count": 7
      },
      {
        "county": "BROOME",
        "airport_count": 7
      },
      {
        "county": "SCHUYLER",
        "airport_count": 7
      },
      {
        "county": "SCHENECTADY",
        "airport_count": 7
      },
      {
        "county": "HERKIMER",
        "airport_count": 7
      },
      {
        "county": "HAMILTON",
        "airport_count": 7
      },
      {
        "county": "NEW YORK",
        "airport_count": 7
      },
      {
        "county": "MADISON",
        "airport_count": 7
      },
      {
        "county": "OTSEGO",
        "airport_count": 6
      },
      {
        "county": "GENESEE",
        "airport_count": 6
      },
      {
        "county": "CHENANGO",
        "airport_count": 5
      },
      {
        "county": "WASHINGTON",
        "airport_count": 4
      },
      {
        "county": "ALLEGANY",
        "airport_count": 4
      },
      {
        "county": "SENECA",
        "airport_count": 4
      },
      {
        "county": "YATES",
        "airport_count": 4
      },
      {
        "county": "WARREN",
        "airport_count": 4
      },
      {
        "county": "TIOGA",
        "airport_count": 4
      },
      {
        "county": "ROCKLAND",
        "airport_count": 4
      },
      {
        "county": "CORTLAND",
        "airport_count": 2
      },
      {
        "county": "FRANKLIN",
        "airport_count": 2
      },
      {
        "county": "LEWIS",
        "airport_count": 1
      },
      {
        "county": "PUTNAM",
        "airport_count": 1
      },
      {
        "county": "BRONX",
        "airport_count": 1
      },
      {
        "county": "KINGS",
        "airport_count": 1
      }
    ]
  }
]
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."county"
      END as "county__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
  WHERE (airports."state"='CA')or(airports."state"='NY')
  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"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_county"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Next, we'll use the output of that query as the input to another, where we determine which counties have the highest percentage of airports compared to the whole state, taking advantage of the nested structure of the data to to so.

document
run: airports -> {
  where: state = 'CA' | 'NY'
  group_by: state
  aggregate: airport_count
  nest: by_county is  {
    group_by: county
    aggregate: airport_count
  }
} -> {
  limit: 10; order_by: 4 desc
  select:
    by_county.county
    airports_in_county is by_county.airport_count
    airports_in_state is airport_count
    # percent
    percent_in_county is by_county.airport_count / airport_count
}
QUERY RESULTS
countyairports_​in_​countyairports_​in_​statepercent_​in_​county
LOS ANGELES17698417.89%
SAN BERNARDINO719847.22%
SUFFOLK345765.9%
ORANGE539845.39%
KERN499844.98%
[
  {
    "county": "LOS ANGELES",
    "airports_in_county": 176,
    "airports_in_state": 984,
    "percent_in_county": 0.17886178861788618
  },
  {
    "county": "SAN BERNARDINO",
    "airports_in_county": 71,
    "airports_in_state": 984,
    "percent_in_county": 0.07215447154471545
  },
  {
    "county": "SUFFOLK",
    "airports_in_county": 34,
    "airports_in_state": 576,
    "percent_in_county": 0.059027777777777776
  },
  {
    "county": "ORANGE",
    "airports_in_county": 53,
    "airports_in_state": 984,
    "percent_in_county": 0.05386178861788618
  },
  {
    "county": "KERN",
    "airports_in_county": 49,
    "airports_in_state": 984,
    "percent_in_county": 0.049796747967479675
  }
]
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."county"
      END as "county__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
  WHERE (airports."state"='CA')or(airports."state"='NY')
  GROUP BY 1,2,4
)
, __stage1 AS (
  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"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_county"
  FROM __stage0
  GROUP BY 1
)
SELECT 
   by_county_0."county" as "county",
   by_county_0."airport_count" as "airports_in_county",
   base."airport_count" as "airports_in_state",
   by_county_0."airport_count"*1.0/base."airport_count" as "percent_in_county"
FROM __stage1 as base
LEFT JOIN LATERAL (SELECT UNNEST(base."by_county"), 1 as ignoreme) as by_county_0_outer(by_county_0,ignoreme) ON by_county_0_outer.ignoreme=1
ORDER BY 4 desc NULLS LAST
LIMIT 10

Aggregate Locality

When computing sum, avg, and count on fields in joined sources with one-to-many relationships, Malloy will automatically handle the duplication of rows that occurs in the join, and compute accurate aggregations on the fanned-out table. See the Aggregate Locality section for more information.

document
run: aircraft -> {
  aggregate:
    // The average number of seats on models of registered aircraft
    models_avg_seats is aircraft_models.seats.avg()
    // The average number of seats on registered aircraft
    aircraft_avg_seats is avg(aircraft_models.seats)
}
QUERY RESULTS
models_​avg_​seatsaircraft_​avg_​seats
12.9198.672
[
  {
    "models_avg_seats": 12.919491525423728,
    "aircraft_avg_seats": 8.671575437621561
  }
]
SELECT 
   (
        SELECT AVG(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:aircraft_models_0."aircraft_model_code", val: aircraft_models_0."seats"})) a
        )
      ) as "models_avg_seats",
   AVG(aircraft_models_0."seats") as "aircraft_avg_seats"
FROM '../data/aircraft.parquet' as aircraft
 LEFT JOIN '../data/aircraft_models.parquet' AS aircraft_models_0
  ON aircraft."aircraft_model_code"=aircraft_models_0."aircraft_model_code"

Comments

Malloy code can include both line and block comments. Line comments, which begin with -- or //, may appear anywhere within a line, and cause all subsequent characters on that line to be ignored. Block comments, which are enclosed between /* and */, cause all enclosed characters to be ignored and may span multiple lines.

--  The total number of flight entries
run: flights -> {
  aggregate: flight_count // Defined simply as `count()`
}

/*
 * A comparison of the total number of flights
 * for each of the tracked carriers.
 */
run: flights -> {
  group_by: carrier
  aggregate: flight_count /* , total_distance */
}

Ordering and Limiting

In Malloy, ordering and limiting work pretty much the same way they do in SQL, though Malloy introduces some reasonable defaults.

The limit: statement limits the number of rows returned. Results below are sorted by the first measure descending—in this case, airport_count.

document
run: duckdb.table('../data/airports.parquet') -> {
  limit: 2
  group_by: state
  aggregate: airport_count is count()
}
QUERY RESULTS
stateairport_​count
TX1,845
CA984
[
  {
    "state": "TX",
    "airport_count": 1845
  },
  {
    "state": "CA",
    "airport_count": 984
  }
]
SELECT 
   base."state" as "state",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 2

Default ordering can be overridden with order_by:, as in the following query, which shows the states in alphabetical order. order_by: can take a field index number or the name of a field.

document
run: duckdb.table('../data/airports.parquet') -> {
  order_by: state
  group_by: state
  aggregate: airport_count is count()
}
QUERY RESULTS
stateairport_​count
AK608
AL260
AR299
AS4
AZ319
[
  {
    "state": "AK",
    "airport_count": 608
  },
  {
    "state": "AL",
    "airport_count": 260
  },
  {
    "state": "AR",
    "airport_count": 299
  },
  {
    "state": "AS",
    "airport_count": 4
  },
  {
    "state": "AZ",
    "airport_count": 319
  }
]
SELECT 
   base."state" as "state",
   COUNT( 1) as "airport_count"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 1 ASC NULLS LAST

Next Steps

This was a whirlwind tour of the syntax and features of Malloy. To continue on your Malloy journey: