OceanBase Database allows you to perform a conversion between JSON data and other data types by calling the CAST() function.
The following table describes the JSON data conversion rules.
| Data type | CAST(other_type AS JSON) | CAST(JSON AS other_type) |
|---|---|---|
| JSON | No changes | No changes |
| utf8 characters (utf8mb4, utf8, and ascii) | Converts characters to JSON values and checks the validity of the JSON values. | Serializes JSON values into utf8mb4 strings. |
| Other character set types | Converts characters to utf8mb4 characters and then to JSON values based on the description for the utf8 characters. | Serializes JSON values into utf8mb4 strings and then to strings of the corresponding character set. |
| NULL | Returns a JSON null. | N/A |
| Other types | Converts only individual scalar values to individual JSON values. | If the JSON value to be converted is only a scalar value and the value type is the same as the data type to be converted to, the JSON value is converted to the corresponding type. Otherwise, NULL is returned and an alert is generated. |
Note
other_type specifies a data type other than JSON.
Here are some examples:
obclient> SELECT CAST("123" AS JSON);
+---------------------+
| CAST("123" AS JSON) |
+---------------------+
| 123 |
+---------------------+
1 row in set
obclient> SELECT CAST(null AS JSON);
+--------------------+
| CAST(null AS JSON) |
+--------------------+
| NULL |
+--------------------+
1 row in set
CREATE TABLE tj1 (c1 JSON,c2 VARCHAR(20));
INSERT INTO tj1 VALUES ('{"id": 17, "color": "red"}','apple'),('{"id": 18, "color": "yellow"}', 'banana'),('{"id": 16, "color": "orange"}','orange');
obclient> SELECT * FROM tj1 ORDER BY CAST(JSON_EXTRACT(c1, '$.id') AS UNSIGNED);
+-------------------------------+--------+
| c1 | c2 |
+-------------------------------+--------+
| {"id": 16, "color": "orange"} | orange |
| {"id": 17, "color": "red"} | apple |
| {"id": 18, "color": "yellow"} | banana |
+-------------------------------+--------+
3 rows in set