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 sent as a JSON object inside the ksql key. The available fields are:

Field	Required	Description
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)
with	No	Array of CTEs (Common Table Expressions) — reusable named subqueries (see section below)

Minimal example

{
  "ksql": {
    "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}
]

Column-to-column comparison (value_column)

Instead of comparing a column to a fixed value, you can compare a column to another column using value_column instead of value:

"where_conditions": [
  {"column": "revenue", "operator": ">", "value_column": "cost"},
  {"column": "ship_date", "operator": "<=", "value_column": "deadline"}
]

value_column accepts only valid column names (letters, numbers, _ and .). The same security rules as column apply.

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).

{
  "ksql": {
    "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:

{
  "ksql": {
    "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.

{
  "ksql": {
    "union": [
      {
        "table_name": "sales_2025",
      "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
  }
}

CTEs — Common Table Expressions (with)

The with parameter lets you create named subqueries (CTEs) that can be referenced in the main query as if they were tables. This is useful for organizing complex queries into steps.

with is an array of objects. Each object defines a CTE with:

Field	Required	Description
alias	Yes	CTE name (used as table name in the main query)
ksql	Yes*	KSQL query that defines the CTE data
saved_query_id	Yes*	ID of a saved query to use as CTE (alternative to ksql)

* Use ksql or saved_query_id, never both.

CTE with inline query

{
  "ksql": {
    "with": [
      {
        "alias": "recent_sales",
        "ksql": {
          "table_name": "sales",
          "columns": ["product", "revenue", "sale_date"],
          "where_conditions": [
            {"column": "sale_date", "operator": "LAST_N_DAYS", "value": 30}
          ]
        }
      }
    ],
    "table_name": "recent_sales",
    "columns": ["product", "revenue"],
    "column_aggregations": ["none", "sum"],
    "dimensions": ["product"],
    "order_by": [{"column": "revenue", "direction": "desc"}],
    "limit": 10
  }
}

CTE with saved query

If you have saved queries, you can reference them by ID instead of rewriting the KSQL:

{
  "ksql": {
    "with": [
      {
        "alias": "customer_base",
        "saved_query_id": 31
      }
    ],
    "table_name": "customer_base",
    "columns": ["name", "email", "total_purchases"],
    "order_by": [{"column": "total_purchases", "direction": "desc"}],
    "limit": 50
  }
}

Multiple CTEs

You can define multiple CTEs and combine them in the main query with JOINs:

{
  "ksql": {
    "with": [
      {
        "alias": "product_revenue",
        "ksql": {
          "table_name": "sales",
          "columns": ["product_id", "revenue"],
          "column_aggregations": ["none", "sum"],
          "dimensions": ["product_id"]
        }
      },
      {
        "alias": "product_cost",
        "ksql": {
          "table_name": "costs",
          "columns": ["product_id", "total_cost"],
          "column_aggregations": ["none", "sum"],
          "dimensions": ["product_id"]
        }
      }
    ],
    "table_name": "product_revenue",
    "columns": ["product_revenue.product_id", "product_revenue.revenue", "product_cost.total_cost"],
    "joins": [
      {
        "tableName": "product_cost",
        "joinType": "LEFT",
        "conditions": [
          {"leftColumn": "product_revenue.product_id", "rightColumn": "product_cost.product_id"}
        ]
      }
    ]
  }
}

Nested (recursive) CTEs

A CTE can contain its own inner CTEs, allowing subquery chaining:

{
  "ksql": {
    "with": [
      {
        "alias": "summary",
        "ksql": {
          "with": [
            {
              "alias": "monthly_sales",
              "ksql": {
                "table_name": "sales",
                "columns": ["category", "revenue"],
                "column_aggregations": ["none", "sum"],
                "dimensions": ["category"],
                "where_conditions": [
                  {"column": "sale_date", "operator": "LAST_MONTH"}
                ]
              }
            }
          ],
          "table_name": "monthly_sales",
          "columns": ["category", "revenue"],
          "where_conditions": [
            {"column": "revenue", "operator": ">", "value": 1000}
          ]
        }
      }
    ],
    "table_name": "summary",
    "columns": ["category", "revenue"],
    "order_by": [{"column": "revenue", "direction": "desc"}]
  }
}

⚠️ All CTEs run in the same database as the main query. The same security rules and validation apply to each CTE individually.

Limits and restrictions

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

Complete examples

Sales by region in the last month

{
  "ksql": {
    "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

{
  "ksql": {
    "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

{
  "ksql": {
    "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}