JSON data type conversion

seekdb supports the CAST function to convert data of other types to JSON data and vice versa.

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

Other data type	CAST(other_type AS JSON)	CAST(JSON AS other_type)
JSON	No change	No change
UTF-8 character type (including UTF-8, UTF-8MB4, and ASCII)	The characters are converted to JSON values and validated.	The characters are serialized into UTF-8MB4 strings.
Other character set types	The characters are first converted to UTF-8MB4 encoding and then converted to UTF-8 characters.	The characters are first serialized into UTF-8MB4 strings and then converted to strings of the corresponding character set.
NULL	An empty JSON value is returned.	N/A
Other types	Only scalar values are converted to JSON values that contain only one 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 generated.

info

other_type specifies a data type other than JSON.

The following examples show how to convert data of other types to JSON data and vice versa.

SELECT CAST("123" AS JSON);

The preceding statement returns the following result:

+---------------------+
| CAST("123" AS JSON) |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.001 sec)

SELECT CAST(null AS JSON);

The preceding statement returns the following result:

+--------------------+
| CAST(null AS JSON) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.001 sec)

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');

SELECT * FROM tj1 ORDER BY CAST(JSON_EXTRACT(c1, '$.id') AS UNSIGNED);

The preceding statement returns the following result:

+-------------------------------+--------+
| c1                            | c2     |
+-------------------------------+--------+
| {"id": 16, "color": "orange"} | orange |
| {"id": 17, "color": "red"}    | apple  |
| {"id": 18, "color": "yellow"} | banana |
+-------------------------------+--------+
3 rows in set (0.003 sec)