JSON Functions That Return JSON Values
This document describes JSON functions that return JSON values.
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)