Datatype Functions

These functions can be used on attributes in the SELECT clause. Arguments can be literals, attributes, or in some cases, other SELECT functions.

Overview

String Functions

These functions can also be used as arguments for any parameter that takes the ANY or STRING-VALUE types.

Function	Description
CONCAT	Concatenates strings together. E.g. CONCAT('Dr ', doctor.first_name, ' ', doctor.last_name)
SUBSTR	Creates a substring from a string value. E.g. SUBSTR(t.value, 0, 3)
TOSTRING	Converts value to a string. E.g. TOSTRING(20)
TOUPPER	Converts a string value to uppercase. E.g. TOUPPER('str')
TOLOWER	Converts a string value to lowercase. E.g. TOLOWER('STR')
TRIM	Removes whitespace off start and end of string. E.g. TRIM(' str ')
DATETOSTRING	Converts a date to a string, with an optional custom format. E.g. DATETOSTRING(20, '%m-%d-%Y')
DATETOSTRINGTZ	Converts a date to a string, taking a timezone argument and an optional custom format. E.g. DATETOSTRINGTZ(20, '-0530', '%m-%d-%Y')

Numeric Functions

These functions can also be used as arguments for any parameter that takes the ANY or NUMERIC-VALUE types.

Function	Description
ADD	Add 2 or more numeric values. E.g. ADD(3, 5.6, t.int_value, e.float_value)
SUBTRACT	Subtract 2 numeric values. E.g. SUBTRACT(3, 5.6, t.int_value, e.float_value)
MULTIPLY	Multiply 2 or more numeric values. E.g. MULTIPLY(3, 5.6, t.int_value, e.float_value)
DIVIDE	Divide 2 numeric values. E.g. DIVIDE(t.int_value, 3)
MOD	Modulus operator of 2 numeric values. E.g. MOD(t.int_value, 4)
SQRT	Get the square root of a numeric value. E.g SQRT(9)
TOLONG	Convert a value to a long. E.g TOLONG('1321113')
ROUND	Round a numeric value. E.g ROUND(100.5)

Integer Functions

These functions can also be used as arguments for any parameter that takes the ANY, NUMERIC-VALUE, or INTEGER-VALUE types.

Function	Description
TOINT	Casts a value to an integer. E.g. TOINT('15')
SIZE	Computes the size of the list. E.g. SIZE(t.my_list)
FLOOR	Round a numeric value down to an integer. E.g FLOOR(100.5)
CEIL	Round a numeric value up to an integer. E.g CEIL(100.5)
STRLENGTH	Computes the size of the string. E.g. STRLENGTH(t.my_list)
YEAR	Returns the 4 digit year integer value of a date. E.g. MONTH(t.my_date_time)
MONTH	Returns the month integer value of a date. E.g. MONTH(t.my_date_time)
WEEK	Returns the week integer value of a date. E.g. WEEK(t.my_date_time)
DAYOFYEAR	Returns the day of year integer value of a date. E.g. DAYOFYEAR(t.my_date_time)
DAYOFMONTH	Returns the day of month integer value of a date. E.g. DAYOFMONTH(t.my_date_time)
DAYOFWEEK	Returns the day of week integer value of a date. E.g. DAYOFWEEK(t.my_date_time)
HOUR	Returns the hour integer value of a date. E.g. HOUR(t.my_date_time)
MINUTE	Returns the minute integer value of a date. E.g. MINUTE(t.my_date_time)
SECOND	Returns the second integer value of a date. E.g. SECOND(t.my_date_time)
MILLISECOND	Returns the millisecond integer value of a date. E.g. MILLISECOND(t.my_date_time)

Boolean Functions

These functions can also be used as arguments for any parameter that takes the BOOLEAN types.

Function	Description
TOBOOL	Converts a value to a boolean. E.g TOBOOL(1)

Date Functions

These functions can also be used as arguments for any parameter that takes the ANY type.

