You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
@@ -7,14 +7,40 @@ Any such value holds at runtime two pieces of information:
7
7
- the data type
8
8
- the data value
9
9
10
-
Values of `VARIANT` type can be created by casting any other value to a `VARIANT`: e.g.
11
-
`SELECT CAST(x AS VARIANT)`. Conversely, values of type `VARIANT` can be cast to any other data type
12
-
`SELECT CAST(variant AS INT)`. A cast of a value of type `VARIANT` to target type T
13
-
will compare the runtime type with T. If the types are identical, the
14
-
original value is returned. Otherwise the `CAST` returns `NULL`.
10
+
::: warning
15
11
16
-
Values of type `ARRAY`, `MAP`, and `ROW` type can be cast to `VARIANT`. `VARIANT` values
17
-
also offer the following operations:
12
+
Currently `VARIANT` values are not supported in table or output views;
13
+
they can only be used in intermediate computations.
14
+
15
+
:::
16
+
17
+
18
+
Values of `VARIANT` type can be created by casting any other value to
19
+
a `VARIANT`: e.g. `SELECT CAST(x AS VARIANT)`. Conversely, values of
20
+
type `VARIANT` can be cast to any other data type `SELECT CAST(variant
21
+
AS INT)`. A cast of a value of type `VARIANT` to target type T will
22
+
compare the runtime type with T. If the types are identical or there
23
+
is a natural conversion from the runtime type to T, the original value
24
+
is returned. Otherwise the `CAST` returns `NULL`.
25
+
26
+
::: warning
27
+
28
+
Remember that the `DECIMAL` type specified without precision is the
29
+
same as `DECIMAL(0)`, with no digits after the decimal point. When
30
+
you cast a `VARIANT` value to `DECIMAL` you should specify a precision
31
+
and scale good enough for the values that you expect in the data.
32
+
33
+
:::
34
+
35
+
Values of type `ARRAY` and `MAP` can be cast to `VARIANT`.
36
+
37
+
There exists a special value of `VARIANT` type called `null`. This
38
+
value is different from the SQL `NULL` value. It is used to implement
39
+
the JSON `null` value. An important difference is that two `VARIANT`
40
+
`null` values are equal, whereas `NULL` in SQL is not equal (or
41
+
different!) to anything.
42
+
43
+
`VARIANT` values also offer the following operations:
18
44
19
45
- indexing using array indexing notation `variant[index]`. If the `VARIANT` is
20
46
obtained from an `ARRAY` value, the indexing operation returns a `VARIANT` whose value element
@@ -30,11 +56,7 @@ also offer the following operations:
30
56
is subject to the capitalization rules of the SQL dialect, so for correct
31
57
operation the field may need to be quoted: `variant."field"`
32
58
33
-
The runtime types do not need to match exactly the compile-time types.
34
-
As a compiler front-end, Calcite does not mandate exactly how the runtime types
35
-
are represented. Calcite does include one particular implementation in
36
-
Java runtime, which is used for testing. In this representation
37
-
the runtime types are represented as follows:
59
+
The runtime types do not match exactly the compile-time types:
38
60
39
61
- The scalar types do not include information about precision and scale. Thus all `DECIMAL`
40
62
compile-time types are represented by a single run-time type.
@@ -43,20 +65,64 @@ the runtime types are represented as follows:
43
65
-`FLOAT` and `DOUBLE` are both represented by the same runtime type.
44
66
- All "short interval" types (from days to seconds) are represented by a single type.
45
67
- All "long interval" types (from years to months) are represented by a single type.
46
-
- Generic types such as `INT ARRAY`, `MULTISET`, and `MAP` do carry runtime
68
+
- Generic types such as `INT ARRAY`, `MULTISET`, and `MAP` do not carry runtime
47
69
information about the element types
48
-
- The `ROW` type does have information about all field types (currently not yet supported)
49
70
50
71
## Functions that operate on `VARIANT` values
51
72
52
73
| Function | Description |
53
74
|`VARIANTNULL()`| Can be used to create an instance of the `VARIANT``null` value. |
54
-
|`TYPEOF(`_variant_`)`| Argument must be a `VARIANT` value. Returns the runtime type of the value |
75
+
|`TYPEOF(`_variant_`)`| Argument must be a `VARIANT` value. Returns a string describing the runtime type of the value |
55
76
|`PARSE_JSON(`_string_`)`| Parses a string that represents a JSON value, returns a `VARIANT` object, or `NULL` if parsing fails |
56
-
|`UNPARSE_JSON(`_variant_`)`| Returns a string that represents the serialization of a `VARIANT` value. If the value cannot be represented as JSON, the result is `NULL`|
77
+
|`UNPARSE_JSON(`_variant_`)`| Argument must be a `VARIANT` value. Returns a string that represents the serialization of a `VARIANT` value. If the value cannot be represented as JSON, the result is `NULL`|
78
+
79
+
### `PARSE_JSON`
80
+
81
+
`PARSE_JSON` converts a JSON value as follows:
82
+
83
+
- JSON `null` is converted to a `VARIANT``null` value (not a SQL `NULL`!); see above the description of `VARIANT``null`
84
+
- JSON Booleans are converted to `BOOLEAN` values (wrapped in `VARIANT` values)
85
+
- JSON numbers are converted to `DECIMAL` values (wrapped in `VARIANT` values)
86
+
- JSON strings are converted to `VARCHAR` values (wrapped in `VARIANT` values)
87
+
- JSON arrays are converted to `VARIANT ARRAY` values (wrapped in `VARIANT` values). Each array element is a `VARIANT`
88
+
- JSON objects are converted to `MAP<VARIANT, VARIANT>` values (wrapped in `VARIANT` values). Each key and each value is a `VARIANT`
89
+
90
+
For example, `PARSE_JSON("{"a": 1, "b": [2, 3.3, null]}")` generates the same SQL value that would be generated by the following code:
91
+
92
+
```sql
93
+
SELECT CAST(
94
+
MAP[
95
+
CAST('a'AS VARIANT), CAST(1.0AS VARIANT),
96
+
CAST('b'AS VARIANT), CAST(ARRAY[
97
+
CAST(2.0AS VARIANT),
98
+
CAST(3.3AS VARIANT),
99
+
VARIANTNULL()
100
+
] AS VARIANT)
101
+
] AS VARIANT)
102
+
```
57
103
58
-
Here are some examples using `VARIANT` and `JSON` values:
59
104
105
+
### `UNPARSE_JSON`
106
+
107
+
`PARSE_JSON` converts a `VARIANT` value to a `VARCHAR`. The
108
+
assumption is that the `VARIANT` was has the structure as produced by
109
+
the `PARSE_JSON` function:
110
+
111
+
- the `VARIANT``null` value is converted to the string `null`
112
+
- a `VARIANT` wrapping a Boolean value is converted to the respective Boolean string `true` or `false`
113
+
- a `VARIANT` wrapping any numeric value (`DECIMAL`, `TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE`, `DECIMAL`) is converted to the string representation of the value as produced using a `CAST(value AS VARCHAR)`
114
+
- a `VARIANT` wrapping a `VARCHAR` value is converted to a string with double quotes, and with escape sequences, as mandated by the JSON grammar
115
+
- a `VARIANT` wrapping an `ARRAY` with elements of any type is converted to a JSON array, and the elements are recursively converted
116
+
- a `VARIANT` wrapping a `MAP` whose keys have any SQL `CHAR` type, or `VARIANT` values wrapping `CHAR` values will generate a JSON object, by recursively converting each key-value pair.
117
+
- any other data value is a conversion error, and causes the `UNPARSE_JSON` function to produce a `NULL` result
118
+
119
+
## Examples
120
+
121
+
Here are some simple SQL query examples using `VARIANT` and JSON
122
+
values and the expected output values. (Note that these examples
123
+
cannot be executed directly, since they involve no views.)
124
+
125
+
```sql
60
126
SELECT CAST(1AS VARIANT)
61
127
1
62
128
@@ -128,7 +194,7 @@ null
128
194
SELECT CAST(ARRAY[1,2,3] AS VARIANT)[1]
129
195
1
130
196
131
-
-- Acessing items in a VARIANT array returns VARIANT values,
197
+
--Accessing items in a VARIANT array returns VARIANT values,
132
198
-- even if the array itself does not contain VARIANT values
0 commit comments