跳到主要内容

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],以此类推。
    • * 代表通配符,即 $.* 代表根对象的所有成员,$[*] 代表数组的所有元素。
  • 如果 <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. 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 |
    +------+------+