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:

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

Set operators

Text operators

Null operators

Range operator

Relative date operators

These operators make date filtering easy without manual date calculations:

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

Column aliases (column_aliases)

Use column_aliases to rename the columns returned by your query. The array must be the same length as columns and column_aggregations. Each position defines the output column name for the corresponding column. Use null to keep the original name.

{
  "ksql": {
    "table_name": "orders",
    "columns": ["category", "revenue", "quantity"],
    "column_aggregations": ["none", "sum", "count"],
    "dimensions": ["category"],
    "column_aliases": [null, "total_revenue", "total_orders"]
  }
}

In this example, the output columns will be: category, total_revenue, and total_orders.

Table alias (alias)

The root-level alias field defines an alias for the main table in your query, useful when you need to prefix columns to avoid ambiguity (especially with JOINs and CTEs).

{
  "ksql": {
    "table_name": "orders",
    "alias": "o",
    "columns": ["o.id", "o.customer_id", "o.total"],
    "joins": [
      {
        "tableName": "customers",
        "joinType": "LEFT",
        "conditions": [
          {"leftColumn": "o.customer_id", "rightColumn": "customers.id"}
        ]
      }
    ]
  }
}

The root-level alias is different from the alias used inside CTE entries in the with parameter.

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:

* 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 CTEs

A CTE can contain its own inner CTEs, allowing subquery chaining in layers (a CTE can reference another previously declared CTE, but not itself — recursion is not supported):

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

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": 1000
  }
}

_{– KSQL queries are in beta mode}