typeof

Returns the type of value stored in an ANY column or an ARRAY column.

Syntax

typeof( <expr> )

Arguments

<expr>

The argument can be a column name or a general expression of type ANY or ARRAY.

Returns

If the argument is of type ANY, returns a string that contains the data type of the input expression.

If the argument is of type ARRAY, returns an array that contains the data type of the elements in the array.

Examples

Get the type of a value in an ANY column

Create and populate a table. Note that the INSERT statement uses the parse_json function.

CREATE TABLE vartab (n int, v any);
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} ')
SELECT $0 AS n, parse_json($1) AS v
INSERT INTO vartab;

Query the data:

FROM vartab
SELECT n, v, typeof(v)
ORDER BY n;

+---+------------------------------+-----------+
| n | v                            | typeof(v) |
+---+------------------------------+-----------+
| 1 | NULL                         | null      |
| 2 | NULL                         | null      |
| 3 | true                         | boolean   |
| 4 | -17                          | int       |
| 5 | 123.12                       | float     |
| 6 | 191.2                        | float     |
| 7 | "Om ara pa ca na dhih"       | string    |
| 8 | [-1,12,289,2188,false]       | array     |
| 9 | {"x":"abc","y":false,"z":10} | object    |
+---+------------------------------+-----------+

Note that PARSE_JSON('null') returns a NULL value.

Get the element type of an array

This example shows how to get the type of elements in an array:

SELECT
    typeof([1, 2, 3]),
    typeof(['a', 'b', 'c']),
    typeof([1, 'a', true]);

+-------------------+-------------------------+----------------------------+
| typeof([1, 2, 3]) | typeof(['a', 'b', 'c']) | typeof([1, 'a', TRUE])     |
+-------------------+-------------------------+----------------------------+
| ["int"]           | ["string"]              | ["int","string","boolean"] |
+-------------------+-------------------------+----------------------------+