OceanBase AI Database supports using the CAST function to convert between other data types and the JSON data type.
The conversion rules for the JSON data type are shown in the following table.
Other data types |
CAST(other_type AS JSON) |
CAST(JSON AS other_type) |
|---|---|---|
| JSON | No change | No change |
| UTF-8 character types (including utf8mb4, utf8, and ascii) | Characters are converted to JSON values and validated for legality. | Serializes to a UTF-8MB4 string. |
| Other character set types | First, it is converted to the utf8mb4 character encoding, and then the conversion is performed according to the description of the utf8 character type. | First, it serializes the data into a UTF-8MB4-encoded string, and then converts it into a string of the corresponding character set. |
| NULL | Return an empty JSON value. | N/A |
| Other Types | Only a single scalar value can be converted into a JSON value that represents a single value. | If a JSON value contains only one scalar value that matches the target type, it is converted to the corresponding type. Otherwise, NULL is returned and an alert is triggered. |
Note
other_type specifies data types other than JSON.
The following are some examples of conversions.
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