Via Kondado: KSQL Reference

KSQL is Kondado's query language. It uses a JSON syntax that lets you filter, group, sort, and transform your data in a simple and secure way.

ℹ️ Beta: KSQL queries are available in beta mode.

Basic structure

A KSQL query is a JSON object with the following fields:

Field	Required	Description
`dest_collection_id`	Yes	Your destination collection identifier
`table_name`	Yes	Name of the table to query
`columns`	Yes	List of columns to return
`column_aggregations`	No	Aggregation type per column (e.g., `"sum"`, `"avg"`, `"none"`)
`dimensions`	No	Grouping columns (equivalent to GROUP BY)
`where_conditions`	No	Filter conditions
`order_by`	No	Result sorting
`limit`	No	Maximum number of records (max: 1,000)
`customColumns`	No	Calculated columns with custom expressions
`column_transformations`	No	Transformations applied to columns
`joins`	No	Join with other tables
`union`	No	Array of queries to combine with UNION ALL (replaces other fields)

Minimal example

{
  "dest_collection_id": 12345,
  "table_name": "sales",
  "columns": ["product", "amount", "date"]
}

Filters (where_conditions)

Filters are defined as a list of conditions. All conditions are combined with AND.

"where_conditions": [
  {"column": "status", "operator": "=", "value": "active"},
  {"column": "amount", "operator": ">", "value": 100}
]

Comparison operators

Operator	Description	Example value
`=`	Equal to	`"active"`
`!=`	Not equal to	`"cancelled"`
`>`	Greater than	`100`
`<`	Less than	`50`
`>=`	Greater than or equal	`100`
`<=`	Less than or equal	`200`

Set operators

Operator	Description	Example value
`IN`	Is in the list	`["US", "UK", "CA"]`
`NOT IN`	Is not in the list	`["cancelled", "expired"]`

Text operators

Operator	Description	Example value
`LIKE`	Matches pattern (use `%` as wildcard)	`"%kondado%"`
`NOT LIKE`	Does not match pattern	`"%test%"`
`STARTS_WITH`	Starts with	`"BR"`
`ENDS_WITH`	Ends with	`".com"`
`CONTAINS`	Contains the text	`"kondado"`
`NOT_CONTAINS`	Does not contain	`"test"`

Null operators

Operator	Description
`IS NULL`	Is null
`IS NOT NULL`	Is not null

Range operator

Operator	Description	Example value
`BETWEEN`	Is between two values	`[100, 500]`

Relative date operators

These operators make date filtering easy without manual date calculations:

Operator	Description
`LAST_N_DAYS`	Last N days
`NEXT_N_DAYS`	Next N days
`LAST_N_HOURS`	Last N hours
`LAST_N_MINUTES`	Last N minutes
`THIS_WEEK`	Current week
`LAST_WEEK`	Previous week
`THIS_MONTH`	Current month
`LAST_MONTH`	Previous month
`THIS_QUARTER`	Current quarter
`LAST_QUARTER`	Previous quarter
`THIS_YEAR`	Current year
`LAST_YEAR`	Previous year

Example:

{"column": "created_at", "operator": "LAST_N_DAYS", "value": 7}

Aggregations

To calculate metrics on your data, use column_aggregations together with dimensions.

The column_aggregations field is a list with the same length as columns. Each position defines the aggregation for the corresponding column. Use "none" for columns without aggregation (these should be listed in dimensions).

{
  "table_name": "orders",
  "columns": ["category", "revenue", "quantity"],
  "column_aggregations": ["none", "sum", "count"],
  "dimensions": ["category"]
}

Available aggregation functions

Function	Description
`none`	No aggregation (grouping column)
`sum`	Sum of values
`avg`	Average of values
`count`	Record count
`count_distinct`	Count of unique values
`min`	Minimum value
`max`	Maximum value
`stddev`	Standard deviation
`variance`	Variance

Sorting (order_by)

"order_by": [
  {"column": "revenue", "direction": "desc"},
  {"column": "name", "direction": "asc"}
]

Calculated columns (customColumns)

Create derived columns using expressions:

"customColumns": [
  {"name": "profit", "expression": "revenue - cost"},
  {"name": "margin", "expression": "(revenue - cost) / revenue * 100"}
]

Calculated columns can be used in columns, where_conditions, order_by, and dimensions, just like any other column.

Functions available in expressions

Math: ABS, CEIL, FLOOR, ROUND, TRUNCATE, MOD, SQRT, POWER, EXP, LOG, LN, SIGN

Text: UPPER, LOWER, TRIM, LTRIM, RTRIM, SUBSTR, SUBSTRING, LENGTH, REPLACE, CONCAT, SPLIT_PART, LEFT, RIGHT, LPAD, RPAD, REVERSE, REPEAT, INSTR, LOCATE, REGEXP_EXTRACT, REGEXP_REPLACE, REGEXP_LIKE

