Type Functions
- reference
Type functions perform operations that check or convert expressions.
If any arguments to any of the following functions are MISSING then the result is also MISSING (i.e.
no result is returned).
Similarly, if any of the arguments passed to the functions are NULL or are of the wrong type (e.g.
an integer instead of a string), then NULL is returned as the result.
|
ISARRAY(expression)
Examples
SELECT ISARRAY(true) AS `boolean`,
ISARRAY(MISSING) AS `missing`,
ISARRAY(NULL) AS `null`,
ISARRAY(123) AS `number`,
ISARRAY("hello world") AS `string`,
ISARRAY([1, 2, 3]) AS `array`,
ISARRAY({"hello": "world"}) AS `object`;
[
{
"array": true,
"boolean": false,
"null": null,
"number": false,
"object": false,
"string": false
}
]
ISATOM(expression)
Return Value
Returns True if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or false.
Examples
SELECT ISATOM(true) AS `boolean`,
ISATOM(MISSING) AS `missing`,
ISATOM(NULL) AS `null`,
ISATOM(123) AS `number`,
ISATOM("hello world") AS `string`,
ISATOM([1, 2, 3]) AS `array`,
ISATOM({"hello": "world"}) AS `object`;
[
{
"array": false,
"boolean": true,
"null": null,
"number": true,
"object": false,
"string": true
}
]
ISBOOLEAN(expression)
Examples
SELECT ISBOOLEAN(true) AS `boolean`,
ISBOOLEAN(MISSING) AS `missing`,
ISBOOLEAN(NULL) AS `null`,
ISBOOLEAN(123) AS `number`,
ISBOOLEAN("hello world") AS `string`,
ISBOOLEAN([1, 2, 3]) AS `array`,
ISBOOLEAN({"hello": "world"}) AS `object`;
[
{
"array": false,
"boolean": true,
"null": null,
"number": false,
"object": false,
"string": false
}
]
ISNUMBER(expression)
Examples
SELECT ISNUMBER(true) AS `boolean`,
ISNUMBER(MISSING) AS `missing`,
ISNUMBER(NULL) AS `null`,
ISNUMBER(123) AS `number`,
ISNUMBER("hello world") AS `string`,
ISNUMBER([1, 2, 3]) AS `array`,
ISNUMBER({"hello": "world"}) AS `object`;
[
{
"array": false,
"boolean": false,
"null": null,
"number": true,
"object": false,
"string": false
}
]
ISOBJECT(expression)
Examples
SELECT ISOBJECT(true) AS `boolean`,
ISOBJECT(MISSING) AS `missing`,
ISOBJECT(NULL) AS `null`,
ISOBJECT(123) AS `number`,
ISOBJECT("hello world") AS `string`,
ISOBJECT([1, 2, 3]) AS `array`,
ISOBJECT({"hello": "world"}) AS `object`;
[
{
"array": false,
"boolean": false,
"null": null,
"number": false,
"object": true,
"string": false
}
]
ISSTRING(expression)
Examples
SELECT ISSTRING(true) AS `boolean`,
ISSTRING(MISSING) AS `missing`,
ISSTRING(NULL) AS `null`,
ISSTRING(123) AS `number`,
ISSTRING("hello world") AS `string`,
ISSTRING([1, 2, 3]) AS `array`,
ISSTRING({"hello": "world"}) AS `object`;
[
{
"array": false,
"boolean": false,
"null": null,
"number": false,
"object": false,
"string": true
}
]
TYPE(expression)
Return Value
Returns one of the following strings, based on the value of expression:
-
"missing"
-
"null"
-
"boolean"
-
"number"
-
"string"
-
"array"
-
"object"
-
"binary"
Examples
SELECT TYPE(true) AS `boolean`,
TYPE(MISSING) AS `missing`,
TYPE(NULL) AS `null`,
TYPE(123) AS `number`,
TYPE("hello world") AS `string`,
TYPE([1, 2, 3]) AS `array`,
TYPE({"hello": "world"}) AS `object`;
[
{
"array": "array",
"boolean": "boolean",
"missing": "missing",
"null": "null",
"number": "number",
"object": "object",
"string": "string"
}
]
TOARRAY(expression)
Return Value
Returns one of the following strings, based on the value of expression:
Returns array as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
Arrays are themselves.
-
All other values are wrapped in an array.
Examples
SELECT TOARRAY(true) AS `boolean`,
TOARRAY(MISSING) AS `missing`,
TOARRAY(NULL) AS `null`,
TOARRAY(123) AS `number`,
TOARRAY("hello world") AS `string`,
TOARRAY([1, 2, 3]) AS `array`,
TOARRAY({"hello": "world"}) AS `object`;
[
{
"array": [
1,
2,
3
],
"boolean": [
true
],
"null": null,
"number": [
123
],
"object": [
{
"hello": "world"
}
],
"string": [
"hello world"
]
}
]
TOATOM(expression)
Return Value
Returns atomic value as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
Arrays of length 1 are the result of TOATOM() on their single element.
-
Objects of length 1 are the result of TOATOM() on their single value.
-
Booleans, numbers, and strings are themselves.
-
All other values are NULL.
Examples
SELECT TOATOM(true) AS `boolean`,
TOATOM(MISSING) AS `missing`,
TOATOM(NULL) AS `null`,
TOATOM(123) AS `number`,
TOATOM("hello world") AS `string`,
TOATOM([1, 2, 3]) AS `array`,
TOATOM({"hello": "world"}) AS `object`;
[
{
"array": null,
"boolean": true,
"null": null,
"number": 123,
"object": "world",
"string": "hello world"
}
]
TOBOOLEAN(expression)
Return Value
Returns Boolean as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
False is false.
-
Numbers +0, -0, and NaN are false.
-
Empty strings, arrays, and objects are false.
-
All other values are true.
Examples
SELECT TOBOOLEAN(true) AS `boolean`,
TOBOOLEAN(MISSING) AS `missing`,
TOBOOLEAN(NULL) AS `null`,
TOBOOLEAN(123) AS `number`,
TOBOOLEAN("hello world") AS `string`,
TOBOOLEAN([1, 2, 3]) AS `array`,
TOBOOLEAN({"hello": "world"}) AS `object`;
[
{
"array": true,
"boolean": true,
"null": null,
"number": true,
"object": true,
"string": true
}
]
TONUMBER(expression)
Return Value
Returns number as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
False is 0.
-
True is 1.
-
Numbers are themselves.
-
Strings that parse as numbers are those numbers.
-
All other values are NULL.
Examples
SELECT TONUMBER(true) AS `boolean`,
TONUMBER(MISSING) AS `missing`,
TONUMBER(NULL) AS `null`,
TONUMBER(123) AS `number`,
TONUMBER("hello world") AS `string`,
TONUMBER([1, 2, 3]) AS `array`,
TONUMBER({"hello": "world"}) AS `object`;
[
{
"array": null,
"boolean": 1,
"null": null,
"number": 123,
"object": null,
"string": null
}
]
TOOBJECT(expression)
Return Value
Returns object as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
Objects are themselves.
-
All other values are the empty object.
Examples
SELECT TOOBJECT(true) AS `boolean`,
TOOBJECT(MISSING) AS `missing`,
TOOBJECT(NULL) AS `null`,
TOOBJECT(123) AS `number`,
TOOBJECT("hello world") AS `string`,
TOOBJECT([1, 2, 3]) AS `array`,
TOOBJECT({"hello": "world"}) AS `object`;
[
{
"array": {},
"boolean": {},
"null": null,
"number": {},
"object": {
"hello": "world"
},
"string": {}
}
]
TOSTRING(expression)
Return Value
Returns string as follows:
-
MISSING is MISSING.
-
NULL is NULL.
-
False is "false".
-
True is "true".
-
Numbers are their string representation.
-
Strings are themselves.
-
All other values are NULL.
Examples
SELECT TOSTRING(true) AS `boolean`,
TOSTRING(MISSING) AS `missing`,
TOSTRING(NULL) AS `null`,
TOSTRING(123) AS `number`,
TOSTRING("hello world") AS `string`,
TOSTRING([1, 2, 3]) AS `array`,
TOSTRING({"hello": "world"}) AS `object`;
[
{
"array": null,
"boolean": "true",
"null": null,
"number": "123",
"object": null,
"string": "hello world"
}
]