Skip to main content

Overview

DQL (Data Query Language) supports different types of functions, depending on how many inputs (operands) they take.

Unary Functions​

A unary function applies a function to a single operand. Typical use cases: math, transformation, logic.

JSON form: 
{
  "$unary": {
    "$fn": <string>,
    "$operand": Object ( Expression )
  }
}
Example: 
{
  "$unary": {
    "$fn": "sum",
    "$operand": {
      "$qualifier": {
        "$table": "vs"
      },
      "$identifier": "nb_users"
    }
  }
}

Binary Functions​

A binary function applies a function to exactly two operands. Typical use cases: arithmetic, comparison, logic.

JSON form: 
{
  "$binary": {
    "$fn": <string>,
    "$left": Object ( Expression ),
    "$right": Object ( Expression )
  }
}
Example: 
{
  "$binary": {
    "$fn": "add",
    "$left": {
      "$scalar": 5
    },
    "$right": {
      "$scalar": 3
    }
  }
}

Variadic Functions​

A variadic function accepts two or more operands. Typical use cases: fallback, greatest/least, string concatenation.

JSON form: 
{
  "$variadic": {
    "$fn": <string>,
    "$operands": Array ( Object ( Expression ) )
  }
}
Example: 
{
  "$variadic": {
    "$fn": "coalesce",
    "$operands": [
      {
        "$unary": {
          "$fn": "sum",
          "$operand": {
            "$qualifier": {
              "$table": "vs"
            },
            "$identifier": "nb_users"
          }
        }
      },
      {
        "$scalar": 0
      }
    ]
  }
}

Aggregate Functions​

Aggregate functions perform a computation over a set of rows and return a single result. Typical use cases: count, average, sum, etc.

JSON form: 
{
  "$agg": {
    "$fn": <string>,
    "$distinct": <boolean>,                    
    "$filter": Object ( LogicalExpression ),    
    "$orderBy": [ Object ( OrderBy ) ],         
    "$operands": [ Object ( Expression ) ]
  }
}

Example: 
{
  "$agg": {
    "$fn": "avg",
    "$distinct": false,
    "$operands": [
      {
        "$identifier": "revenue"
      }
    ]
  }
}

Window Functions​

Window functions compute values across rows that are related to the current row. They do not collapse rows like aggregate functions.

JSON form: 
{
  "$window": {
    "$fn": <string>,
    "$orderBy": [ Object ( OrderBy ) ],       
    "$expressions": [ Object ( Expression ) ],
    "$partitionBy": [ Object ( Expression ) ]
  }
}
Example: 
{
  "$window": {
    "$fn": "rank",
    "$orderBy": [
      {
        "$expression": {
          "$identifier": "score"
        },
        "$direction": "desc"
      }
    ],
    "$partitionBy": [
      {
        "$identifier": "department"
      }
    ]
  }
}

Example: Embedded Functions​

Sometimes, functions can be nested, where one function serves as an operand to another. For instance, a coalesce (variadic) function might use an aggregate like last(...) as one of its arguments.

JSON form: 
{
  "$as": "value",
  "$variadic": {
    "$fn": "coalesce",
    "$operands": [
      {
        "$agg": {
          "$fn": "last",
          "$orderBy": [
            {
              "$expr": {
                "$qualifier": {
                  "$table": "t1"
                },
                "$identifier": "timestamp"
              }
            }
          ],
          "$operands": [
            {
              "$qualifier": {
                "$table": "t1"
              },
              "$identifier": "data"
            }
          ]
        }
      },
      {
        "$scalar": 0
      }
    ]
  },
  "$shortCast": [
    "INT"
  ]
}