OceanBase Database allows you to use the CAST function to convert data of other types to JSON data and vice versa.
The following table describes the conversion rules of JSON data.
| 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) | The characters are converted to JSON values and validated. | The characters are serialized into a UTF-8MB4 string. |
| Other character sets | The characters are first converted to UTF-8MB4 encoding and then converted to JSON values. | The characters are first serialized into a UTF-8MB4 string and then converted to the corresponding character set. |
| NULL | An empty JSON value is returned. | N/A |
| Other types | Only scalar values are converted to JSON values. | If a JSON value contains only a scalar value that matches the target type, it 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.
The following examples show how to convert data types to JSON.
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