Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ M-compatible Mode/ Data Types/ JSON
Updated on 2025-06-30 GMT+08:00
View PDF
Share

JSON

Table 1 JSON Data Type

Data Type

Differences Compared with MySQL

JSON

GaussDB supports the JSON data type. Compared with MySQL, GaussDB has the following differences in specifications:

  • Value range:

    In MySQL, the maximum size of the JSON data type is 4 GB. However, in GaussDB, the maximum size of the JSON data type is 1 GB – 512 bytes, and the maximum number of key-value pairs of an object and the maximum number of elements in an array are also less than those in MySQL.

  • Difference in collation:

    In MySQL, when the collation function is used to separately query columns of the JSON type, the returned collation is BINARY. However, utf8mb4_bin is returned in GaussDB. In other scenarios, utf8mb4_bin is used, which is the same as that of MySQL.

  • The differences in behavior when ORDER BY is performed on non-scalar JSON types are as follows:

    MySQL has no rule for using ORDER BY to sort non-scalar JSON data and does not support this operation. It reports an alarm for attempts of sorting non-scalar JSON data.

    In GaussDB, non-scalar JSON data can be sorted and arranged according to specific collation rules.

    • Compare the JSON data types (OPAQUE > TIMESTAMP = DATETIME > TIME > DATE > BOOL > array & > object & > string type > DOUBLE = UINT = INT = DECIMAL > NULL). If the results are the same, compare the contents.
    • Compare values between scalars. For OPAQUE, compare types first (TYPE_STRING > TYPE_VAR_STRING > TYPE_BLOB > TYPE_BIT > TYPE_VARCHAR > TYPE_YEAR). If the types are the same, compare each byte.
    • Compare the size of arrays. The size of arrays can be compared by comparing the number of the elements between arrays. The array with more elements is larger. When the number of elements are the same, compare the lengths of elements. If the lengths are the same, compare each key-value pair in sequence. Compare the key first and then the value.
  • In GaussDB, the BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY, BIT, and YEAR types are converted to the JSON type. The result is different from that in MySQL.

    Example:

    -- GaussDB
m_db=# CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
CREATE TABLE
m_db=# INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
INSERT 0 1
m_db=# SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
      CAST       |  CAST  | CAST  |               CAST                |                 CAST                 |               CAST                | CAST |  CAST  
-----------------+--------+-------+-----------------------------------+--------------------------------------+-----------------------------------+------+--------
 "[1, \"json\"]" | "true" | "abc" | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "[1, \"json\"]                     " | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "20" | "2020"
(1 row)
m_db=# DROP TABLE test_blob;
DROP TABLE

-- MySQL
mysql> CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
| CAST(c1 AS JSON)                  | CAST(c2 AS JSON)          | CAST(c3 AS JSON)      | CAST(c4 AS JSON)                                      | CAST(c5 AS JSON)                                              | CAST(c6 AS JSON)                                     | CAST(c7 AS JSON)             | CAST(c8 AS JSON)         |
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
| "base64:type252:WzEsICJqc29uIl0=" | "base64:type249:dHJ1ZQ==" | "base64:type250:YWJj" | "base64:type251:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type254:WzEsICJqc29uIl0AAAAAAAAAAAAAAAAAAAAAAAAAAAA=" | "base64:type15:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type16:AAAAAAAAMjA=" | "base64:type13:MjAyMA==" |
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE test_blob;
Query OK, 0 rows affected (0.01 sec)
  • If a forcible type conversion (::JSON) is performed on data in the current version, the actual converted JSON type depends on whether the GUC parameter m_format_behavior_compat_options is set to cast_as_new_json.
Parent topic: Data Types