Categories:: Semi-structured and structured data functions (Type Predicates)

IS_<object_type>¶

This family of functions serves as Boolean predicates that can be used to determine the data type of a value stored in a VARIANT column:

See also:: AS_<object_type> , TYPEOF

General usage notes¶

All the functions are unary, taking a VARIANT expression as the only argument.
All the functions return FALSE if the input is SQL NULL or the VARIANT expression contains null.

Examples¶

These examples use the vartab table (created in the PARSE_JSON examples):

select n, v, typeof(v) from vartab;

+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+

Copy

Count all rows in vartab table where the VARIANT column v contains a string value:

select count(*) from vartab where is_char(v);

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Copy

Select rows in vartab table where the VARIANT column v contains the specified data type:

select * from vartab where is_null_value(v);

+---+------+
| N | V    |
|---+------|
| 1 | null |
+---+------+

select * from vartab where is_boolean(v);

+---+------+
| N | V    |
|---+------|
| 3 | true |
+---+------+

select * from vartab where is_integer(v);

+---+-----+
| N | V   |
|---+-----|
| 4 | -17 |
+---+-----+

select * from vartab where is_decimal(v);

+---+--------+
| N | V      |
|---+--------|
| 4 | -17    |
| 5 | 123.12 |
+---+--------+

select * from vartab where is_double(v);

+---+-----------------------+
| N | V                     |
|---+-----------------------|
| 4 | -17                   |
| 5 | 123.12                |
| 6 | 1.912000000000000e+02 |
+---+-----------------------+

select * from vartab where is_varchar(v);

+---+------------------------+
| N | V                      |
|---+------------------------|
| 7 | "Om ara pa ca na dhih" |
+---+------------------------+

select * from vartab where is_array(v);

+---+-------------+
| N | V           |
|---+-------------|
| 8 | [           |
|   |   -1,       |
|   |   12,       |
|   |   289,      |
|   |   2188,     |
|   |   false,    |
|   |   undefined |
|   | ]           |
+---+-------------+

select * from vartab where is_object(v);

+---+---------------+
| N | V             |
|---+---------------|
| 9 | {             |
|   |   "x": "abc", |
|   |   "y": false, |
|   |   "z": 10     |
|   | }             |
+---+---------------+

Copy