JSON Functions That Return JSON Values

November 19, 2025 ยท View on GitHub

TiDB supports all the JSON functions that return JSON value attributes available in MySQL 8.0.

JSON_DEPTH()

The JSON_DEPTH(json_doc) function returns the maximum depth of a JSON document.

Examples:

In the following example, JSON_DEPTH() returns 3 because there are three levels:

  • root ($)
  • weather ($.weather)
  • weather current ($.weather.sunny)
SELECT JSON_DEPTH('{"weather": {"current": "sunny"}}');

+-------------------------------------------------+
| JSON_DEPTH('{"weather": {"current": "sunny"}}') |
+-------------------------------------------------+
|                                               3 |
+-------------------------------------------------+
1 row in set (0.00 sec)

JSON_LENGTH()

The JSON_LENGTH(json_doc [,path]) function returns the length of a JSON document. If a path argument is given, it returns the length of the value within the path.

Examples:

In the following example, the returned value is 1 because the only item at the root of the document is weather.

SELECT JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$');

+----------------------------------------------------------------------------+
| JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$') |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

In the following example, the returned value is 2 because there are two items at $.weather: current and tomorrow.

SELECT JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$.weather');

+------------------------------------------------------------------------------------+
| JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$.weather') |
+------------------------------------------------------------------------------------+
|                                                                                  2 |
+------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

JSON_TYPE()

The JSON_TYPE(json_val) function returns a string indicating the type of a JSON value.

Example:

WITH demo AS (
    SELECT 'null' AS 'v' 
    UNION SELECT '"foobar"' 
    UNION SELECT 'true' 
    UNION SELECT '5' 
    UNION SELECT '1.14' 
    UNION SELECT '[]' 
    UNION SELECT '{}' 
    UNION SELECT POW(2,63)
)
SELECT v, JSON_TYPE(v) FROM demo ORDER BY 2;

+----------------------+--------------+
| v                    | JSON_TYPE(v) |
+----------------------+--------------+
| []                   | ARRAY        |
| true                 | BOOLEAN      |
| 1.14                 | DOUBLE       |
| 9.223372036854776e18 | DOUBLE       |
| 5                    | INTEGER      |
| null                 | NULL         |
| {}                   | OBJECT       |
| "foobar"             | STRING       |
+----------------------+--------------+
8 rows in set (0.00 sec)

Note that values that look the same might not have the same type, as demonstrated in the following example.

SELECT '"2025-06-14"',CAST(CAST('2025-06-14' AS date) AS json);

+--------------+------------------------------------------+
| "2025-06-14" | CAST(CAST('2025-06-14' AS date) AS json) |
+--------------+------------------------------------------+
| "2025-06-14" | "2025-06-14"                             |
+--------------+------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_TYPE('"2025-06-14"'),JSON_TYPE(CAST(CAST('2025-06-14' AS date) AS json));

+---------------------------+-----------------------------------------------------+
| JSON_TYPE('"2025-06-14"') | JSON_TYPE(CAST(CAST('2025-06-14' AS date) AS json)) |
+---------------------------+-----------------------------------------------------+
| STRING                    | DATE                                                |
+---------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)

JSON_VALID()

The JSON_VALID(str) function checks if the argument is valid JSON. This can be useful for checking a column before converting it to the JSON type.

SELECT JSON_VALID('{"foo"="bar"}');

+-----------------------------+
| JSON_VALID('{"foo"="bar"}') |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.01 sec)

SELECT JSON_VALID('{"foo": "bar"}');

+------------------------------+
| JSON_VALID('{"foo": "bar"}') |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.01 sec)

See also