JSON Functions That Return JSON Values
本文档描述返回 JSON 值的 JSON 函数。
JSON_DEPTH()
JSON_DEPTH(json_doc)
函数返回 JSON 文档的最大深度。
示例:
在以下示例中,JSON_DEPTH()
返回 3
,因为有三个层级:
- 根 (
$
) - weather (
$.weather
) - weather current (
$.weather.sunny
)
SELECT JSON_DEPTH('{"weather": {"current": "sunny"}}');
+-------------------------------------------------+
| JSON_DEPTH('{"weather": {"current": "sunny"}}') |
+-------------------------------------------------+
| 3 |
+-------------------------------------------------+
1 行结果(0.00 秒)
JSON_LENGTH()
JSON_LENGTH(json_doc [,path])
函数返回 JSON 文档的长度。如果提供了 path
参数,则返回路径内值的长度。
示例:
在以下示例中,返回值为 1
,因为文档根部唯一的项是 weather
。
SELECT JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$');
+----------------------------------------------------------------------------+
| JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$') |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
1 行结果(0.00 秒)
在以下示例中,返回值为 2
,因为 $.weather
位置有两个项:current
和 tomorrow
。
SELECT JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$.weather');
+------------------------------------------------------------------------------------+
| JSON_LENGTH('{"weather": {"current": "sunny", "tomorrow": "cloudy"}}','$.weather') |
+------------------------------------------------------------------------------------+
| 2 |
+------------------------------------------------------------------------------------+
1 行结果(0.01 秒)
JSON_TYPE()
JSON_TYPE(json_val)
函数返回一个字符串,指示 JSON 值的类型。
示例:
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 行结果(0.00 秒)
注意,看似相同的值可能类型不同,正如以下示例所示。
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 行结果(0.00 秒)
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 行结果(0.00 秒)
JSON_VALID()
JSON_VALID(str)
函数检查参数是否为有效的 JSON。这对于在将列转换为 JSON
类型之前进行验证非常有用。
SELECT JSON_VALID('{"foo"="bar"}');
+-----------------------------+
| JSON_VALID('{"foo"="bar"}') |
+-----------------------------+
| 0 |
+-----------------------------+
1 行结果(0.01 秒)
SELECT JSON_VALID('{"foo": "bar"}');
+------------------------------+
| JSON_VALID('{"foo": "bar"}') |
+------------------------------+
| 1 |
+------------------------------+
1 行结果(0.01 秒)