📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

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 位置有两个项:currenttomorrow

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 秒)

另请参见

文档内容是否有帮助?