MySQL JSON_VALUE() 函数使用指南

MySQL JSON_VALUE() 函数从一个指定的 JSON 文档中提取指定路径的值并返回。

JSON_VALUE() 语法

这里是 MySQL JSON_VALUE() 的语法：

JSON_VALUE(json, path [RETURNING type] [{NULL | ERROR | DEFAULT value} ON EMPTY] [{NULL | ERROR | DEFAULT value} ON ERROR])

参数

  • json

    必需的。一个 JSON 文档。

  • path

    必需的。一个路径表达式。

  • RETURNING type

    可选的。他决定了返回值的类型。您可以使用下面值中的一个：

    • FLOAT
    • DOUBLE
    • DECIMAL
    • SIGNED
    • UNSIGNED
    • DATE
    • TIME
    • DATETIME
    • YEAR (MySQL 8.0.22 and later)
    • CHAR
    • JSON

  • {NULL | ERROR | DEFAULT value} ON EMPTY

    可选的。如果指定了，它决定了指定路径下没有数据的返回值：

    • NULL ON EMPTY: 如果指定路径下没有数据，JSON_VALUE() 函数将返回 NULL，这是默认的行为。
    • DEFAULT value ON EMPTY: 如果指定路径下没有数据，JSON_VALUE() 函数将返回 value。
    • ERROR ON EMPTY: 如果指定路径下没有数据，JSON_VALUE() 函数将抛出一个错误。

  • {NULL | ERROR | DEFAULT value} ON ERROR

    可选的。如果指定了，它决定了处理错误的逻辑：

    • NULL ON ERROR: 如果有错误，JSON_VALUE() 函数将返回 NULL，这是默认的行为。
    • DEFAULT value ON ERROR: 如果有错误，JSON_VALUE() 函数将返回 value。
    • ERROR ON ERROR: 如果有错误，JSON_VALUE() 函数将抛出一个错误。

返回值

如果 JSON 文档在指定的路径上有值，JSON_VALUE() 函数返回路径上的数据。除非您使用 RETURNING type 子句，否则 JSON_VALUE() 函数默认将找到的数据作为字符串返回。

JSON_VALUE() 函数将在以下情况下返回错误：

  • 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
  • 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_VALUE() 示例

让我们先创建一个 JSON 文档以供以下的例子使用：

SET @json = '[
  {
    "name": "Tim",
    "age": 20,
    "amount": 123.456,
    "note": "He is perfect."
  },
  {
    "name": "Tom",
    "age": 20,
    "amount": 456.1
  }
]';

这里， 我们创建了一个 JSON 数组，它包含两个用户信息。

返回第一个用户的年龄

SELECT JSON_VALUE(@json, '$[0].age');

+-------------------------------+
| JSON_VALUE(@json, '$[0].age') |
+-------------------------------+
| 20                            |
+-------------------------------+

返回第二个用户的金额

SELECT JSON_VALUE(@json, '$[1].amount');

+----------------------------------+
| JSON_VALUE(@json, '$[1].amount') |
+----------------------------------+
| 456.1                            |
+----------------------------------+

如果您想要返回的数据像第一位用户的金额那样具有 3 位小数位，您可以使用 RETURNING DECIMAL(10,3) 子句：

SELECT JSON_VALUE(@json, '$[1].amount' RETURNING DECIMAL(9,3));

+---------------------------------------------------------+
| JSON_VALUE(@json, '$[1].amount' RETURNING DECIMAL(9,3)) |
+---------------------------------------------------------+
|                                                 456.100 |
+---------------------------------------------------------+

默认值

如果指定的路径上没有数据，您可以使用 ON EMPTY 子句设定默认值：

SELECT
    JSON_VALUE(@json, '$[0].note' DEFAULT 'Nothing' ON EMPTY) AS `$[0].note`,
    JSON_VALUE(@json, '$[1].note' DEFAULT 'Nothing' ON EMPTY) AS `$[1].note`;

+----------------+-----------+
| $[0].note      | $[1].note |
+----------------+-----------+
| He is perfect. | Nothing   |
+----------------+-----------+