Query syntax

The grammar of q on the command line (:, then q:): a subset of the q language, evaluated right to left. Query data walks through it. Every example below runs on the public dataset its block names; open that dataset from Example datasets on the home screen.

q and kdb+ are trademarks of KX Systems. datui is not affiliated with or endorsed by KX.

Structure of a query

Each part in brackets is optional; replace it with a list of expressions:

select [columns] [by group_columns] [where conditions]

Use clauses in the order shown, at most once each. Misplaced or repeated clauses and extra tokens after an expression are errors.

The : assignment (aliasing)

name : expression names an expression. The left side is the new column or group name, an identifier (total) or col["name with spaces"]; the right side is any expression (column reference, literal, arithmetic, function call).

select carrier, flight, gain: dep_delay - arr_delay
select route: dest, flight
select flights: count flight by airline: carrier, long: distance > 1000

Assignment works in both select and by. In by it defines computed group keys or renames.

Columns with spaces in their names

Identifiers cannot contain spaces. For columns (or aliases) with spaces, use col["..."] with a quoted string, or col[identifier] for a name without spaces. The same syntax works in select, by and where.

select col["Team 1"], col["Team 2"], FT
select home: col["Team 1"]

A column named like a function (count, log, var) is read as the function when something follows it, so select log + 1 is log(+1). Write col["log"] + 1.

Right-to-left expression parsing

There is no operator precedence. Expressions are parsed right-to-left: the leftmost binary operator is the root, and everything to its right is parsed first as a unit.

• a + b * c → a + (b * c)
• a * b + c → a * (b + c), not (a * b) + c
• (a + b) * 2 > 100 → (a + b) * (2 > 100); write the comparison first, 100 < (a + b) * 2

Put the operation you want done first on the right, or use () to override grouping:

select gain: (dep_delay - arr_delay) * 60
select carrier, flight where (dep_delay > 60) | (arr_delay > 60)

Parentheses also matter for , and | in where: splitting on comma and pipe respects nesting, so you can wrap ORs in () and combine them with commas. See Where clause.

Select clause

• select — all columns, no expressions
• select a, b, c — those columns or expressions, in order, separated by ,
• select a, b: x + y, c — columns and aliased expressions
• select distinct carrier, origin — only the distinct rows of the result; select distinct alone drops duplicate rows. A column named distinct is col["distinct"], or plain distinct before , : . or an operator

By clause (grouping and aggregation)

• by origin, dest — group by those columns; non-group columns become list columns, and the UI supports drill-down
• by carrier, long: distance > 1000 — group by a column and a computed expression
• select avg dep_delay, min dep_delay by carrier — aggregations per group; Enter on a row drills down to the rows behind it

By uses the same comma-separated list and name : expression rules as select. Aggregation functions (avg, min, max, count, sum, std, med, nunique, var, dev) can be written fn[expr] or fn expr; brackets are optional. wavg goes between its operands: w wavg x.

An unaliased aggregate of a single column is named {fn}_{column}, so select avg dep_delay, max dep_delay by carrier yields avg_dep_delay and max_dep_delay; an explicit alias (total: sum[distance]) overrides it.

Where clause: , and |

The where clause combines conditions with two separators:

• , — AND. Each comma-separated segment is one ANDed condition.
• | — OR. Within one segment, | separates alternatives that are ORed.

The where part is split on , first (respecting () and []), then each segment on |, so , has broader scope than |:

The where clause takes conditions only: no name: expression assignment.

For more complex logic, wrap OR subexpressions in () — parentheses keep | inside one AND term — and separate the groups with ,.

Operators and literals

A timestamp literal compared with a column that has a time zone is read as a clock time in that zone. A clock time repeated when clocks fall back means its first instant.

Quoted text is a string, never a date: where d = "2024.01.01" on a date column is an error that names the literal to write, here 2024.01.01. Time and duration columns have no literal; compare t.hour, t.minute or t.second with a number.

Either side of a comparison can be a column, a literal or an expression: where a = 10, where created_at.date > other_date_col.

Word operators

q’s infix words, parsed right-to-left like every other operator.

Because evaluation is right-to-left, x mod 2 in [1] is x mod (2 in [1]). Write (x mod 2) in [1] or 1 = x mod 2. not name in ["Mary"] negates the whole test.

The words are operators only between two operands. A column named in or mod still works on its own or at the start of an expression, and col["in"] always does.

Date and datetime accessors

For columns of type Date or Datetime (with or without timezone), dot notation extracts components: column_ref.accessor.

String accessors

Apply to String columns:

part, slice, replace, strip, to_date and to_datetime also work on number and date columns, read as their text: NOAA’s DATE parses whether it was read as 20240101 text or as an integer. A value that does not parse becomes null.

Number and conversion accessors

Accessors chain left to right: FT.part["–", 0].int. Arguments are literals, quoted text or numbers, and a wrong number of them is an error naming the accessor. To apply an accessor to an aggregate or an expression, wrap it in parentheses: (avg dep_delay).round[1].

An accessor result is automatically aliased to {column}_{accessor}, so timestamp.date becomes timestamp_date.

Examples

time_hour in NYC flights is a UTC datetime:

select day: time_hour.date
select time_hour.date, time_hour.year
select flight, time_hour.time
select time_hour, time_hour.month, time_hour.dow by time_hour.year
select delay: arr_delay^dep_delay
select tailnum.len, tailnum.upper, time_hour.format["%Y-%m"]
select where time_hour.date > 2013.06.30
select where time_hour.month = 12, time_hour.dow = 1
select where dest.ends_with["A"]
select where null dep_time
select where not null dep_time

tpep_pickup_datetime in NYC yellow taxis is a datetime with no time zone:

select where tpep_pickup_datetime > 2025.01.15T14:30:00.123456

Functions

Functions are used for aggregation (typically in select with by) and for logic in where. Write fn[expr] or fn expr; brackets are optional.

Aggregation functions

Logic functions

Scalar functions

var, dev and std divide by n − 1, where q’s var and dev divide by n.

Examples on the built-in datasets

NYC yellow taxis:

select trips: count VendorID by tpep_pickup_datetime.hour
select trips: count fare_amount by b: 5 xbar fare_amount where fare_amount > 0, fare_amount < 100

Premier League:

select home: FT.part["–", 0].int, away: FT.part["–", 1].int
select d: Date.replace["(P)", ""].strip.to_date["%a %b %d %Y"]
select matches: count Round by m: Date.replace["(P)", ""].strip.to_date["%a %b %d %Y"].month

US baby names:

select total: sum n by name where name in ["Emma", "Jennifer", "Olivia"]
select total: sum n by decade: 10 xbar year where name = "Jennifer"
select year, log_n: (log n).round[2] where name = "Emma"

NYC flights:

select mean_delay: (avg dep_delay).round[1] by hour
select distinct carrier, origin
select planes: nunique tailnum by carrier
select delay: distance wavg arr_delay by carrier
select dep_time, minute: dep_time mod 100
select sd: sqrt var dep_delay by origin

Food nutrition:

select items: count item by restaurant where item like "*Chicken*"
select restaurant, item where item like "*Chicken*"

Palmer penguins:

select mean_mass_g: avg body_mass_g by species
select species, island, bill_ratio: (bill_length_mm % bill_depth_mm).round[2] where not null bill_length_mm