JSON Type

TiDB supports the JSON (JavaScript Object Notation) data type, which is useful for storing semi-structured data. The JSON data type provides the following advantages over storing JSON-format strings in a string column:

  • Use the Binary format for serialization. The internal format permits quick read access to JSON document elements.
  • Automatic validation of the JSON documents stored in JSON columns. Only valid documents can be stored.

JSON columns, like columns of other binary types, are not indexed directly, but you can index the fields in the JSON document in the form of generated column:

  1. CREATE TABLE city (
  2.     id INT PRIMARY KEY,
  3.     detail JSON,
  4.     population INT AS (JSON_EXTRACT(detail, '$.population')),
  5.     index index_name (population)
  6.     );
  7. INSERT INTO city (id,detail) VALUES (1, '{"name": "Beijing", "population": 100}');
  8. SELECT id FROM city WHERE population >= 100;

For more information, see JSON Functions and Generated Columns.

Restrictions

  • Currently, TiDB only supports pushing down limited JSON functions to TiFlash. For more information, see Push-down expressions.
  • TiDB Backup & Restore (BR) changes how JSON column data is encoded in v6.3.0. Therefore, it is not recommended to use BR to restore data containing JSON columns to a TiDB cluster earlier than v6.3.0.
  • Do not use any replication tool to replicate data containing non-standard JSON data types, such as DATE, DATETIME, and TIME.

MySQL compatibility

  • When you create JSON columns with data in the BINARY type, MySQL mislabels the data as the STRING type currently, while TiDB processes it as the BINARY type correctly.

    1. CREATE TABLE test(a json);
    2. INSERT INTO test SELECT json_objectagg('a', b'01010101');
    4. -- In TiDB, executing the following SQL statement returns `0, 0`. In MySQL, executing the following SQL statement returns `0, 1`.
    5. mysql> SELECT JSON_EXTRACT(JSON_OBJECT('a', b'01010101'), '$.a') = "base64:type15:VQ==" AS r1, JSON_EXTRACT(a, '$.a') = "base64:type15:VQ==" AS r2 FROM test;
    6. +------+------+
    7. | r1   | r2   |
    8. +------+------+
    9. |    0 |    0 |
    10. +------+------+
    11. 1 row in set (0.01 sec)

    For more information, see issue #37443.

  • When converting the data type from ENUM or SET to JSON, TiDB checks the correctness of data format. For example, executing the following SQL statements in TiDB will return an error.

    1. CREATE TABLE t(e ENUM('a'));
    2. INSERT INTO t VALUES ('a');
    3. mysql> SELECT CAST(e AS JSON) FROM t;
    4. ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.

    For more information, see issue #9999.

  • In TiDB, you can use ORDER BY to sort JSON arrays or JSON objects.

    In MySQL, if you use ORDER BY to sort JSON arrays or JSON objects, MySQL returns a warning and the sorting result does not match the result of the comparison operation:

    1. CREATE TABLE t(j JSON);
    2. INSERT INTO t VALUES ('[1,2,3,4]');
    3. INSERT INTO t VALUES ('[5]');
    5. mysql> SELECT j FROM t WHERE j < JSON_ARRAY(5);
    6. +--------------+
    7. | j            |
    8. +--------------+
    9. | [1, 2, 3, 4] |
    10. +--------------+
    11. 1 row in set (0.00 sec)
    13. -- In TiDB, executing the following SQL statement returns the correct sorting result. In MySQL, executing the following SQL statement returns the "This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'." warning and the sorting result is inconsistent with the comparison result of `<`.
    14. mysql> SELECT j FROM t ORDER BY j;
    15. +--------------+
    16. | j            |
    17. +--------------+
    18. | [1, 2, 3, 4] |
    19. | [5]          |
    20. +--------------+
    21. 2 rows in set (0.00 sec)

    For more information, see issue #37506.

  • When you insert data to a JSON column, TiDB implicitly converts the value of the data to the JSON type.

    1. CREATE TABLE t(col JSON);
    3. -- In TiDB, the following INSERT statement is executed successfully. In MySQL, executing the following INSERT statement returns the "Invalid JSON text" error.
    4. INSERT INTO t VALUES (3);

For more information about the JSON data type, see JSON functions and Generated Columns.