JSON_EXTRACT

描述

从 JSON 类型的数据中提取 json_path 指定的字段。

语法

JSON_EXTRACT (<json_object>, <path>[, <path2>, ...])

参数

必须参数：

• <json_object>: 要提取的 JSON 类型的表达式。
• <path>: 要从目标 JSON 中提取目标元素的 JSON 路径。

可选/可变参数

• <path2> 可以从 JSON 对象中提取多个路径的值。

返回值

• Nullable(JSON): 返回 <path> 指向的 JSON 元素，如果匹配了多个结果会以 JSON 数组的形式返回。

使用说明

• 如果 <json_object> 是 NULL，或者 <path> 是 NULL，返回 NULL。

• 对于单个 <path> 参数的情况，如果 <path> 如果路径不存在，返回 NULL。

• 对于多个 <path> 参数的情况，不存在的路径会被忽略，匹配到的元素组成 JSON 数组返回。如果一个匹配也没有则返回 NULL。

• 如果 <path> 不是一个合法的路径，报错。

• 如果 <path> 对应的值是字符串，返回的字符串会被双引号（"）包围，如果要得到没有双引号的结果，请对结果使用函数 JSON_UNQUOTE 以去掉双引号。

• <path> 的语法如下

  • $ 代表 json root
  • .k1 代表 json object 中 key 为k1的元素
    • 如果 key 列值包含 ".", <path> 中需要用双引号，例如 SELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"');
  • [i] 代表 json array 中下标为 i 的元素
    • 获取 json_array 的最后一个元素可以用$[last]，倒数第二个元素可以用$[last-1]，以此类推。
  • * 代表通配符，即 $.* 代表根对象的所有成员，$[*] 代表数组的所有元素。
  • ** 代表通配符，通常和 '$' 一起使用： '$**' 代表所有的路径（以及多层的子路径，见下面的示例 9）。

• 如果 <path> 存在通配符(*)，匹配的结果会以数组形式返回。

示例

1. 一般参数

   SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1');

   +-----------------------------------------------+
   | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1') |
   +-----------------------------------------------+
   | "v31"                                         |
   +-----------------------------------------------+

   注意：返回的结果是"v31" 不是 v31。

2. NULL 参数

   select JSON_EXTRACT(null, '$.k1');

   +----------------------------+
   | JSON_EXTRACT(null, '$.k1') |
   +----------------------------+
   | NULL                       |
   +----------------------------+

3. <path> 为 NULL

   SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', NULL);

   +---------------------------------------------+
   | JSON_EXTRACT('{"k1":"v31","k2":300}', NULL) |
   +---------------------------------------------+
   | NULL                                        |
   +---------------------------------------------+

4. 多级路径

   SELECT JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key');

   +------------------------------------------------------------------------+
   | JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key') |
   +------------------------------------------------------------------------+
   | 1234.56                                                                |
   +------------------------------------------------------------------------+

5. 数组路径

   SELECT JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$[2]');

   +----------------------------------------------------------------------+
   | JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$.[2]') |
   +----------------------------------------------------------------------+
   | "2025-07-16 18:35:25"                                                |
   +----------------------------------------------------------------------+

6. 不存在的 path

   SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3');

   +-----------------------------------------------+
   | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3') |
   +-----------------------------------------------+
   | NULL                                          |
   +-----------------------------------------------+

7. 多个路径参数

   select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id', '$.not_exists');

   +--------------------------------------------------------------------------------+
   | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id', '$.not_exists') |
   +--------------------------------------------------------------------------------+
   | ["doris",123]                                                                  |
   +--------------------------------------------------------------------------------+

   即使只有一个匹配也会以数组形式返回

   select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2', '$.not_exists');

   +---------------------------------------------------------------------------------+
   | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2', '$.not_exists') |
   +---------------------------------------------------------------------------------+
   | ["doris"]                                                                       |
   +---------------------------------------------------------------------------------+

   > 如果所有路径都没有匹配则返回 NULL
   select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2', '$.not_exists');

   +------------------------------------------------------------------------------+
   | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2', '$.not_exists') |
   +------------------------------------------------------------------------------+
   | NULL                                                                         |
   +------------------------------------------------------------------------------+

8. 通配符路径

   select json_extract('{"k": [1,2,3,4,5]}', '$.k[*]');

   +----------------------------------------------+
   | json_extract('{"k": [1,2,3,4,5]}', '$.k[*]') |
   +----------------------------------------------+
   | [1,2,3,4,5]                                  |
   +----------------------------------------------+

   select json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}', '$.*', '$.k3.k4');

   +---------------------------------------------------------------------------------------+
   | json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}', '$.*', '$.k3.k4') |
   +---------------------------------------------------------------------------------------+
   | [[1,2,3,4,5],"abc",{"k4":"v4"},"v4"]                                                  |
   +---------------------------------------------------------------------------------------+

9. '**' 作为通配符

   select json_extract('{"k": 123, "b": {"k": ["ab", "cd"]}}', '$**.k');

   +---------------------------------------------------------------+
   | json_extract('{"k": 123, "b": {"k": ["ab", "cd"]}}', '$**.k') |
   +---------------------------------------------------------------+
   | [123,["ab","cd"]]                                             |
   +---------------------------------------------------------------+

10. Value 是 NULL 的情况

    select JSON_EXTRACT('{"id": 123, "name": null}', '$.name') v, JSON_EXTRACT('{"id": 123, "name": null}', '$.name') is null v2;

    +------+------+
    | v    | v2   |
    +------+------+
    | null |    0 |
    +------+------+