| DATUI-QUERY(7) | Miscellaneous | DATUI-QUERY(7) |
datui-query - the q syntax of the datui command line
select [columns] [by groups] [where conditions]
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.
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.
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).
On the flights dataset (see DATASETS):
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.
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.
On the football dataset (see DATASETS):
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.
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.
Put the operation you want done first on the right, or use () to override grouping:
On the flights dataset (see DATASETS):
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.
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.
The where clause combines conditions with two separators:
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 ,.
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.
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.
For columns of type Date or Datetime (with or without timezone), dot notation extracts components: column_ref.accessor.
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.
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.
time_hour in NYC flights is a UTC datetime:
On the flights dataset (see DATASETS):
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:
On the taxis dataset (see DATASETS):
select where tpep_pickup_datetime > 2025.01.15T14:30:00.123456
Functions are used for aggregation (typically in select with by) and for logic in where. Write fn[expr] or fn expr; brackets are optional.
var, dev and std divide by n − 1, where q's var and dev divide by n.
NYC yellow taxis:
On the taxis dataset (see DATASETS):
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:
On the football dataset (see DATASETS):
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:
On the names dataset (see DATASETS):
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:
On the flights dataset (see DATASETS):
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:
On the food dataset (see DATASETS):
select items: count item by restaurant where item like "*Chicken*" select restaurant, item where item like "*Chicken*"
Palmer penguins:
On the penguins dataset (see DATASETS):
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
The examples run on the Example datasets that come with datui (on the home screen). Open one, press /, and type the query.
datui https://vincentarelbundock.github.io/Rdatasets/csv/nycflights13/flights.csv
datui https://raw.githubusercontent.com/footballcsv/england/de3945297668d7114006a8ca1c4c3740010b111c/2020s/2020-21/eng.1.csv
datui https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet
datui https://raw.githubusercontent.com/rfordatascience/tidytuesday/8bfa9d9a7279192cb41cab041f426f2aacefde91/data/2022/2022-03-22/babynames.csv
datui https://vincentarelbundock.github.io/Rdatasets/csv/openintro/fastfood.csv
datui https://vincentarelbundock.github.io/Rdatasets/csv/palmerpenguins/penguins.csv
datui(1)
The datui documentation: <https://derekwisong.github.io/datui/>
Report bugs at <https://github.com/derekwisong/datui/issues>.
Derek Wisong and the datui contributors.
Copyright © 2026 Derek Wisong
datui is free software under the MIT License.
| 2026-10-06 | datui 0.4.1 |