AS_<object_type>¶

General usage notes¶

• If the type of value in the VARIANT argument does not match the output value, then NULL is returned. For example, if AS_DATE() is passed a VARIANT value that doesn’t contain a DATE, then NULL is returned.

• If the input is NULL, the output is NULL.

Examples¶

Compute the average of all numeric values from a variant type column in the vartab table:

First, create the table and load data into it.

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;

Copy

Second, show the data types of the values (some of which are numeric and some of which are not):

select n, as_real(v), typeof(v)
    from vartab
    order by n;
+---+------------+------------+
| N | AS_REAL(V) | TYPEOF(V)  |
|---+------------+------------|
| 1 |       NULL | NULL_VALUE |
| 2 |       NULL | NULL       |
| 3 |       NULL | BOOLEAN    |
| 4 |     -17    | INTEGER    |
| 5 |     123.12 | DECIMAL    |
| 6 |     191.2  | DOUBLE     |
| 7 |       NULL | VARCHAR    |
| 8 |       NULL | ARRAY      |
| 9 |       NULL | OBJECT     |
+---+------------+------------+

Copy

Third, compute the average of all numeric values from the VARIANT column:

select avg(as_real(v)) from vartab;
+-----------------+
| AVG(AS_REAL(V)) |
|-----------------|
|    99.106666667 |
+-----------------+

Copy