在 MariaDB 中使用 JSON_EXTRACT 函数提取 JSON 数组中的元素

详细介绍在 MariaDB 中使用 JSON_EXTRACT() 函数及 JSON 路径表达式(如 $[0]、[*] 与递归通配符)从 JSON 数组中提取元素的语法与实战示例。

发布于

在 MariaDB 中存储和处理半结构化数据时,JSON 数组是常见的数据载体。若要从 JSON 数组中按索引或通配符检索特定元素,主要使用内置的 JSON_EXTRACT() 函数或其简写操作符。

JSON_EXTRACT() 函数接收两个核心参数:

  1. 第一个参数:待检索的 JSON 文档(JSON 字符串或 JSON 列)。
  2. 第二个参数:一个或多个用于定位数据的 JSON 路径表达式(Path Expression)。

常用数组路径表达式语法

在 JSON 路径语法中,$ 代表 JSON 根节点:

  • $[N]:选择数组中索引为 N 的元素(索引从 0 开始)。
  • $[last]:选择数组中的最后一个元素(支持从后向前索引,例如 $[last-1])。
  • $[*]:通配选择数组中的所有元素。

示例 1:一维纯数组提取

以下示例演示如何按索引提取单个数组元素:

SET @json = '[0, 1, 2, 3]';

SELECT
  JSON_EXTRACT(@json, '$[0]') AS '$[0]',
  JSON_EXTRACT(@json, '$[1]') AS '$[1]',
  JSON_EXTRACT(@json, '$[2]') AS '$[2]',
  JSON_EXTRACT(@json, '$[3]') AS '$[3]';

输出:

+------+------+------+------+
| $[0] | $[1] | $[2] | $[3] |
+------+------+------+------+
| 0    | 1    | 2    | 3    |
+------+------+------+------+

如果需要一次性提取数组中的所有元素,可以使用 $[*]:

SET @json = '[0, 1, 2, 3]';

SELECT JSON_EXTRACT(@json, '$[*]') AS all_elements;

输出:

+--------------+
| all_elements |
+--------------+
| [0, 1, 2, 3] |
+--------------+

示例 2:提取对象中的嵌套数组

当数组嵌入在 JSON 对象的某个键(Key)下时,先使用键名导航,再配合数组下标索引。

SET @json = '{
  "name": "Tom",
  "hobbies": ["Football", "Piano"]
}';

SELECT
  JSON_EXTRACT(@json, '$.hobbies[0]') AS first_hobby,
  JSON_EXTRACT(@json, '$.hobbies[1]') AS second_hobby;

输出:

+-------------+--------------+
| first_hobby | second_hobby |
+-------------+--------------+
| "Football"  | "Piano"      |
+-------------+--------------+

在此示例中,路径表达式 $.hobbies[0] 先通过 $.hobbies 定位到对象中的兴趣数组,再通过 [0] 选取数组中的首个元素。


示例 3:使用递归通配符 ** 深层提取

当 JSON 结构层级较深,或者在不同的子对象中均存在同名数组时,可以使用双星号通配符 ** 进行递归检索:

SET @json = '{
  "user": {
    "profile": {
      "name": "Tom",
      "hobbies": ["Football", "Piano"]
    }
  }
}';

SELECT
  JSON_EXTRACT(@json, '$**.hobbies[0]') AS first_hobby,
  JSON_EXTRACT(@json, '$**.hobbies[1]') AS second_hobby;

输出:

+--------------+--------------+
| first_hobby  | second_hobby |
+--------------+--------------+
| ["Football"] | ["Piano"]    |
+--------------+--------------+

[!NOTE] 注意:使用递归通配符 ** 检索时,即便仅匹配到一个目标,MariaDB 也会将匹配结果封装在数组中返回(如 ["Football"])。


进阶技巧:使用 -> 箭头语法

在 MariaDB 中,column -> 'path' 是 JSON_EXTRACT() 的语法糖,使用起来更加紧凑:

SET @json = '["Database", "Linux", "Python"]';

SELECT @json -> '$[0]' AS item_0;

输出:

+------------+
| item_0     |
+------------+
| "Database" |
+------------+

若希望去掉外层双引号得到纯文本,可以使用 JSON_UNQUOTE(@json -> '$[0]')。


总结

  • 检索 JSON 数组单个元素使用 $[0]、$[1] 或 $[last]。
  • 提取对象中嵌套数组使用 $.key[index]。
  • 深层未知路径使用 $**.key[index]。
  • 掌握 JSON_EXTRACT() 与 -> 运算符能大幅提升在 SQL 中处理 JSON 数据的效率。