Convert JSON data types

seekdb supports the CAST function for converting between JSON and other data types.

The following table describes the conversion rules for JSON data types.

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 data is serialized into utf8mb4 strings.
Other character sets	First converted to utf8mb4 encoding, then processed as UTF-8 character type.	First serialized into utf8mb4-encoded strings, then converted to the corresponding character set.
NULL	An empty JSON value is returned.	Not applicable.
Other types	Only scalar values are converted to JSON values containing that single value.	If the JSON value contains only one scalar value that matches the target type, it is converted to the corresponding type; otherwise, NULL is returned and a warning is issued.

info

other_type specifies a data type other than JSON.

Here are some conversion 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