Function	Description
DATEFROMPARTS	Creates a date from components. E.g DATEFROMPARTS(2000, MONTH: 3, DAY: 10, TIMEZONE: '+04:45')
DATEFROMSTRING	Creates a date from a string. E.g DATEFROMSTRING('2017-02-08T12:10:40.787')
DATEFROMSTRINGTZ	Creates a date from a string using a timezone. E.g DATEFROMSTRING('2017-02-08T12:10:40.787', '+04:45')
TODATE	Converts a value to a date. E.g TODATE(120000000000.5)

List Functions

These functions can also be used as arguments for any parameter that takes the ANY or LIST-VALUE types.

Function	Description
APPEND	Concatenates lists together. E.g. APPEND(t.value, [1], [1, 2, 3])
SLICE	Slices a list attribute into smaller list. E.g. SLICE(t.my_list, 3, 5)
MAP	Transform each element of a list. E.g. MAP(t.my_list USING elem WITH TOINT(elem))
FILTER	Remove elements from a list based on a condition. E.g. FILTER(t.my_list USING elem WITH elem GT 0)

Literals

As part of a SELECT clause, the user can set literals in an output attribute. For instance, it may be advantageous to create a flag column set to true. Here are some examples of literals in a SELECT clause:

SELECT Date('2020-01-01') AS date_literal,
    'my string' AS string_literal,
    true AS boolean_literal,
    32 AS integer_literal,
    [1, 2, 3] AS list_literal,
    {
        'key1': 'value1',
        'key2': 'value2',
        'subdocument': {
            'list': [1, 2, 3],
        }
    } AS document_literal

It is worth noting you can use SELECT functions inside document and list literal declarations:

    SELECT
        {
            'conditional_value': IF(str_attr ISNULL THEN 'default_value' ELSE str_attr),
            'list': [0, 1, ADD(x, 10)]
        } AS document

Examples:

CONCAT

CONCAT(<string1>, <string2>, ...)

Parameter	Type	Description
stringN	STRING-VALUE	String to be concatenated (one or more)

Here is a basic document we can apply CONCAT on:

