Query Language Reference

This guide explains how to write JSON-based queries for the Docwize Query Builder. The rules below describe the query format.

Query Structure

A query is a JSON array of clauses. Clauses are processed in order:

1. from: specify the data source (table, view, or virtual view)
2. join/left_join/outer_join: relate additional tables or views
3. select: list fields or expressions to return
4. where-style filters (and, or, comparison operators)
5. groupby + aggregations: grouping and summary functions
6. order_by: sort the final results

Skeleton:

[
  { "from": [ { "var": "TableOrView" } ] },
  { "join": [ tableSpec, onCondition ] },
  { "select": [ colSpec, ... ] },  
  { "and": [ cond1, cond2 ] },
  { "groupby": [ ... ] },
  { "aggregations": [ ... ] },
  { "order_by": [ ... ] }
]

Referencing Virtual JSON Views

Users can treat any cached “virtual view” like a table by referencing its name or ID in braces:

• By ID: {42} references the view with id = 42.
• By name: {MyViewName} (case-insensitive) references the view whose name matches.

Once referenced in a from or join, all columns that view outputs become available for select, filters, etc. Users can also add an alias:

{ "from": [ { "var": "{42}",  "alias": "v" } ] }

Top-Level Clauses

Clause	Purpose	Example
from	Base source	{ "from":[ {"var":"Users"} ] } or { "from":[ {"var":"{ReportsSummary}","alias":"r"} ] }
join	Inner join	{ "join":[ tblSpec, condSpec ] }
left_join	Left (outer) join	{ "left_join":[ tblSpec, condSpec ] }
outer_join	Left outer join (alias for left_join)	{ "outer_join":[ tblSpec, condSpec ] }
select	Columns / expressions to return	{ "select":[ colSpec, ... ] }
and / or	Combine filter conditions	{ "and":[cond1,cond2] }, { "or":[...] }
Comparison ops	==, !=, <, <=, >, >=	{ "<":[{"var":"Amount"}, 1000] }
contains	Contains the text	{ "contains":[{"var":"Email"},"@example.com"] }
in	column IN (subquery)	{ "in":[ tblSpec, {"select":[...]} ] }
groupby	Group rows	{ "groupby":[ colSpec, ... ] }
aggregations	Summary functions (sum, count, etc.)	{ "aggregations":[ {"alias":"total","aggregator":"sum"} ] }
order_by	Sort rows	{ "order_by":[ {"alias":"total","desc":true} ] }

Where:

• tblSpec is identical to a select-style colSpec but refers to a table/view without a column, e.g.:

  { "var": "MyTable" }

• condSpec is any boolean/comparison/json operator clause (see below).

Column & Expression Specs

1. Column reference (var)

Use to pull a column from a table or view (including virtual views):

{ "var": "TableOrView.ColumnName" }

With alias or label

{ "var": [
    "Table.Column",          // table or view name (with `{}` if a view)
    { "alias": "t" },      // optional alias for the table/view
    { "label": "col_alias" } // optional label for the column
] }

2. Virtual View reference in var

{ "var": [
    "{42}.MetricValue",      // columns from view id 42
    { "alias": "mv" },     // optional alias
    { "label": "value" }   // optional column label
] }

3. Function call (func)

{
  "func": {
    "name": "DATEDIFF",     // a function from the Functions tab
    "args": [
      { "keyword": "DAY" },
      { "var": "Table.Date1" },
      { "func": {"name": "GETDATE", "args": []} }
    ],
    "label": "days_diff"
  }
}

• name: a function from the Functions tab of the Query Lab (see Functions).
• args: array of any valid spec (var, nested func, literal, arithmetic).
• label: optional alias for the resulting column.

4. Literal keywords (keyword)

Used for bare keywords (e.g. date parts, data types):

{ "keyword": "DAY" }
{ "keyword": "VARCHAR" }

5. Arithmetic (operation)

{
  "operation": "+",           // one of +, -, *, /
  "left": {"var":"A.x"},
  "right": {"var":"B.y"},
  "label": "sum_xy"         // optional alias
}

6. Runtime inputs

Pull in user-supplied values at execution time:

{ "runtime_input": "filter.min_date" }
{ "optional_runtime_input": ["filter","status"] }

• runtime_input: if missing or null, the entire condition is skipped.
• optional_runtime_input: if missing, skipped quietly (does not abort).

Boolean & Comparison Clauses

