Query Language Reference

This guide explains how to write JSON-based queries for the Docwize Query Builder. Just follow the DSL (Domain-Specific Language) rules below to construct queries that the system will translate into SQL.

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	SQL LIKE '%…%'	{ "contains":[{"var":"Email"},"@docwize.com"] }
in	column IN (subquery)	{ "in":[ tblSpec, {"select":[...]} ] }
groupby	GROUP BY	{ "groupby":[ colSpec, ... ] }
aggregations	Summary functions (sum, count, etc.)	{ "aggregations":[ {"alias":"total","aggregator":"sum"} ] }
order_by	ORDER BY	{ "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 any 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",     // must be whitelisted
    "args": [
      { "keyword": "DAY" },
      { "var": "Table.Date1" },
      { "func": {"name": "GETDATE", "args": []} }
    ],
    "label": "days_diff"
  }
}

• name: any function in the Allowed Functions list (see below).
• 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 SQL tokens (e.g. date parts, casting keywords):

{ "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 → SQL column LIKE '%' || :param || '%'

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

  {
    "in": [
      { "var": "Users.id" },
      { "select": [ { "var": "AllowedUsers.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 a whitelisted func (e.g. SUM, COUNT).

Example:

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

The builder wraps the inner query as a subquery, then applies .group_by(...) and the aggregate calls.

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.

Allowed Functions

Below are the functions users can call via the "func" spec. Function names are case-insensitive but typically written uppercase. Anything not on this list will be rejected.

MSSQL Date & Time: GETDATE, GETUTCDATE, DATEADD, DATEDIFF, DATEPART, CURRENT_TIMESTAMP, DATENAME, DAY, MONTH, SYSDATETIME, SYSUTCDATETIME, TIMEFROMPARTS, TODATETIMEOFFSET, YEAR

MSSQL String: ASCII, CHAR, CHARINDEX, CONCAT, FORMAT, LEFT, LEN, LOWER, LTRIM, NCHAR, PATINDEX, QUOTENAME, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, STR, STUFF, SUBSTRING, TRANSLATE, TRIM, UNICODE, UPPER

MSSQL Numeric: ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, TAN

MSSQL Aggregates: AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, MAX, MIN, STDEV, STDEVP, SUM, VAR, VARP

MSSQL Logical: CHOOSE, COALESCE, IIF, ISNULL, NULLIF

MSSQL JSON: JSON_VALUE, JSON_QUERY, JSON_MODIFY, ISJSON, OPENJSON

MSSQL Window & Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK

MSSQL Cryptographic: HASHBYTES

MSSQL Text Search: CONTAINS, LIKE

MSSQL System (safe): CAST, CONVERT, PARSE, TRY_CAST, TRY_CONVERT, TRY_PARSE

MSSQL XML: FOR XML, NODES, QUERY, VALUE

MSSQL Spatial: STArea, STAsText, STBuffer, STDistance, STIntersects, STLength, STOverlaps

PostgreSQL Date & Time: NOW, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, AGE, TO_TIMESTAMP, TO_DATE, DATE_PART, DATE_TRUNC

PostgreSQL String: CHAR_LENGTH, LOWER, UPPER, INITCAP, CONCAT, POSITION, SUBSTRING, TRIM, OVERLAY, REPLACE, TO_CHAR, TO_NUMBER, SPLIT_PART

PostgreSQL Numeric: ABS, CEIL, FLOOR, ROUND, POWER, MOD, SQRT, EXP, LN, LOG, RADIANS, DEGREES, PI, RANDOM, SIGN, TRUNC, COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2

PostgreSQL Aggregates: AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE

PostgreSQL JSON: JSON_BUILD_OBJECT, JSONB_BUILD_OBJECT, JSON_ARRAY_LENGTH, JSONB_ARRAY_LENGTH, JSON_EXTRACT_PATH_TEXT, TO_JSON, TO_JSONB

PostgreSQL Logical: COALESCE, NULLIF, CASE, GREATEST, LEAST

PostgreSQL Window: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE

PostgreSQL Text Search: TO_TSVECTOR, TO_TSQUERY, PLAINTO_TSQUERY, PHRASETO_TSQUERY, WEBSEARCH_TO_TSQUERY, TS_RANK, TS_RANK_CD

Docwize DW: FNGETPERIOD, FNGETPERIOD_WEEK

Allowed Keywords

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

General Casting/Data Types: CAST, CONVERT, AS

Character/String Types: CHAR, CHARACTER, VARCHAR, TEXT, NCHAR, NVARCHAR

Numeric Types: DECIMAL, NUMERIC, INT, INTEGER, BIGINT, SMALLINT, FLOAT, REAL, DOUBLE

Date/Time Types: DATE, TIME, DATETIME, TIMESTAMP, INTERVAL, ZONE

Boolean: BOOLEAN, TRUE, FALSE

Binary/BLOB: BINARY, VARBINARY, BYTEA

JSON: JSON, JSONB

Array: ARRAY

PostgreSQL-specific: UUID, TSVECTOR, TSQUERY, CIDR, INET, MACADDR, BIT, VARBIT, XML, GEOMETRY

Trimming: LOWER, UPPER, TRIM, TRAILING, LEADING

Null: NULL

DateParts for DATEDIFF or EXTRACT: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND

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.
• Parameter binding: literal values become bound params (:Param_1).
• Optional filters: use optional_runtime_input to skip filters if no input supplied.
• Validation: unsupported functions/keywords produce clear parse-time errors.

Calling Functions in Queries

To use database functions (e.g. date calculations, string operations), include a func clause in the JSON DSL. This tells the builder to wrap the expression in the corresponding SQL function.

{
  "func": {
    "name": "<FUNCTION_NAME>",   // Must match a function in the “Allowed Functions” list
    "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) exactly as listed under Allowed Functions.

• 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"
  }
}

CASE
  WHEN "Users"."status" = :status_1 THEN "Users"."login_count"
  WHEN "Users"."status" = :status_2 THEN 0
END AS login_when_active

2. Date Difference: DATEDIFF & GETDATE

Calculate days since order creation:

{
  "func": {
    "name": "DATEDIFF",
    "args": [
      { "keyword": "DAY" },
      { "var": "Orders.created_at" },
      { "func": { "name": "GETDATE", "args": [] } }
    ],
    "label": "age_in_days"
  }
}

DATEDIFF(DAY, "Orders"."created_at", GETDATE()) AS age_in_days

3. 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"
  }
}

CONCAT(LOWER("Users"."firstname"), ' ', "Users"."lastname") AS full_name

4. 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"
  }
}

ROUND(ABS("Transactions"."amount"), 2) AS abs_amount_rounded

5. 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" } ]
}

SELECT "Products"."category",
       COUNT("Products"."id") AS product_count
FROM "Products"
GROUP BY "Products"."category";

6. Nested Functions

Use the result of one function as an argument to another:

{
  "func": {
    "name": "DATEADD",
    "args": [
      { "keyword": "MONTH" },
      1,
      {
        "func": {
          "name": "DATEDIFF",
          "args": [
            { "keyword": "DAY" },
            { "var": "Subscriptions.start_date" },
            { "func": { "name": "CURRENT_TIMESTAMP", "args": [] } }
          ]
        }
      }
    ],
    "label": "next_billing"
  }
}

DATEADD(MONTH, 1,
        DATEDIFF(DAY, "Subscriptions"."start_date", CURRENT_TIMESTAMP)
) AS next_billing

With this pattern—specifying name, args, and optional label—users can leverage any whitelisted function, simple or nested, directly in their JSON queries to build rich, dynamic SQL expressions.