Query Data in KDB-X DB Service

This page provides an overview of how to query data in KDB-X DB Service.

KDB-X DB Service provides 3 query interfaces for retrieving data.

Structured query — parameterized API for querying timeseries.
q query — free-form q/qsql, which can include an optional free-form aggregation function.
SQL query — SELECT-only SQL interface.

A detailed reference, including examples, for all APIs is available in the API spec. Sample notebooks demonstrating API usage are bundled with the DB Service repo on GitHub.

Query results

All queries return a header and a payload.

The header contains execution metadata, including correlation IDs, timestamps, the API used, and whether limits were applied. This information can be used for debugging, tracing requests, and understanding how the query was processed.
Errors are reported in the ai field in the header.
The payload contains the result rows, returned as JSON. The q and Python clients return them as tables by default, but this can be overridden to JSON (or a Pandas dataframe in the Python client).

{
  "header": {
    "corr": "740fb465-208b-4be1-acc9-3424136cdcd5",
    "logCorr": "740fb465-208b-4be1-acc9-3424136cdcd5",
    "version": 1,
    "rcvTS": "2026-04-27T09:57:18.238000000",
    "http": "json",
    "api": ".query.q",
    "agg": ":172.20.0.3:5060",
    "refVintage": -9223372036854775807,
    "rc": 0,
    "ac": 0,
    "ai": ""
  },
  "payload": [
    {
      "trddate": "2026-01-21",
      "sym": "EURUSD",
      "o": 901.2,
      "h": 901.2,
      "l": 901.2,
      "c": 901.2
    },
    ...
  ]
}

Structured query

The Structured query endpoint is POST /api/v0/query/simple.

Structured query uses explicit parameters instead of a query string.

table is required for all queries.
startTS and endTS timestamps are required for all timeseries requests; they are used to route requests to the appropriate DB tier (RDB, IDB, IDB) and locate data on disk in the correct partition. These values work on the prtnCol defined for the table.

Filtering

The filter parameter takes a list of filter triples in the form [operator, column, values]. It is possible to nest filters with the logical operators not, and, or. For example, ["and", ["=", "size", 100], ["<", "price", 500]].

Operator	Notes
`=`, `<>`, `<`, `>`, `<=`, `>=`	Standard comparison
`in`	Expects a list of values
`within`	Expects a range as a tuple
`like`	Expects a symbol or string; follows KDB-X `like` rules

Aggregation

The agg parameter has three different behaviors, depending on what type of list it's given:

Input type	Behavior	Example
List of column names	Specifies what columns to include in the result	`["sym", "bid", "ask"]`
List of tuples	Specifies what columns to include in the result, and renames them	`[["Symbol", "sym"], ["BidPrice", "bid"], ["AskPrice", "ask"]]`
List of triples	Applies aggregation function to column and renames it	`[["o", "first", "bid"], ["h", "max", "bid"], ["l", "min", "bid"], ["c", "last", "bid"]]`

Available aggregation functions: all, any, avg, cor, count, cov, dev, distinct (always returns a list), first, last, max, min, prd, scov, sdev, sum, svar, var, wavg, wsum.

Use groupBy to group aggregated results by another column.

Sorting

The sortCols parameter specifies a list of sorting criteria for the result. Sorting is ascending by default.

Input type	Behavior	Example
List of column name strings	Sort ascending by each column	`["sym", "ts"]`
List of tuples `[col, direction]`	Sort each column in the specified direction	`[["sym", "asc"], ["ts", "desc"]]`
List of strings with `"desc"` appended	Sort all columns descending	`["sym", "ts", "desc"]`

Other features

Temporality

The temporality parameter controls how the time range is applied.

Value	Behavior
`snapshot` (default)	If temporality is not specified, returns a continuous block of data between `startTS` and `endTS`
`slice`	Returns data between the time of `startTS` and time of `endTS` for every day in the range of `startTS` to `endTS`. For example, given a `startTS` of `2021.01.01D10:00:00` and an `endTS` of `2021.01.03D13:00:00`, data would be returned from `10:00:00` and `13:00:00` on each of the dates `2021.01.01`, `2021.01.02`, `2021.01.03`.

Fill

The fill parameter replaces null values in the result.

Value	Behavior
`forward`	Replaces nulls with the previous non-null value
`zero`	Replaces nulls with the zero-equivalent for the column type

Limit

The limit parameter restricts the number of rows returned.

Value	Behavior
Positive integer	Limits rows forward from the start of the result
Negative integer	Limits rows backward from the end of the results
Tuple of integers	Limits results as above, with the first integer acting as an offset and the second specifying the limit.