{
  "first_name" : "John",
  "last_name" : "Smith"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT *,
    CONCAT(first_name, last_name) AS full_name,
    CONCAT("My name is ", first_name) AS description,
    CONCAT("I can add these ", "together") AS sentence

Resulting document:

{
  "first_name" : "John",
  "last_name" : "Smith",
  "full_name" : "John Smith",
  "description" : "My name is John",
  "sentence" : "I can add these together"
}

---

SUBSTR

SUBSTR(<input-string>, <index>)

OR

SUBSTR(<input-string>, <index>, <length>)

Parameter	Type	Description
input-string	STRING-VALUE	String to substring
index	INTEGER-VALUE	0-based index at which new string will start at.
length	INTEGER-VALUE	Length of new string. If negative, new string will be the rest of the input string after index

Here is a basic document we can apply SUBSTR on:

{
  "full_name" : "John Smith"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    full_name,
    SUBSTR(full_name, 0, 4) AS first_name,
    SUBSTR('012345', 2, 3) AS example,
    SUBSTR('012345', 2, -1) AS example2,

Resulting document:

{
  "full_name": "John Smith",
  "first_name": "John",
  "example": "234",
  "example2": "2345"
}

---

TOSTRING

TOSTRING(<input-value>)

Parameter	Type	Description
input-value	ANY	Value to convert to a string

Note that you cannot convert a document or list to a string.

Here is a basic document we can apply TOSTRING on:

{
  "value": 1 
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    TOSTRING(value) AS value_string

Resulting document:

{
  "value": 1, 
  "value_string": "1"
}

---

TOUPPER

TOUPPER(<input-string>)

Parameter	Type	Description
input-string	STRING-VALUE	String to convert to uppercase

Here is a basic document we can apply TOUPPER on:

{
  "my_string": "hello"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_string,
    TOUPPER(my_string) AS upper_string

Resulting document:

{
  "my_string": "hello",
  "upper_string": "HELLO"
}

---

TOLOWER

TOLOWER(<input-string>)

Parameter	Type	Description
input-string	STRING-VALUE	String to convert to lowercase

Here is a basic document we can apply TOLOWER on:

{
  "my_string": "HELLO"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_string,
    TOLOWER(my_string) AS lower_string

Resulting document:

{
  "my_string": "HELLO",
  "lower_string": "hello"
}

---

TRIM

TRIM(<input-string>)

Parameter	Type	Description
input-string	STRING-VALUE	String to convert to trim

Here is a basic document we can apply TRIM on:

{
  "my_string": "   hello    "
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_string,
    TRIM(my_string) AS trimmed_string

Resulting document:

{
  "my_string": "   hello    ",
  "trimmed_string": "hello"
}

---

DATETOSTRING

DATETOSTRING(<input-date>)

OR

DATETOSTRING(<input-date>, <format-str>)

Parameter	Type	Description
input-date	DATE-VALUE	Date to convert to a string
format-str	STRING-VALUE	Format of output. Defaults to %Y-%m-%dT%H:%M:%S.%LZ if unspecified. You can see info on date formats here

Here is a basic document we can apply DATETOSTRING on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    DATETOSTRING(my_date, '%m-%d-%Y %H:%S') AS date_string

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }, 
  "date_string": "06-22-2019 10:30"
}

---

DATETOSTRINGTZ

DATETOSTRINGTZ(<input-date>, <tz-str>)

OR

DATETOSTRINGTZ(<input-date>, <tz-str>, <format-str>)

Parameter	Type	Description
input-date	DATE-VALUE	Date to convert to a string
tz-str	STRING-VALUE	Timezone of output date string
format-str	STRING-VALUE	Format of output. Defaults to %Y-%m-%dT%H:%M:%S.%LZ if unspecified. You can see info on date formats here

tz-str can be one of the following values:

• A string UTC offset, with the format of
  • +/-[hh]:[mm]
  • +/-[hh][mm]
  • +/-[hh]
• A string value of 'GMT', 'UTC', or 'Z'
• A string Olson Timezone Identifier such as 'America/Denver'

Here is a basic document we can apply DATETOSTRINGTZ on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    DATETOSTRINGTZ(my_date, '-0530', '%m-%d-%Y %H:%S') AS date_string

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "date_string": "06-22-2019 04:30"
}

---

ADD

ADD(<value1>, <value2>, ...)

Parameter	Type	Description
valueN	NUMERIC-VALUE	Number to add

Here is a basic document we can apply ADD on:

{
  "value": 1, 
  "value_two": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    value_two,
    ADD(value, value_two) AS sum,
    ADD(2, value_two) AS sum2,
    ADD(2, value_two, 3) AS sum3

Resulting document:

{
  "value": 1,
  "value_two": 2,
  "sum": 3,
  "sum2": 4,
  "sum3": 7
}

---

SUBTRACT

SUBTRACT(<value1>, <value2>)

Parameter	Type	Description
value1	NUMERIC-VALUE	Number to subtract from
value2	NUMERIC-VALUE	Number to subtract

Here is a basic document we can apply SUBTRACT on:

{
  "value": 1, 
  "value_two": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    value_two,
    SUBTRACT(value, value_two) AS subtraction,
    SUBTRACT(2, value_two) AS subtraction2

Resulting document:

{
  "value": 1,
  "value_two": 2,
  "subtraction": -1,
  "subtraction2": 0  
}

---

MULTIPLY

MULTIPLY(<value1>, <value2>, ...)

Parameter	Type	Description
valueN	NUMERIC-VALUE	Number to multiply

Here is a basic document we can apply MULTIPLY on:

{
  "value": 3, 
  "value_two": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    value_two,
    MULTIPLY(value, value_two) AS multi,
    MULTIPLY(2, value_two) AS multi2,
    MULTIPLY(2, value_two, 3) AS multi3

Resulting document:

{
  "value": 3,
  "value_two": 2,
  "multi": 6,
  "multi2": 4,
  "multi3": 12
}

---

DIVIDE

DIVIDE(<dividend>, <divisor>)

Parameter	Type	Description
dividend	NUMERIC-VALUE	Number to divide
divisor	NUMERIC-VALUE	Number by which to divide

Here is a basic document we can apply DIVIDE on:

{
  "value": 1, 
  "value_two": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    value_two,
    DIVIDE(value, value_two) AS div,
    DIVIDE(2, value_two) AS div2

Resulting document:

{
  "value": 1,
  "value_two": 2,
  "div": 0.5,
  "div2": 1  
}

---

MOD

MOD(<dividend>, <divisor>)

Parameter	Type	Description
dividend	NUMERIC-VALUE	Number to divide
divisor	NUMERIC-VALUE	Number by which to divide

Here is a basic document we can apply MOD on:

{
  "value": 1, 
  "value_two": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    value,
    value_two,
    MOD(value, value_two) AS mod1,
    MOD(5, value_two) AS mod2

Resulting document:

{
  "value": 1,
  "value_two": 2,
  "mod1": 1,
  "mod2": 1  
}

---

SQRT

SQRT(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE	Value to get square root of

Here is a basic document we can apply SQRT on:

{
  "value": 9
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT SQRT(value) AS value

Resulting document:

{
  "value": 3
}

---

TOLONG

TOLONG(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE / STRING-VALUE / DATE-VALUE / BOOLEAN-VALUE	Value to convert to long

Here is a basic document we can apply TOLONG on:

{
  "bool": true,
  "decimal": 9.99999,
  "date": {
    "$date": "2018-03-26T04:38:28.044Z"
  },
  "string": "11111"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT TOLONG(bool) AS bool_long,
  TOLONG(decimal) AS decimal_long,
  TOLONG(date) AS date_long,
  TOLONG(string) AS string_long

Resulting document:

{
  "bool_long": 1L,
  "decimal_long": 9L,
  "date_long": 1522039108044L,
  "string_long": 11111L,

}

---

ROUND

ROUND(<input-value>)

OR

ROUND(<input-value>, <place>)

Parameter	Type	Description
input-value	NUMERIC-VALUE	Value to round
place	INTEGER-VALUE	How many decimal places to round to

Here is a basic document we can apply ROUND on:

{
  "decimal1": 9.99999,
  "decimal2": 9.44444
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT round(decimal1) AS decimal1_round,
  round(decimal2) AS decimal2_round,
  round(decimal2, 3) AS round_with_place

Resulting document:

{
  "decimal1_round": 10,
  "decimal2_round": 9,
  "round_with_place": 9.444
}

---

TOINT

TOINT(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE / STRING-VALUE /BOOLEAN-VALUE	Value to convert to an integer

Here is a basic document we can apply TOINT on:

{
  "str": "3",
  "float": 3.5,
  "bool": true
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    TOINT(str) AS str_as_int,
    TOINT(float) AS float_as_int,
    TOINT(bool) AS bool_as_int

Resulting document:

{
  "str_as_int": 3,
  "float_as_int": 3,
  "bool_as_int": 1
}

---

SIZE

SIZE(<input-list>)

Parameter	Type	Description
input-list	LIST-VALUE	List of which to get size of

Here is a basic document we can apply SIZE on:

{
  "values": ["value1", "value2", "value3"]
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    values,
    SIZE(values) AS value_size

Resulting document:

{
  "values": ["value1", "value2", "value3"],
  "value_size": 3
}

---

---

FLOOR

FLOOR(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE	Value which to get floor of

Here is a basic document we can apply FLOOR on:

{
  "decimal": 9.99999
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT FLOOR(decimal) AS decimal_floor

Resulting document:

{
  "decimal_floor": 9
}

---

CEIL

CEIL(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE	Value which to get ceiling of

Here is a basic document we can apply CEIL on:

{
  "decimal": 9.99999
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT CEIL(decimal) AS decimal_ceil

Resulting document:

{
  "decimal_ceil": 10
}

STRLENGTH

STRLENGTH(<input-str>)

Parameter	Type	Description
input-str	STRING-VALUE	String of which to get length of

Here is a basic document we can apply SIZE on:

{
  "my_string": "my_str"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_string,
    LENGTH(my_string) AS my_string_len

Resulting document:

{
  "my_string": "my_str",
  "my_string_len": 6
}

---

YEAR

YEAR(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get year of

Here is a basic document we can apply YEAR on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    YEAR(my_date) AS my_year

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_year": 2019
}

---

MONTH

MONTH(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get month of

Here is a basic document we can apply MONTH on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    MONTH(my_date) AS my_month

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_month": 6
}

---

WEEK

WEEK(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get week of

Note that this uses zero based numbering.

Here is a basic document we can apply MONTH on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    WEEK(my_date) AS my_week

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_week": 24
}

---

DAYOFYEAR

DAYOFYEAR(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get day of year

Here is a basic document we can apply DAYOFYEAR on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    DAYOFYEAR(my_date) AS my_day

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_day": 173
}

---

DAYOFMONTH

DAYOFMONTH(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get day of month

Here is a basic document we can apply DAYOFYEAR on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    DAYOFMONTH(my_date) AS my_day

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_day": 22
}

---

DAYOFWEEK

DAYOFWEEK(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get day of week

Here is a basic document we can apply DAYOFWEEK on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    DAYOFWEEK(my_date) AS my_day

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_day": 7
}

---

HOUR

HOUR(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get hour of

Here is a basic document we can apply HOUR on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    HOUR(my_date) AS my_hour

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_hour": 10
}

---

MINUTE

MINUTE(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get minute of

Here is a basic document we can apply MINUTE on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    MINUTE(my_date) AS my_minute

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_minute": 15
}

---

SECOND

SECOND(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get second of

Here is a basic document we can apply SECOND on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    SECOND(my_date) AS my_second

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_second": 30
}

---

MILLISECOND

MILLISECOND(<input-date>)

Parameter	Type	Description
input-date	DATE-VALUE	Date of which to get millisecond of

Here is a basic document we can apply MILLISECOND on:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  }
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date,
    MILLISECOND(my_date) AS my_millisecond

Resulting document:

{
  "my_date": {
    "$date": "2019-06-22T10:15:30.321Z"
  },
  "my_millisecond": 321
}

---

TOBOOL

TOBOOL(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE / STRING-VALUE / DATE-VALUE / BOOLEAN-VALUE	Value to convert to boolean

Here is a basic document we can apply TOBOOL on:

{
  "decimal": 9.99999,
  "integer": 0,
  "date": {
    "$date": "2018-03-26T04:38:28.044Z"
  },
  "string": "false"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT TOBOOL(decimal) AS decimal_bool,
  TOBOOL(integer) AS integer_bool,
  TOBOOL(date) AS date_bool,
  TOBOOL(string) AS string_bool

Resulting document:

{
  "decimal_bool": true,
  "integer_bool": false,
  "date_bool": true,
  "string_bool": true
}

---

DATEFROMPARTS

DATEFROMPARTS(<year-int>, MONTH : <month-int>, DAY : <day-int>, HOUR : <hour-int>, MINUTE : <minute-int>, SECOND : <second-int>, MILLISECOND : <millisecond-int>, TIMEZONE : <timezone>)

Note that all parameters other than <year-int> are optional, can can specified in any order.

Parameter	Type	Description
year-int	INTEGER-VALUE	Year of the resulting date
month-int	INTEGER-VALUE	Month of the resulting date
day-int	INTEGER-VALUE	Day of the resulting date
hour-int	INTEGER-VALUE	Hour of the resulting date
minute-int	INTEGER-VALUE	Minute of the resulting date
second-int	INTEGER-VALUE	Second of the resulting date
millisecond-int	INTEGER-VALUE	Millisecond of the resulting date
timezone	STRING-VALUE	Timezone of the resulting date

Here is a basic document we can apply DATEFROMPARTS on:

{
  "my_date": ""
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    DATEFROMPARTS(2010, MONTH : 3, DAY : 10, TIMEZONE : '+04:45') AS my_date

Resulting document:

{
  "my_date": {
    "$date": "2010-03-09T19:15:00.000Z"
  }
}

---

DATEFROMSTRING

DATEFROMSTRING(<date-str>)

OR

DATEFROMSTRING(<date-str>, <format-str>)

Parameter	Type	Description
date-str	STRING-VALUE	String representing date to be converted to DATE
format-str	STRING-VALUE	Format to of <date-str>. Defaults to %Y-%m-%dT%H:%M:%S.%LZ if unspecified. You can see info on date formats here

Here is a basic document we can apply DATEFROMSTRING on:

{
  "my_date_str": "'10-25-2013'"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date_str,
    DATEFROMSTRING(my_date_str, '%m-%d-%Y') AS my_date

Resulting document:

{
  "my_date": {
    "my_date_str": "'10-25-2013'",
    "$date": "2013-10-25T00:00:00.000Z"
  }
}

Or without specifying a format:

SELECT DATEFROMSTRING('2020-02-08T12:10:40.787') AS my_date

Resulting document:

{
  "my_date": {
    "$date": "2020-02-08T12:10:40.787Z"
  }
}

---

DATEFROMSTRINGTZ

DATEFROMSTRINGTZ(<date-str>, <timezone>)

OR

DATEFROMSTRINGTZ(<date-str>, <timezone>, <format-str>)

Parameter	Type	Description
date-str	STRING-VALUE	String representing date to be converted to DATE
timezone	STRING-VALUE	Timezone of the resulting date
format-str	STRING-VALUE	Format of <date-str>. Defaults to %Y-%m-%dT%H:%M:%S.%LZ if unspecified. You can see info on date formats here

Here is a basic document we can apply DATEFROMSTRINGTZ on:

{
  "my_date_str": "'10-25-2013'"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    my_date_str,
    DATEFROMSTRINGTZ(my_date_str, '-0530', '%m-%d-%Y') AS my_date

Resulting document:

{
  "my_date": {
    "my_date_str": "'10-25-2013'",
    "$date": "2013-10-25T05:30:00Z"
  }
}

Or without specifying a format:

SELECT DATEFROMSTRINGTZ('2020-02-08T12:10:40.787', '-0530') AS my_date

Resulting document:

{
  "my_date": {
    "$date": "2020-02-08T17:40:40.787Z"
  }
}

---

TODATE

TODATE(<input-value>)

Parameter	Type	Description
input-value	NUMERIC-VALUE / STRING-VALUE	Value to convert to date

Here is a basic document we can apply TODATE on:

{
  "decimal": 1253372036000.50,
  "long": 1100000000000,
  "string_a": "2011-11-11",
  "string_b": "2011-11-11 11:00:06 +0500"
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT TODATE(decimal) AS decimal_date,
  TOBOOL(long) AS long_date,
  TOBOOL(string_a) AS string_a_date,
  TOBOOL(string_b) AS string_b_date

Resulting document:

{
  "decimal_date": {
    "$date": "2009-09-19T14:53:56Z"
  },
  "long_date": {
    "$date": "2004-11-09T11:33:20Z"
  },
  "string_a_date": {
    "$date": "2011-11-11T00:00:00Z"
  },
  "string_b_date": {
    "$date": "2011-11-11T06:00:06Z"
  }
}

---

APPEND

APPEND(<list1>, <list2>, ...)

Parameter	Type	Description
listN	LIST-VALUE	List to concatenate

Here is a basic document we can apply APPEND on:

{
  "values1": ["1", "2"],
  "values2": ["3", "4"]
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    values1,
    values2,
    APPEND(values1, values2, ["5"]) AS all_values

Resulting document:

{
  "values1": ["1", "2"],
  "values2": ["3", "4"],
  "all_values": ["1", "2", "3", "4", "5"]
}

---

SLICE

SLICE(<input-list>, <index>, <length>)

OR

SLICE(<input-list>, <length>)

Parameter	Type	Description
input-list	LIST-VALUE	List to slice
index	INTEGER-VALUE	0-base index from which to begin slice. If negative, counts back from end of list
length	INTEGER-VALUE	Length of slice to be returned. If index not specified, can be negative, and will slice from the end of list

Here is a basic document we can apply SLICE on:

{
  "all_values": ["0", "1", "2", "3"]
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    all_values,
    SLICE(all_values, 1, 2) AS small_values,
    SLICE(all_values, 2) AS some_values,
    SLICE(all_values, -2) AS last_values,
    SLICE(all_values, -2, 1) AS second_to_last_value

Resulting document:

{
  "all_values": ["0", "1", "2", "3"],
  "small_values": ["1", "2"],
  "some_values": ["0", "1"],
  "last_values": ["2", "3"],
  "second_to_last_value": ["2"]
}

---

MAP

MAP(<input-list> USING <handle> WITH <value>)

Parameter	Type	Description
input-list	LIST-VALUE	List to transform
handle	ATTRIBUTE	Temporary attribute which can be used to refer to each <input-list> element when inside the <value> expression
value	ANY	Transform to apply to each element of <input-list>. Can be a literal, or any other SELECT function

The identifier passed into the USING clause will be the handle for each list element when evaluating the WITH clause, which defines the transform Here is a basic document we can apply MAP on:

{
  "x": [0, 1, 2, 3],
  "y": 10
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    MAP(x USING elem WITH ADD(elem, y)) AS xy,
    MAP(["hello", "goodbye"] USING elem WITH 0) AS zeros,
    MAP(x USING elem WITH { 'key': elem }) AS docs

Resulting document:

{
  "xy": [10, 11, 12, 13],
  "zeros": [0, 0],
  "docs": [
    {
      "key": 0
    }, {
      "key": 1
    }, {
      "key": 2
    }, {
      "key": 3
    }
  ]
}

---

FILTER

FILTER(<input-list> USING <handle> WITH <condition>)

Parameter	Type	Description
input-list	LIST-VALUE	List to filter
handle	ATTRIBUTE	Temporary attribute which can be used to refer to each <input-list> element when inside the <condition> expression
condition	CONDITION	Condition that determines whether to keep a given element

Here is a basic document we can apply FILTER on:

{
  "x": [0, 1, 2, 3],
  "y": 2
}

SELECT portion for TQL or TQLFlow could look like this:

SELECT
    FILTER(x USING elem WITH elem GE y) AS filtered_x

Resulting document:

{
  "filtered_x": [2, 3]
}

---

DATE FORMATS

The following specifiers can be used inside a format string for DATETOSTRING and DATETOSTRINGTZ:

Specifiers	Description	Possible Values
%d	Day of Month (2 digits, zero padded)	01-31
%G	Year in ISO 8601 format	0000-9999
%H	Hour (2 digits, zero padded, 24-hour clock)	00-23
%j	Day of year (3 digits, zero padded)	001-366
%L	Millisecond (3 digits, zero padded)	000-999
%m	Month (2 digits, zero padded)	01-12
%M	Minute (2 digits, zero padded)	00-59
%S	Second (2 digits, zero padded)	00-60
%w	Day of week (1-Sunday, 7-Saturday)	1-7
%u	Day of week number in ISO 8601 format (1-Monday, 7-Sunday)	1-7
%U	Week of year (2 digits, zero padded)	00-53
%V	Week of Year in ISO 8601 format	01-53
%Y	Year (4 digits, zero padded)	0000-9999
%z	The timezone offset from UTC.	+/-[hh][mm]
%Z	The minutes offset from UTC as a number.	+/-mmm
%%	Percent Character as a Literal	%