Variant data type

The variant data type can contain a value of any other data type.

Inserting variant data

To insert variant data directly, use SELECT ... INSERT INTO .... The following example shows how to insert JSON-formatted data into a variant value:

CREATE TABLE variant_insert (v variant);
SELECT parse_json('{"key1": "value1", "key2": "value2"}') INSERT INTO variant_insert;
FROM variant_insert;

+-----------------------------------+
| v                                 |
+-----------------------------------+
| {"key1":'value1',"key2":'value2'} |
+-----------------------------------+

Alternatively, you can use the variant literal syntax:

DELETE FROM variant_insert;
SELECT {"key3": 'value3', "key4": 'value4'} INSERT INTO variant_insert;
FROM variant_insert;

+-----------------------------------+
| v                                 |
+-----------------------------------+
| {"key3":'value3',"key4":'value4'} |
+-----------------------------------+

Using variant values

To convert a value to or from the variant data type, you can explicitly cast using the CAST function or the :: operator (e.g. <expr>::variant). For details, see data type conversion.

To understand the variant data, consider the following example:

CREATE TABLE vartab (n int, v variant);
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 with their value type:

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    |
+---+------------------------------+-----------+

You can extract a field from an OBJECT, or an element from an ARRAY, using the [] operator. For example:

FROM vartab
SELECT n, v['x'] AS x, v[1] AS a1,
WHERE x IS NOT NULL AND a1 IS NOT NULL
ORDER BY n

+---+-------+------+
| n | x     | a1   |
+---+-------+------+
| 8 | NULL  | 12   |
| 9 | 'abc' | NULL |
+---+-------+------+

Variant data has a total ordering, which means you can use the ORDER BY clause to sort the data.

FROM vartab WHERE v IS NOT NULL ORDER BY v DESC;

+---+------------------------------+
| n | v                            |
+---+------------------------------+
| 9 | {"x":'abc',"y":false,"z":10} |
| 8 | [-1,12,289,2188,false]       |
| 3 | true                         |
| 6 | 191.2                        |
| 5 | 123.12                       |
| 4 | -17                          |
| 7 | 'Om ara pa ca na dhih'       |
| 1 | null                         |
+---+------------------------------+

Between different variant types, the order is as follows:

Object > Array > Timestamp > Interval > Boolean > Number > String > Null

Variant data type

Inserting variant data

Using variant values

Related content