Skip to content

Latest commit

 

History

History
168 lines (132 loc) · 5.42 KB

json-functions-return.md

File metadata and controls

168 lines (132 loc) · 5.42 KB
Raw
title summary
JSON Functions That Return JSON Values
Learn about JSON functions that return JSON values.

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)

See also