Date: DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATE_FORMAT, DATE_TRUNC, DATE_ADD, DATE_DIFF, CURRENT_DATE, CURRENT_TIMESTAMP, NOW, EXTRACT, TO_DATE, TO_TIMESTAMP, FROM_UNIXTIME

Conversion: CAST, COALESCE, NULLIF, NVL, IFNULL, GREATEST, LEAST

Conditional: IF, IIF, CASE WHEN ... THEN ... ELSE ... END

Column transformations (column_transformations)

Apply sequential transformations to existing columns. Transformations are processed in the order they appear.

"column_transformations": {
  "name": [
    {"type": "text", "operation": "uppercase"}
  ],
  "created_at": [
    {"type": "date", "operation": "year_month"}
  ],
  "amount": [
    {"type": "number", "operation": "round", "params": {"decimals": 2}}
  ]
}

Text transformations: uppercase, lowercase, trim, ltrim, rtrim, extract_domain, extract_before, extract_after, replace (params: find, replace), regexp_extract (params: pattern), regexp_replace (params: pattern, replacement)

Date transformations: date, year_month, year_week, year_quarter, year, hour, minute, extract_year, extract_month, extract_day, extract_hour, date_add_days (params: days), date_add_hours (params: hours), date_add_minutes (params: minutes)

Number transformations: round (params: decimals), floor, ceil, abs, truncate (params: decimals), divide (params: divisor), multiply (params: multiplier)

Type conversions: cast_to_text, cast_to_int, cast_to_number, cast_to_date, cast_to_timestamp

JOINs

Combine data from multiple tables:

{
  "table_name": "orders",
  "columns": ["orders.id", "customers.name", "orders.total"],
  "column_aggregations": ["none", "none", "none"],
  "joins": [
    {
      "tableName": "customers",
      "joinType": "LEFT",
      "conditions": [
        {
          "leftColumn": "orders.customer_id",
          "rightColumn": "customers.id"
        }
      ]
    }
  ]
}

JOIN types: INNER (only matching records), LEFT (all from main table + matches), RIGHT (all from joined table + matches), FULL (all from both), CROSS (cartesian product).

When using JOINs, prefix column names with the table name (e.g., orders.id, customers.name).

UNION ALL

Combine results from queries on different tables. All queries go inside the union array (minimum 2). The order_by and limit fields are placed at the root level.

{
  "union": [
    {
      "table_name": "sales_2025",
      "dest_collection_id": 12345,
      "columns": ["month", "revenue"],
      "column_aggregations": ["none", "sum"],
      "dimensions": ["month"]
    },
    {
      "table_name": "sales_2024",
      "columns": ["month", "revenue"],
      "column_aggregations": ["none", "sum"],
      "dimensions": ["month"]
    }
  ],
  "order_by": [{"column": "revenue", "direction": "desc"}],
  "limit": 500
}

Limits and restrictions

Restriction	Value
Maximum records per query	1,000
Filter conditions	AND only
Subqueries	Not supported
DISTINCT	Not supported (use `count_distinct`)
Simple UNION	Not supported (UNION ALL only)

Complete examples

Sales by region in the last month

{
  "dest_collection_id": 12345,
  "table_name": "sales",
  "columns": ["region", "revenue", "orders"],
  "column_aggregations": ["none", "sum", "count"],
  "dimensions": ["region"],
  "where_conditions": [
    {"column": "sale_date", "operator": "LAST_MONTH"}
  ],
  "order_by": [{"column": "revenue", "direction": "desc"}]
}

Top 10 customers by purchase value

{
  "dest_collection_id": 12345,
  "table_name": "orders",
  "columns": ["orders.customer_id", "customers.name", "orders.amount"],
  "column_aggregations": ["none", "none", "sum"],
  "dimensions": ["orders.customer_id", "customers.name"],
  "joins": [
    {
      "tableName": "customers",
      "joinType": "LEFT",
      "conditions": [
        {"leftColumn": "orders.customer_id", "rightColumn": "customers.id"}
      ]
    }
  ],
  "order_by": [{"column": "amount", "direction": "desc"}],
  "limit": 10
}

Filter with multiple conditions

{
  "dest_collection_id": 12345,
  "table_name": "leads",
  "columns": ["name", "email", "source", "score"],
  "where_conditions": [
    {"column": "score", "operator": ">=", "value": 80},
    {"column": "source", "operator": "IN", "value": ["google", "facebook"]},
    {"column": "email", "operator": "IS NOT NULL"},
    {"column": "created_at", "operator": "LAST_N_DAYS", "value": 90}
  ],
  "order_by": [{"column": "score", "direction": "desc"}],
  "limit": 100
}

_{– KSQL queries are in beta mode}