Skip to content

Query Data

This section covers how to perform queries against tables in the KDB.AI database.

Selecting the table to query

Each table in KDB.AI has an associated name. To perform a query, specify the name of the table in which the relevant data is stored. Using Python client you can create a table object from the session. The REST client is more direct with the table name supplied as a field in the JSON payload.

documents = session.table("documents")


Obtain the data from the entire table using the following command:

curl -s -H "Content-Type: application/json" localhost:8082/data -d '{"table":"documents"}'


To select a subset of the data, you can apply filters as documented here. These serve as where clauses.

    results = table.query(filter=[("within","createdDate",["2020.07.10D15:00:00.0000", "2021.07.10D15:00:00.0000"]),("<=", "length", 100)])
    print(f'Results: {results}')
curl -s -H "Content-Type: application/json" localhost:8082/data \
-d '{"table":"documents","filter":[["within","createdDate",["2020.07.10D15:00:00.0000", "2021.07.10D15:00:00.0000"]],["<=", "length", 100]]}'

Processing results

You can return a subset of the columns in the table, reducing the amount of data sent back to the client.

curl -s -H "Content-Type: application/json" localhost:8082/data \
-d '{"table":"documents",agg=["author","content"]}'

In addition to returning a subset of the columns, you can return aggregated results, group by categorical variables, and sort based on a column name.

    table.query(aggs=[('sumLength','sum','length')], group_by=['author'], sort_by=['sumLength'])
curl -s -H "Content-Type: application/json" localhost:8082/data \
-d '{"table":"documents", 

Supported aggregations

The table below lists all supported aggregation functions.

Function Description
all Returns the logical 'and' of all values in a set.
any Returns the logical 'or' of all values in a set.
avg Calculates the mean value across the set of matching records.
count Returns the number of records in the current selection.
dev Calculates the standard deviation of a column.
distinct Returns the distinct values from a column.
first Returns the first occurrence of a value. This is useful when performing a group by aggregation.
last Returns the last occurrence of a value. This is useful when performing a group by aggregation.
max Takes the maximum value of a set of records.
min Takes the minimum value of a set of records.
prd Calculates the product of matching records.
sdev Calculates the sample deviation of matching records.
scov Calculates the sample covariance between matching records.
sum Calculates the sum of matching records.
svar Calculates the sample variance of matching records.
var Calculates the variance of matching records.