在 MariaDB 中使用 JSON_EXTRACT 函数提取 JSON 数组中的元素
详细介绍在 MariaDB 中使用 JSON_EXTRACT() 函数及 JSON 路径表达式(如 $[0]、[*] 与递归通配符)从 JSON 数组中提取元素的语法与实战示例。
发布于
在 MariaDB 中存储和处理半结构化数据时,JSON 数组是常见的数据载体。若要从 JSON 数组中按索引或通配符检索特定元素,主要使用内置的 JSON_EXTRACT() 函数或其简写操作符。
JSON_EXTRACT() 函数接收两个核心参数:
- 第一个参数:待检索的 JSON 文档(JSON 字符串或 JSON 列)。
- 第二个参数:一个或多个用于定位数据的 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 数据的效率。