These appear either inline or inside and/or arrays:

{ "==": [ expr1, expr2 ] }
{ "!=" : [ expr1, expr2 ] }
{ ">":  [ expr1, expr2 ] }
{ "<=": [ expr1, expr2 ] }
{ "contains": [ expr, substring ] }
{ "in": [ tblSpec, subquery ] }

• contains → the column contains the text.

• in → subquery must be a select clause, e.g.:

  {
    "in": [
      { "var": "Users.id" },
      { "select": [ { "var": "Managers.id" } ] }
    ]
  }

Combine with logical operators:

{ "and": [ cond1, cond2, ... ] }
{ "or":  [ ... ] }
{ "not": cond }

Grouping & Aggregation

• groupby: list of columns/exprs to group by.
• aggregations: list of objects { alias, aggregator }, where aggregator is an aggregate function (e.g. SUM, COUNT).

Example:

{
  "groupby": [ {"var":"Orders.status"} ],
  "aggregations": [ {"alias":"count_by_status","aggregator":"COUNT"} ]
}

Ordering Results

{ "order_by": [ {"alias":"count_by_status","desc":true} ] }

• alias must match a selected or aggregated column.
• desc: true for descending, false (or omitted) for ascending.

Functions

The Functions tab of the Query Lab lists the functions a query can call. Function names are not case-sensitive. A function that is not supported is refused when the view is saved.

Keywords

Literals recognized via the "keyword" spec (case-insensitive):

Casting: CAST, CONVERT, AS

Text types: CHAR, VARCHAR, TEXT

Numeric types: DECIMAL, NUMERIC, INT, INTEGER, BIGINT, FLOAT

Date and time types: DATE, TIME, TIMESTAMP

Boolean: BOOLEAN, TRUE, FALSE

Null: NULL

Date parts: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND

Tips & Gotchas

• Virtual views: use {} to bring in JSON-defined views by id or name.
• Alias duplicates: when joining the same source multiple times, always assign an alias.
• Optional filters: use optional_runtime_input to skip filters if no input supplied.
• Validation: a function or keyword that is not supported is refused when the view is saved.

Calling Functions in Queries

To use functions (e.g. date calculations, string operations), include a func clause in the query.

{
  "func": {
    "name": "<FUNCTION_NAME>",   // A function from the Functions tab
    "args": [ <arg1>, <arg2>, ... ],  // Arguments: literals, keywords, column refs, or nested funcs
    "label": "alias"             // (Optional) Alias for the result column
  }
}

• name: The function’s name (case‑insensitive) as listed on the Functions tab.

• args: An array where each element is one of:

  • A column reference: { "var": "Table.Column" }
  • A literal value: e.g. 100, "text"
  • A keyword: { "keyword": "DAY" }
  • A nested function: another { "func": {...} } object

• label: (optional) The output column name in the result set.

Examples of Function Usage

1. Conditional Logic with CASE

Return login_count when status is Active, else 0 when Inactive:

{
  "func": {
    "name": "case",
    "args": [
      [
        [ { "==": [ { "var": "Users.status" }, "Active" ] },
          { "var": "Users.login_count" }
        ],
        [ { "==": [ { "var": "Users.status" }, "Inactive" ] },
          0
        ]
      ]
    ],
    "label": "login_when_active"
  }
}

2. String Manipulation: CONCAT & LOWER

Build a lowercase full name:

{
  "func": {
    "name": "CONCAT",
    "args": [
      { "func": { "name": "LOWER", "args": [ { "var": "Users.firstname" } ] } },
      " ",
      { "var": "Users.lastname" }
    ],
    "label": "full_name"
  }
}

3. Numeric Rounding: ABS & ROUND

Absolute value rounded to 2 decimals:

{
  "func": {
    "name": "ROUND",
    "args": [
      { "func": { "name": "ABS", "args": [ { "var": "Transactions.amount" } ] } },
      2
    ],
    "label": "abs_amount_rounded"
  }
}

4. Aggregates: SUM & COUNT

Count products by category:

{
  "select": [
    { "var": "Products.category" },
    {
      "func": {
        "name": "COUNT",
        "args": [ { "var": "Products.id" } ],
        "label": "product_count"
      }
    }
  ],
  "groupby": [ { "var": "Products.category" } ]
}

With this pattern (name, args, and an optional label), users can call any function from the Functions tab in a query.