Timezone handling

All timestamps in KDB-X DB Service data are treated as UTC. Structured queries support explicit timezone control via three parameters:

Parameter	Description
`inputTZ`	Timezone to interpret `startTS` and `endTS` as. Defaults to UTC.
`outputTZ`	Timezone to convert `outputTZCols` to. Defaults to UTC (no conversion).
`outputTZCols`	List of columns to the output timezone `outputTZ`.

Timezones should be supplied in tz database format; for example America/New_York or Europe/London.

Example structured query

qPythonREST

session.querySimple[([
    table:`fxquote;
    startTS:2026.03.02D00:00:00;
    endTS:2026.03.04D00:00:00;
    filter:enlist[("in";"sym";(`EURUSD;`USDJPY;`GBPUSD))];
    groupBy:(`trddate;`sym);
    agg:((`o;`first;`bid);
        (`h;`max;`bid);
        (`l;`min;`bid);
        (`c;`last;`bid))])]

session.query_simple(table='fxquote',
    startTS='2026.03.02D00:00:00',
    endTS='2026.03.04D00:00:00',
    filter=[['in','sym',['EURUSD','USDJPY','GBPUSD']]],
    groupBy=['trddate','sym'],
    agg=[['o', 'first', 'bid'],
        ['h', 'max', 'bid'],
        ['l', 'min', 'bid'],
        ['c', 'last', 'bid']],
    return_as="pandas")

curl -X POST "http://localhost:8080/api/v1/query/simple" \
    -H "Content-Type: application/json" \
    -d '{"table":"fxquote",
        "startTS":"2026.03.02D00:00:00",
        "endTS":"2026.03.04D00:00:00",
        "filter":[["in","sym",["EURUSD","USDJPY","GBPUSD"]]],
        "groupBy":["trddate","sym"],
        "agg":[["o", "first", "bid"],
                ["h", "max", "bid"],
                ["l", "min", "bid"],
                ["c", "last", "bid"]]}'

q query

The q query endpoint is POST /api/v0/query/q. It has a single required parameter query, freeform q to run on each DB tier. It can be used for more complex queries than the Structured or SQL endpoints.

Aggregation

By default, the gateway will concatenate the results from each tier (RDB, IDB, HDB) by performing a raze. The optional parameter agg specifies a q lambda to be used instead, and allows for more sophisticated aggregation and data shaping of the final result.

It can be specified as either:

A unary function, for example "distinct".
A composition of named unary functions, for example "'[distinct;raze]".
A unary lambda, for example "{distinct raze x}".

The data passed to the function is a list query results, each element being the query results from each tier.

`.kxi.selectTable`

The DB Service stores data across multiple internal tables and uses additional metadata columns to handle late data and query availability during end-of-interval and end-of-day processing. The helper function .kxi.selectTable ensures that q queries return all** of the data, rather than only what is stored in a specific tier (for example, the IDB), and also hides internal metadata columns.

Except in cases where only historical HDB data is required and late data is not a concern, q queries should use .kxi.selectTable. Otherwise, the following issues may occur:

Missing late data.
Missed reference data updates between EOIs.
Schema mismatches on aggregation of wildcard select, as metadata columns differ between RDB, IDB, HDB.
Lack of forward compatibility of your queries in future versions, a metadata schemas change.

Structured and SQL queries are not affected by these issues and can be used safely.

.kxi.selectTable takes a single argument, a dictionary with he following parameters:

Field	Type	Required	Details	Default
`table`	symbol	Yes	Table name.
`startTS`	timestamp		Start time (inclusive) for selecting data. This can be an empty list for reference tables.	`-0Wp`
`endTS`	timestamp		End time (exclusive) for selecting data. This can be an empty list for reference tables.	`0Wp`
`filter`	list		Functional where clause, for example, a list of the parse tree.	`()`, indicating no filter is applied.
`groupBy`	dict/boolean		Functional by clause.	`0b`, indicating no by clause.
`agg`	dict		Functional aggregation/select clause.	All columns of the table are included.
`mmap`	boolean		Boolean that indicates whether the result of a `.kxi.selectTable` is mapped instead of loaded to memory.	`0b`, indicating that this feature is disabled.
`limit`	int	int[2]		Value that limits the result row number. (Ordering not guaranteed).

The correct forms for filter, groupBy, and agg are different than the structured query. Using parse on a direct select query can be a useful guide.

Example q query

qPythonREST

