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

本文描述了如何在 MariaDB 中使用 JSON_EXTRACT 函数提取 JSON 数组中的元素。

发布于

MariaDB JSON_EXTRACT() 函数用于从 JSON 数据中提取提取元素。 此函数接受两个参数，第一个参数是要从中抽取的 JSON 文档，第二个参数是要抽取的路径表达式。

如果要从 JSON 数组中提取元素，请使用数组元素选择器：

  • [N]：根据 N 指定的索引选择元素，从 0 开始。
  • [*]: 选择数组中的所有元素。

示例 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, '$[*]');

输出：

+-----------------------------+
| JSON_EXTRACT(@json, '$[*]') |
+-----------------------------+
| [0, 1, 2, 3]                |
+-----------------------------+

示例 2： 对象中的数组

借助路径表达式，您可以轻松的从对象中的数组中提取元素，如下：

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

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

输出：

+--------------+--------------+
| $.hobbies[0] | $.hobbies[1] |
+--------------+--------------+
| "Football"   | "Piano"      |
+--------------+--------------+

在这个示例中，我们使用了路径表达式 $.hobbies[0]，这里 $.hobbies 先定位到 JSON 文档中的 hobbies 数组， [0] 定位 hobbies 数组中的元素。

如果您需要定位的数组位于一个很大的 JSON 文档，他的路径可能很深。您可以使用通配符（**）进行递归匹配

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

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

输出：

+--------------+------------+
| hobbies[0]   | hobbies[1] |
+--------------+------------+
| ["Football"] | ["Piano"]  |
+--------------+------------+

在本示例中，我们使用了路径表达式 $**.hobbies[0] 查找 JSON 文档中的所有的 hobbies 数组中的第一个元素。

注意：由于是递归查找，返回的是一个数组 ["Football"]。

结论

本文给出了几个借助 MariaDB JSON_EXTRACT() 函数从 JSON 数组中提取元素的示例。