Query

Data can be queried provided data has been uploaded at some point in the past.

Construct Query

There are six values you can customise:

• select_columns
  • Which column(s) you want to select
  • List of strings
  • Can contain aggregation functions e.g.: "avg(col1)", "sum(col2)"
  • Can contain renaming of columns e.g.: "col1 AS custom_name"
• filter
  • How to filter the data
  • This is provided via json and converted to a raw SQL string
  • Each condition has column, operator, and value
• group_by_columns
  • Which columns to group by
  • List of column names as strings
• aggregation_conditions
  • What conditions you want to apply to aggregated values (HAVING clause)
  • This is provided via json and converted to a raw SQL string
  • Same structure as filter
• order_by_columns
  • By which column(s) to order the data
  • List of objects with column and direction ("ASC" or "DESC")
  • Defaults to ascending (ASC) if not provided
• limit - How many rows to limit the results to - Integer

Filter Format

Single Condition

{
  "filter": {
    "conditions": [
      {"column": "age", "operator": ">", "value": 18}
    ]
  }
}

AND Filter

Combine multiple conditions with AND or OR operators:

{
  "filter": {
    "logic_operator": "AND",
    "conditions": [
      {"column": "age", "operator": ">", "value": 18},
      {"column": "status", "operator": "=", "value": "active"}
    ]
  }
}

{
  "filter": {
    "logic_operator": "OR",
    "conditions": [
      {"column": "priority", "operator": "=", "value": "high"},
      {"column": "priority", "operator": "=", "value": "urgent"}
    ]
  }
}

Complex Nested Filters

Nest FilterGroups for complex boolean expressions:

{
  "filter": {
    "logic_operator": "AND",
    "conditions": [
      {"column": "status", "operator": "=", "value": "active"}
    ],
    "groups": [
      {
        "logic_operator": "OR",
        "conditions": [
          {"column": "category", "operator": "=", "value": "electronics"},
          {"column": "category", "operator": "=", "value": "books"}
        ]
      }
    ]
  }
}

This generates SQL: WHERE status = 'active' AND (category = 'electronics' OR category = 'books')

Available Operators

Operator	Description	Example
=	Equals	{"column": "age", "operator": "=", "value": 25}
!=	Not equals	{"column": "status", "operator": "!=", "value": "deleted"}
>	Greater than	{"column": "price", "operator": ">", "value": 100}
>=	Greater than or equal	{"column": "age", "operator": ">=", "value": 18}
<	Less than	{"column": "stock", "operator": "<", "value": 10}
<=	Less than or equal	{"column": "discount", "operator": "<=", "value": 50}
LIKE	Pattern matching	{"column": "name", "operator": "LIKE", "value": "John%"}
NOT LIKE	Negative pattern matching	{"column": "email", "operator": "NOT LIKE", "value": "%test%"}
IN	Value in list	{"column": "status", "operator": "IN", "value": ["active", "pending"]}
NOT IN	Value not in list	{"column": "role", "operator": "NOT IN", "value": ["admin", "superuser"]}
IS NULL	Value is null	{"column": "deleted_at", "operator": "IS NULL"} (no value)
IS NOT NULL	Value is not null	{"column": "created_at", "operator": "IS NOT NULL"} (no value)

Complete Example

{
  "select_columns": ["col1", "avg(col2)"],
  "filter": {
    "logic_operator": "AND",
    "conditions": [
      {"column": "col2", "operator": ">=", "value": 10}
    ]
  },
  "group_by_columns": ["col1"],
  "aggregation_conditions": {
    "logic_operator": "AND",
    "conditions": [
      {"column": "avg(col2)", "operator": "<=", "value": 15}
    ]
  },
  "order_by_columns": [
    {
      "column": "col1",
      "direction": "DESC"
    },
    {
      "column": "col2",
      "direction": "ASC"
    }
  ],
  "limit": 30
}

This generates SQL:

SELECT col1, avg(col2)
FROM table
WHERE col2 >= 10
GROUP BY col1
HAVING avg(col2) <= 15
ORDER BY col1 DESC, col2 ASC
LIMIT 30

Note: If you do not specify a customised query, and only provide the domain and dataset, you will select the entire dataset