// Using .kxi.selectTable - safest
session.queryQ["
.kxi.selectTable[([
    table:`fxquote;
    startTS:2026.03.02D00:00:00;
    endTS:2026.03.04D00:00:00;
    filter:((in;`sym;enlist `EURUSD`USDJPY`GBPUSD);(in;`trddate;2026.03.02 2026.03.03));
    groupBy:`trddate`sym!`trddate`sym;
    agg:`o`h`l`c!((*:;`bid);(max;`bid);(min;`bid);(last;`bid))])]"]

// Selecting data directly - potentially unsafe
session.queryQ["
select o: first bid,
        h: max bid,
        l: min bid,
        c: last bid 
by trddate, sym 
from fxquote 
where sym in `EURUSD`USDJPY`GBPUSD,
    trddate in 2026.03.02 2026.03.03"]

# Using .kxi.selectTable - safest
session.query_q(query="""
.kxi.selectTable[([
    table:`fxquote;
    startTS:2026.03.02D00:00:00;
    endTS:2026.03.04D00:00:00;
    filter:((in;`sym;enlist `EURUSD`USDJPY`GBPUSD);(in;`trddate;2026.03.02 2026.03.03));
    groupBy:`trddate`sym!`trddate`sym;
    agg:`o`h`l`c!((*:;`bid);(max;`bid);(min;`bid);(last;`bid))])]
""", return_as="pandas")

# Selecting data directly - potentially unsafe
session.query_q(query="""
select o: first bid,
        h: max bid,
        l: min bid,
        c: last bid 
by trddate, sym 
from fxquote 
where sym in `EURUSD`USDJPY`GBPUSD,
    trddate in 2026.03.02 2026.03.03
""", return_as="pandas")

# Using .kxi.selectTable - safest
QUERY='
.kxi.selectTable[([
    table:`fxquote;
    startTS:2026.03.02D00:00:00;
    endTS:2026.03.04D00:00:00;
    filter:((in;`sym;enlist `EURUSD`USDJPY`GBPUSD);(in;`trddate;2026.03.02 2026.03.03));
    groupBy:`trddate`sym!`trddate`sym;
    agg:`o`h`l`c!((*:;`bid);(max;`bid);(min;`bid);(last;`bid))])]
'
curl -s -X POST "http://localhost:8080/api/v1/query/q" \
    -H "Content-Type: application/json" \
    -d "{\"query\":\"$QUERY\"}"

# Selecting data directly - potentially unsafe
QUERY='
select o: first bid,
        h: max bid,
        l: min bid,
        c: last bid 
by trddate, sym 
from fxquote 
where sym in `EURUSD`USDJPY`GBPUSD,
    trddate in 2026.03.02 2026.03.03
'
curl -s -X POST "http://localhost:8080/api/v1/query/q" \
    -H "Content-Type: application/json" \
    -d "{\"query\":\"$QUERY\"}"

SQL query

The SQL query endpoint is POST /api/v0/query/sql. The KDB-X DB Service runs SQL queries across all data tiers, and supports a subset of the KDB-X SQL dialect. Queries are limited to SELECT, following the following structure:

SELECT [DISTINCT] ... FROM TABLE [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ...] [OFFSET ...]

Limitations:

INSERT, CREATE, and DELETE are not supported by the API.
Chained boolean operations are not supported, logic must be wrapped in parenthesis. For example: SELECT sym, (sector='tech') or ((exchangeID in (1,2)) or (sym='FNLH.CAN')) FROM instrument.
Positional grouping is not supported, for example SELECT avg(price) FROM trade GROUP BY 1.
Non-aggregate operations on the GROUP BY columns are not supported.
Irreversable operations are not permitted in WHERE clauses when restricting on the partition column (date). For example: SELECT * FROM myTable WHERE date % 2 = 0.

Joins

The SQL interface supports INNNER, LEFT, RIGHT, FULL, and CROSS joins. Note the following constraints:

Constraint	Detail
Aliases	Required when using joins
Join condition	Only `=` is supported for joining columns
Syntax	Must use the `ON` keyword
Column types	Left-hand and right-hand join columns must be the same kdb+ datatype

Set operations

UNION, INTERSECT and EXCEPT operators are supported to combine the results of two queries. Note the following constraints:

Constraint	Detail
Compatibility	Both `SELECT` statements must return the same number of columns with the same data types
Result ordering	The order of data returned from a set operation query is not guaranteed unless the `ORDER BY` clause is included in the query.
Supported clauses on result	Currently only `LIMIT` and `ORDER BY` are supported on the result set of a `UNION`, `EXCEPT`, `INTERSECT` query.

Supported functions

Aggregation

count, first, last, min, max, sum, avg, prod, wsum, wavg, var, svar, dev, stdev

Comparison operators

Operator	Notes
`=`	Exact match on strings. Use `like` for pattern matching.
`!=`, `>`, `<`, `>=`, `<=`	Standard comparisons. Note: `>` does not work on alphabetical lists.
`!<`, `!>`	Not less than / not greater than
`in`	Match against a list of values
`between`	Range comparison (inclusive)
`like`	Pattern matching using KDB-X `like` rules — does not support full regex
`is null`, `is not null`	Null checks

Mathematical functions

Function	Notes
`abs`, `neg`, `ceil` / `ceiling`, `floor`, `round`, `trunc`	Rounding and sign
`trunc(a, b)`	Truncates `a` to `b` decimal places
`sqrt`, `cbrt`	Square and cube root. Does not support negative numbers.
`exp`, `ln`, `log`, `log10`, `power`	Exponential and logarithmic
`sin`, `cos`, `tan`, `asin`, `acos`, `atan`, `atan2`	Trigonometric (radians)
`sind`, `cosd`, `tand`, `asind`, `acosd`, `atand`, `atan2d`	Trigonometric (degrees)
`sinh`, `cosh`, `tanh`, `asinh`, `acosh`, `atanh`	Hyperbolic
`degrees`, `radians`, `pi`	Angle conversion and constant
`div`, `gcd`, `lcm`, `factorial`	Integer arithmetic
`width_bucket(a, b)`	Returns the bucket that value `a` belongs to in list `b`
`width_bucket(a, b, c, d)`	Returns the bucket for `a` between min `b` and max `c`, with `d` total buckets

Mathematical operators

Operator	Meaning
`+`, `-`, `*`, `/`	Arithmetic
`%`	Modulo
`^`	Exponent
`@`	Absolute value
`\\|/`	Square root
`\\|\\|/`	Cube root
`&`, `\\|`, `~`, `#`	Bitwise AND, OR, NOT, XOR
`<<`, `>>`	Bitwise left shift, right shift

Value functions

current_date, current_timestamp, current_time

Miscellaneous functions

Function	Notes
`cast`	Casting to `binary`, `varbinary`, and `image` is not supported
`date_trunc`	Calling `date_trunc("millennium", time)` on dates before `2000.01.01` returns `-0W` (negative infinity), as KDB-X cannot represent year `1001.01.01`
`date_part`, `extract`	Date component extraction
`coalesce`, `nullif`, `case`	Conditional / null handling
`concat`, `cat`, `upper`, `lower`, `trim`, `ltrim`, `rtrim`, `btrim`	String manipulation
`left`, `right`, `length`, `char_length`, `character_length`, `bit_length`, `octet_length`	String length and slicing
`position`, `starts_with`, `reverse`	String search
`now`, `random`, `setseed`, `version`	Utility functions

Example SQL query

Calculating OHLC bars using the different methods

qPythonREST

query:"SELECT "
,"    trddate,"
,"    sym,"
,"    FIRST(bid) AS o, "
,"    MAX(bid) AS h, "
,"    MIN(bid) AS l, "
,"    LAST(bid) AS c "
,"FROM fxquote "
,"WHERE "
,"    trddate in ('2026-03-02', '2026-03-03') AND"
,"    sym in ('EURUSD','USDJPY','GBPUSD')"
,"GROUP BY trddate, sym;"
session.querySession[query]

query = """
SELECT 
    trddate,
    sym,
    FIRST(bid) AS o, 
    MAX(bid) AS h, 
    MIN(bid) AS l, 
    LAST(bid) AS c 
FROM fxquote 
WHERE 
    trddate in ('2026-03-02', '2026-03-03') AND
    sym in ('EURUSD','USDJPY','GBPUSD')
GROUP BY trddate, sym;
"""
session.query_sql(query, return_as="pandas")

QUERY="
SELECT 
    trddate,
    sym,
    FIRST(bid) AS o, 
    MAX(bid) AS h, 
    MIN(bid) AS l, 
    LAST(bid) AS c 
FROM fxquote 
WHERE 
    trddate in ('2026-03-02', '2026-03-03') AND
    sym in ('EURUSD','USDJPY','GBPUSD')
GROUP BY trddate, sym;
"
curl -s -X POST "http://localhost:8080/api/v1/query/sql" \
-H "Content-Type: application/json" \
-d "{\"query\":\"$QUERY\"}"

Next steps

Refer to the DB Service repo for further details and examples.
See the REST APIs for reference.
Learn more about import and managing tables.