- Categories:
Semi-structured and structured data functions (Type Predicates)
IS_ARRAYÂļ
Returns TRUE if its VARIANT argument contains an ARRAY value.
- See also:
SyntaxÂļ
IS_ARRAY( <variant_expr> )
ArgumentsÂļ
variant_expr
An expression that evaluates to a value of type VARIANT.
ReturnsÂļ
Returns a BOOLEAN value or NULL.
Returns TRUE if the VARIANT value contains an ARRAY value. Otherwise, returns FALSE.
If the input is NULL, returns NULL without reporting an error.
Usage notesÂļ
This function doesnât support a structured type as an input argument.
ExamplesÂļ
The following examples use the IS_ARRAY function.
Use the IS_ARRAY function in a WHERE clauseÂļ
Create and fill the vartab
table. The INSERT statement uses the PARSE_JSON function to insert
VARIANT values in the v
column of the table.
CREATE OR REPLACE TABLE vartab (n NUMBER(2), v VARIANT);
INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2) AS v
FROM VALUES (1, 'null'),
(2, null),
(3, 'true'),
(4, '-17'),
(5, '123.12'),
(6, '1.912e2'),
(7, '"Om ara pa ca na dhih" '),
(8, '[-1, 12, 289, 2188, false,]'),
(9, '{ "x" : "abc", "y" : false, "z": 10} ')
AS vals;
Query the data. The query uses the TYPEOF function to show the data types of the values stored in the VARIANT column.
SELECT n, v, TYPEOF(v)
FROM vartab
ORDER BY n;
+---+------------------------+------------+
| 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 | |
| | } | |
+---+------------------------+------------+
Show the ARRAY values in the data by using the IS_ARRAY function in a WHERE clause:
SELECT * FROM vartab WHERE IS_ARRAY(v);
+---+-------------+
| N | V |
|---+-------------|
| 8 | [ |
| | -1, |
| | 12, |
| | 289, |
| | 2188, |
| | false, |
| | undefined |
| | ] |
+---+-------------+
Use the IS_ARRAY function in a SELECT listÂļ
Create and fill the multiple_types
table. The INSERT statement uses the TO_VARIANT function to insert
VARIANT values in the columns.
CREATE OR REPLACE TABLE multiple_types (
array1 VARIANT,
array2 VARIANT,
boolean1 VARIANT,
varchar1 VARIANT,
varchar2 VARIANT,
decimal1 VARIANT,
double1 VARIANT,
integer1 VARIANT,
object1 VARIANT);
INSERT INTO multiple_types
(array1, array2, boolean1, varchar1, varchar2,
decimal1, double1, integer1, object1)
SELECT
TO_VARIANT(TO_ARRAY('Example')),
TO_VARIANT(ARRAY_CONSTRUCT('Array-like', 'example')),
TO_VARIANT(TRUE),
TO_VARIANT('X'),
TO_VARIANT('I am a real character'),
TO_VARIANT(1.23::DECIMAL(6, 3)),
TO_VARIANT(3.21::DOUBLE),
TO_VARIANT(15),
TO_VARIANT(TO_OBJECT(PARSE_JSON('{"Tree": "Pine"}')));
Query the data using the TYPEOF function to show the data types of the values stored in the VARIANT values.
SELECT TYPEOF(array1),
TYPEOF(array2),
TYPEOF(boolean1),
TYPEOF(varchar1),
TYPEOF(varchar2),
TYPEOF(decimal1),
TYPEOF(double1),
TYPEOF(integer1),
TYPEOF(object1)
FROM multiple_types;
+----------------+----------------+------------------+------------------+------------------+------------------+-----------------+------------------+-----------------+
| TYPEOF(ARRAY1) | TYPEOF(ARRAY2) | TYPEOF(BOOLEAN1) | TYPEOF(VARCHAR1) | TYPEOF(VARCHAR2) | TYPEOF(DECIMAL1) | TYPEOF(DOUBLE1) | TYPEOF(INTEGER1) | TYPEOF(OBJECT1) |
|----------------+----------------+------------------+------------------+------------------+------------------+-----------------+------------------+-----------------|
| ARRAY | ARRAY | BOOLEAN | VARCHAR | VARCHAR | DECIMAL | DOUBLE | INTEGER | OBJECT |
+----------------+----------------+------------------+------------------+------------------+------------------+-----------------+------------------+-----------------+
Show whether a column contains ARRAY values in the data by using the IS_ARRAY function in a SELECT list:
SELECT IS_ARRAY(array1),
IS_ARRAY(array2),
IS_ARRAY(boolean1)
FROM multiple_types;
+------------------+------------------+--------------------+
| IS_ARRAY(ARRAY1) | IS_ARRAY(ARRAY2) | IS_ARRAY(BOOLEAN1) |
|------------------+------------------+--------------------|
| True | True | False |
+------------------+------------------+--------------------+