MySQL JSON 函数一篇通读-每日运维

MySQL JSON 函数一篇通读

一起学习MySQL JSON 函数吧

[TOC]

MySQL JSON_ARRAY() 函数

MySQL JSON_ARRAY() 函数返回一个包含了所有参数的 JSON 数组。

JSON_ARRAY() 语法

这里是 MySQL JSON_ARRAY() 的语法：

JSON_ARRAY(value1[, value2[, ...]])

参数

value1[, value2[, …]]

可选的。一些值，他们将被放在 JSON 数组中。

返回值

JSON_ARRAY() 函数评估参数中的所有的值，并返回一个包含了所有参数的 JSON 数组。

这里可能有一些转换发生：

TRUE 被转换为 true
FALSE 被转换为 false
NULL 被转换为 null
日期，时间，日期时间被转换为字符串

JSON_ARRAY() 示例

这里列出了几个常见的 JSON_ARRAY() 示例。

示例 1

SELECT JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW()); +---------------------------------------------------------------+ | JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW()) | +---------------------------------------------------------------+ | [123, "abc", null, true, false, "2022-04-18 07:47:23.000000"] | +---------------------------------------------------------------+

包含数组的数组

SELECT JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec')); +------------------------------------------------------------+ | JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec')) | +------------------------------------------------------------+ | [[123, 456], ["abc", "dec"]] | +------------------------------------------------------------+

这里，我们使用了 JSON_ARRAY() 的结果作为 JSON_ARRAY() 的参数。

包含对象的数组

SELECT JSON_ARRAY( JSON_OBJECT('name', 'Jim', 'age', 20), JSON_OBJECT('name', 'Tim', 'age', 18) ) AS objct_array; +----------------------------------------------------------+ | objct_array | +----------------------------------------------------------+ | [{"age": 20, "name": "Jim"}, {"age": 18, "name": "Tim"}] | +----------------------------------------------------------+

这里，我们使用了 JSON_OBJECT()的结果作为 JSON_ARRAY() 的参数。

MySQL JSON_ARRAY_APPEND() 函数

MySQL JSON_ARRAY_APPEND() 函数向 JSON 文档中的指定的数组中追加一个值并返回修改后的 JSON 文档。

JSON_ARRAY_APPEND() 语法

这里是 MySQL JSON_ARRAY_APPEND() 的语法：

JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)

参数

json

必需的。被修改的 JSON 文档。
path

必需的。添加新元素的路径。一个有效的路径表达式，它不能包含 * 或 **。
value

必需的。被添加到数组的新元素值。

返回值

JSON_ARRAY_APPEND() 函数用来修改 JSON 文档，它向指定的数组节点中追加一个元素，并返回修改后的 JSON 文档。

如果路径表达式指示的节点不是一个数组节点，JSON_ARRAY_APPEND() 函数将会将此节点的值作为数组的第一个元素，并在数组的尾部追加新元素。
如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

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

JSON_ARRAY_APPEND() 示例

这里列出了几个常见的 JSON_ARRAY_APPEND() 用法示例。

在数组末尾追加元素

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4); +----------------------------------------+ | JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4) | +----------------------------------------+ | [1, 2, 3, 4] | +----------------------------------------+

这里，路径表达式 $ 表示正在操作的 JSON 文档。

向内嵌数组中追加元素

SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4); +---------------------------------------------+ | JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4) | +---------------------------------------------+ | [1, [2, 3, 4]] | +---------------------------------------------+

这里，路径表达式 $[1] 表示正在操作的 JSON 文档数组中的第 2 个元素。

向对象中的数组中追加元素

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food"); +---------------------------------------------------------------------------+ | JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food") | +---------------------------------------------------------------------------+ | {"name": "Tim", "hobby": ["car", "food"]} | +---------------------------------------------------------------------------+

这里，路径表达式 $.hobby 表示正在操作的 JSON 文档对象的成员 hobby。

向非数组中追加值

SELECT JSON_ARRAY_APPEND('1', '$', 2); +--------------------------------+ | JSON_ARRAY_APPEND('1', '$', 2) | +--------------------------------+ | [1, 2] | +--------------------------------+

这里，JSON 文档有元素 1 修改成了数组 [1, 2]。

同样可以向数组中的非数组成员中追加，比如：

SELECT JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3); +----------------------------------------+ | JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3) | +----------------------------------------+ | [1, [2, 3]] | +----------------------------------------+

还可以向对象中的非数组成员中追加，比如：

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food"); +-------------------------------------------------------------------------+ | JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food") | +-------------------------------------------------------------------------+ | {"name": "Tim", "hobby": ["car", "food"]} | +-------------------------------------------------------------------------+

MySQL JSON_ARRAY_INSERT() 函数

MySQL JSON_ARRAY_INSERT() 函数向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。

JSON_ARRAY_INSERT() 语法

这里是 MySQL JSON_ARRAY_INSERT() 的语法：

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

参数

json

必需的。被修改的 JSON 文档。
path

必需的。插入新元素的数组元素位置。一个有效的路径表达式，它不能包含 * 或 **。比如 $[0] 和 $.a[0] 表示在数组的开头插入新元素。
value

必需的。被插入到数组的新元素值。

返回值

JSON_ARRAY_INSERT() 函数用来修改 JSON 文档，它向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。

如果路径表达式指示的数组元素超过了数组的长度，那么新元素将插入到数组的尾部。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式或者其中包含 * 或 **, MySQL 将会给出错误。
如果参数 path 指示的不是数组元素的路径, MySQL 将会给出错误。

JSON_ARRAY_INSERT() 示例

这里列出了几个常见的 JSON_ARRAY_INSERT() 用法示例。

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @json = '[1, [2, 3], {"a": [4, 5]}]';

在数组指定位置插入元素

让我们在数组的开头插入一个元素：

SELECT JSON_ARRAY_INSERT(@json, '$[0]', 0); +-------------------------------------+ | JSON_ARRAY_INSERT(@json, '$[0]', 0) | +-------------------------------------+ | [0, 1, [2, 3], {"a": [4, 5]}] | +-------------------------------------+

让我们将新元素插入到数组的第 3 个元素的位置：

SELECT JSON_ARRAY_INSERT(@json, '$[2]', 4); +-------------------------------------+ | JSON_ARRAY_INSERT(@json, '$[2]', 4) | +-------------------------------------+ | [1, [2, 3], 4, {"a": [4, 5]}] | +-------------------------------------+

让我们在数组的末尾插入新元素：

SELECT JSON_ARRAY_INSERT(@json, '$[3]', 'x'); +---------------------------------------+ | JSON_ARRAY_INSERT(@json, '$[3]', 'x') | +---------------------------------------+ | [1, [2, 3], {"a": [4, 5]}, "x"] | +---------------------------------------+

向内嵌数组中插入元素

SELECT JSON_ARRAY_INSERT(@json, '$[1][0]', 'x'); +------------------------------------------+ | JSON_ARRAY_INSERT(@json, '$[1][0]', 'x') | +------------------------------------------+ | [1, ["x", 2, 3], {"a": [4, 5]}] | +------------------------------------------+

向对象中的数组中插入元素

SELECT JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x'); +--------------------------------------------+ | JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x') | +--------------------------------------------+ | [1, [2, 3], {"a": ["x", 4, 5]}] | +--------------------------------------------+

MySQL JSON_CONTAINS() 函数

MySQL JSON_CONTAINS() 函数检查一个 JSON 文档中是否包含另一个 JSON 文档。

如果您需要检查 JSON 文档中指定的路径下的是否存在数据，请使用 JSON_CONTAINS_PATH()函数。

JSON_CONTAINS() 语法

这里是 MySQL JSON_CONTAINS() 的语法：

JSON_CONTAINS(target_json, candidate_json) JSON_CONTAINS(target_json, candidate_json, path)

参数

target_json

必需的。一个 JSON 文档。
candidate_json

必需的。被包含的 JSON 文档。
path

可选的。一个路径表达式。

返回值

如果在 JSON 文档 target_json 中包含了 JSON 文档 candidate_json，JSON_CONTAINS() 函数将返回 1，否则返回 0。如果提供了 path 参数，则检查由 path 匹配的部分是否包含 candidate_json JSON 文档。

如果存在以下的情况， JSON_CONTAINS() 函数将返回 NULL：

如果 JSON 文档中不存在指定的路径。
如果任意一个参数为 NULL。

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

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

JSON_CONTAINS() 示例

这里列出了几个常见的 JSON_CONTAINS() 用法示例。

示例: 数组

SELECT JSON_CONTAINS('[1, 2, {"x": 3}]', '1') as `1`, JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}') as `{"x": 3}`, JSON_CONTAINS('[1, 2, {"x": 3}]', '3') as `3`; +------+----------+------+ | 1 | {"x": 3} | 3 | +------+----------+------+ | 1 | 1 | 0 | +------+----------+------+

示例: 指定路径

SELECT JSON_CONTAINS('[1, 2, [3, 4]]', '2'), JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]'); +--------------------------------------+----------------------------------------------+ | JSON_CONTAINS('[1, 2, [3, 4]]', '2') | JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') | +--------------------------------------+----------------------------------------------+ | 1 | 0 | +--------------------------------------+----------------------------------------------+

这里，我们已经在 JSON_CONTAINS(’[1, 2, [3, 4]]’, ‘2’, ‘$[2]’) 指定了路径表达式 $[2]，其匹配的内容是 [3, 4]。 [3, 4] 中没有包含 2，因此，它返回了 0。

MySQL JSON_CONTAINS_PATH() 函数

MySQL JSON_CONTAINS_PATH() 函数检查一个 JSON 文档中在指定的路径上是否有值存在。

JSON_CONTAINS_PATH() 语法

这里是 MySQL JSON_CONTAINS_PATH() 的语法：

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

参数

json

必需的。一个 JSON 文档。
one_or_all

必需的。可用值：‘one’, ‘all’。它指示是否检查所有的路径。
path

必需的。您应该至少指定一个路径表达式。

返回值

如果 JSON 文档在指定的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

JSON_CONTAINS_PATH() 根据 one_or_all 参数决定是否检查所有的路径：

如果是 ‘one’，且至少有一个路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。
如果是 ‘all’，且所有的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

如果任意一个参数为 NULL， JSON_CONTAINS_PATH() 函数将返回 NULL。

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

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

JSON_CONTAINS_PATH() 示例

这里列出了几个常见的 JSON_CONTAINS() 用法示例。

示例: 数组

SELECT JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]') as `$[0]`, JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]') as `$[3]`, JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[2].x') as `$[2].x`; +------+------+--------+ | $[0] | $[3] | $[2].x | +------+------+--------+ | 1 | 0 | 1 | +------+------+--------+

示例: one vs all

SELECT JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]') as `one`, JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]') as `all`; +------+------+ | one | all | +------+------+ | 1 | 0 | +------+------+

MySQL JSON_DEPTH() 函数

MySQL JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。

JSON_DEPTH() 语法

这里是 MySQL JSON_DEPTH() 的语法：

JSON_DEPTH(json)

参数

json

必需的。一个 JSON 文档。

返回值

JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。JSON_DEPTH() 函数按如下规则计算 JSON 文档的深度：

一个空的数组、空的对象或者纯值的深度是 1。
一个仅包含深度为 1 的元素的数组的深度是 2。
一个所有成员的值的深度为 1 的对象的深度是 2。
除此之外的其他 JSON 文档的深度都大于 2.

如果参数为 NULL，此函数将返回 NULL。

如果参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。

JSON_DEPTH() 示例

这里列出了几个常见的 JSON_DEPTH() 用法示例。

示例 1

SELECT JSON_DEPTH('[]'), JSON_DEPTH('[1, 2]'), JSON_DEPTH('[1, [2, 3]]'); +------------------+----------------------+---------------------------+ | JSON_DEPTH('[]') | JSON_DEPTH('[1, 2]') | JSON_DEPTH('[1, [2, 3]]') | +------------------+----------------------+---------------------------+ | 1 | 2 | 3 | +------------------+----------------------+---------------------------+

示例 2

SELECT JSON_DEPTH('{}'), JSON_DEPTH('{"x": 1}'), JSON_DEPTH('{"x": {"y": 1}}'); +------------------+------------------------+-------------------------------+ | JSON_DEPTH('{}') | JSON_DEPTH('{"x": 1}') | JSON_DEPTH('{"x": {"y": 1}}') | +------------------+------------------------+-------------------------------+ | 1 | 2 | 3 | +------------------+------------------------+-------------------------------+

MySQL JSON_EXTRACT() 函数

MySQL JSON_EXTRACT() 函数在 JSON 文档提取路径表达式指定的数据并返回。

JSON_EXTRACT() 语法

这里是 MySQL JSON_EXTRACT() 的语法：

JSON_EXTRACT(json, path, ...)

参数

json

必需的。一个 JSON 文档。
path

必需的。您应该至少指定一个路径表达式。

返回值

JSON_EXTRACT() 函数返回 JSON 文档中由路径表达式匹配的所有的值。如果路径表达式匹配了一个值，则返回该值，如果路径表达式匹配了多个值，则返回一个包含了所有值的数组。

如果存在以下的情况， JSON_EXTRACT() 函数将返回 NULL：

如果 JSON 文档中不存在指定的路径。
如果任意一个参数为 NULL。

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

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

JSON_EXTRACT() 示例

这里列出了几个常见的 JSON_EXTRACT() 用法示例。

示例: 数组

下面的语句展示了如何从数组中提取一个元素：

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]'); +------------------------------------------+ | JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]') | +------------------------------------------+ | {"x": 3} | +------------------------------------------+

让我们再看一个带有多个路径参数的例子：

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]'); +------------------------------------------------------------+ | JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]') | +------------------------------------------------------------+ | [3, 2, 1] | +------------------------------------------------------------+

示例: 对象

下面的语句展示了如何从对象中提取一个节点的值：

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y'); +----------------------------------------------+ | JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y') | +----------------------------------------------+ | [1, 2] | +----------------------------------------------+

让我们再看一个带有多个路径参数的例子：

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y'); +-----------------------------------------------------+ | JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y') | +-----------------------------------------------------+ | [1, [1, 2]] | +-----------------------------------------------------+

MySQL JSON_INSERT() 函数

MySQL JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。

JSON_INSERT() 语法

这里是 MySQL JSON_INSERT() 的语法：

JSON_INSERT(json, path, value[, path2, value2] ...)

参数

json

必需的。被修改的 JSON 文档。
path

必需的。一个有效的路径表达式，它不能包含 * 或 **。
value

必需的。被插入的数据。

返回值

JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。您可以提供多对 path-value 参数，以便一次插入多个数据。

JSON_INSERT() 函数只能将数据插入到不存在路径。如果 JSON 文档中已经存在指定的路径，则不会插入数据。

如果 value 为字符串， JSON_INSERT() 函数会将其作为字符串写入到 JSON 文档中。为了保证写入到 JSON 文档中的值的类型正确，请对 value 使用 JSON 类型的数据。

如果 path 为 $，JSON_INSERT() 函数会返回原 JSON 文档。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

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

JSON_INSERT() 示例

插入到数组

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @array = '[1, [2, 3], {"a": [4, 5]}]';

让我们在数组的开头插入一个元素：

SELECT JSON_INSERT(@array, '$[0]', 0, '$[3]', 6);

这里，我们想要在数组的开头插入 0，在数组的尾部插入 6。让我们看一下结果：

+-------------------------------------------+ | JSON_INSERT(@array, '$[0]', 0, '$[3]', 6) | +-------------------------------------------+ | [1, [2, 3], {"a": [4, 5]}, 6] | +-------------------------------------------+

我们发现，并没有在数据的开头($[0])插入数据。这是因为数组在 $[0] 位置已经有了值，因此不会插入。而数组在 $[3] 位置是没有数据的，因此 6 被插入到数组的尾部。

您还可以使用 JSON_ARRAY_APPEND() 向数组中插入数据。

插入 JSON 类型数据

除了插入简单的字面值，我们还可以插入复杂的 JSON 元素，比如数组和对象。

让我们首先创建一个包含 JSON 对象的 JSON 文档：

SET @obj = '{"x": 1}';

现在让我们插入一个值为 true 的成员 y:

SELECT JSON_INSERT(@obj, '$.y', 'true'); +----------------------------------+ | JSON_INSERT(@obj, '$.y', 'true') | +----------------------------------+ | {"x": 1, "y": "true"} | +----------------------------------+

我们发现， true 变成了 “true”。而不是我们希望的 {“x”: 1, “y”: true}。

这是因为，如果 value 参数为字符串， JSON_INSERT() 函数会将其作为字符串写入到 JSON 文档中。我们再看几个相似的例子：

SELECT JSON_INSERT(@obj, '$.y', '[1, 2]'); +------------------------------------+ | JSON_INSERT(@obj, '$.y', '[1, 2]') | +------------------------------------+ | {"x": 1, "y": "[1, 2]"} | +------------------------------------+

或者

SELECT JSON_INSERT(@obj, '$.y', '{"z": 2}'); +--------------------------------------+ | JSON_INSERT(@obj, '$.y', '{"z": 2}') | +--------------------------------------+ | {"x": 1, "y": "{"z": 2}"} | +--------------------------------------+

为了解决这个问题，我们可以使用 CAST()函数将数据转为 JSON 类型，比如：

SELECT JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON)); +----------------------------------------------------+ | JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON)) | +----------------------------------------------------+ | {"x": 1, "y": {"z": 2}} | +----------------------------------------------------+

MySQL JSON_KEYS() 函数

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。

JSON_KEYS() 语法

这里是 MySQL JSON_KEYS() 的语法：

JSON_KEYS(json) JSON_KEYS(json, path)

参数

json

必需的。一个 JSON 对象文档。
path

可选的。路径表达式。

返回值

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。如果指定了路径表达式，则返回路径表达式匹配的 JSON 对象中的最上层的成员组成的数组。

如果存在以下的情况， JSON_KEYS() 函数将返回 NULL：

未指定路径，且 JSON 文档不是一个 JSON 对象。
指定了路径，且路径匹配的 JSON 值不是 JSON 对象。
任意参数为 NULL。

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

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

JSON_KEYS() 示例

这里列出了几个常见的 JSON_KEYS() 用法示例。

下面的示例返回一个 JSON 对象的所有顶层成员组成的数组。

SELECT JSON_KEYS('{"x": 1, "y": 2, "z": 3}'); +---------------------------------------+ | JSON_KEYS('{"x": 1, "y": 2, "z": 3}') | +---------------------------------------+ | ["x", "y", "z"] | +---------------------------------------+

我们也可以返回有路径表达式匹配的 JSON 对象的键。

SELECT JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]'); +----------------------------------------------------+ | JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]') | +----------------------------------------------------+ | ["x", "y", "z"] | +----------------------------------------------------+

如果匹配的 JSON 文档不是 JSON 对象，JSON_KEYS() 返回 NULL。这个示例说明了这一点：

MySQL JSON_LENGTH() 函数

MySQL JSON_LENGTH() 函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。

JSON_LENGTH() 语法

这里是 MySQL JSON_LENGTH() 的语法：

JSON_LENGTH(json) JSON_LENGTH(json, path)

参数

json

必需的。一个 JSON 文档。
path

可选的。一个路径表达式。

返回值

如果指定了 path, JSON_LENGTH() 函数返回 JSON 文档中由路径指定的值的长度，否则返回 JSON 文档的长度。JSON_LENGTH() 函数按照如下规则计算 JSON 文档的长度:

纯值的长度是 1。比如， 1, ‘“x”’, true, false, null 的长度都是 1。
数组的长度是数组元素的数量。比如， [1, 2] 的长度是 2。
对象的长度是对象成员的数量。比如， {“x”: 1} 的长度是 1。
内嵌的数组或对象不参与计算长度。比如， {“x”: [1, 2]} 的长度是 1。

如果存在以下的情况， JSON_LENGTH() 函数将返回 NULL：

如果 JSON 文档中不存在指定的路径。
如果任意一个参数为 NULL。

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

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

JSON_LENGTH() 示例

这里列出了几个常见的 JSON_LENGTH() 用法示例。

示例: 值的长度

SELECT JSON_LENGTH('1') as `1`, JSON_LENGTH('true') as `true`, JSON_LENGTH('false') as `false`, JSON_LENGTH('null') as `null`, JSON_LENGTH('"abc"') as `"abc"`; +------+------+-------+------+-------+ | 1 | true | false | null | "abc" | +------+------+-------+------+-------+ | 1 | 1 | 1 | 1 | 1 | +------+------+-------+------+-------+

示例: 数组的长度

SELECT JSON_LENGTH('[]') as `[]`, JSON_LENGTH('[1, 2]') as `[1, 2]`, JSON_LENGTH('[1, {"x": 2}]') as `[1, {"x": 2}]`; +------+--------+---------------+ | [] | [1, 2] | [1, {"x": 2}] | +------+--------+---------------+ | 0 | 2 | 2 | +------+--------+---------------+

示例: 对象的长度

SELECT JSON_LENGTH('{}') as `[]`, JSON_LENGTH('{"x": 1, "y": 2}') as `{"x": 1, "y": 2}`, JSON_LENGTH('{"x": 1, "y": {"z" : 2}}') as `{"x": 1, "y": {"z" : 2}}`; +------+------------------+--------------------------+ | [] | {"x": 1, "y": 2} | {"x": 1, "y": {"z" : 2}} | +------+------------------+--------------------------+ | 0 | 2 | 2 | +------+------------------+--------------------------+

示例: 路径

您可以获取 JSON 文档中通过路径表达式指定的节点值的长度。

SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y'); +---------------------------------------------+ | JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y') | +---------------------------------------------+ | 2 | +---------------------------------------------+

这里，路径表达式 $.y 对应的值是 [1, 2]，[1, 2] 的长度为 2。这相当先使用 JSON_EXTRACT()函数提取路径匹配的部分，再计算长度，如下：

SELECT JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')); +-----------------------------------------------------------+ | JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')) | +-----------------------------------------------------------+ | 2 | +-----------------------------------------------------------+

MySQL JSON_MERGE() 函数

MySQL JSON_MERGE() 函数合并两个或多个 JSON 文档并返回合并的结果。

JSON_MERGE() 函数和 JSON_MERGE_PRESERVE()完全相同。

注意：MySQL JSON_MERGE() 函数已经在 MySQL 8.0.3 中弃用，并可能在将来的版本中被删除。请使用 JSON_MERGE_PRESERVE()

JSON_MERGE() 语法

这里是 MySQL JSON_MERGE() 的语法：

JSON_MERGE(json1, json2, ...)

参数

json1

必需的。一个 JSON 对象文档。
json2

必需的。一个 JSON 对象文档。

返回值

MySQL JSON_MERGE() 函数返回一个由参数指定的多个 JSON 文档合并后的 JSON 文档。JSON_MERGE() 按照如下规则合并多个 JSON 文档：

两个数组合并为一个数组，保留所有数组中的元素。
两个对象合并为一个对象，保留所有的键和值。
一个纯值会被包装成一个数组并作为数组进行合并
对象和数组合并时，会将对象包装到一个数组中并作为数组进行合并。

如果任意一个参数为 NULL， JSON_MERGE() 函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_MERGE() 示例

这里列出了几个常见的 JSON_MERGE() 用法示例。

合并数组

下面的演示了如何使用 JSON_MERGE() 函数合并两个或多个 JSON 数组。

SELECT JSON_MERGE('[1, 2]', '[2, 3]', '[3, 4, 5]'); +---------------------------------------------+ | JSON_MERGE('[1, 2]', '[2, 3]', '[3, 4, 5]') | +---------------------------------------------+ | [1, 2, 2, 3, 3, 4, 5] | +---------------------------------------------+

从结果我们可以看出所有数组中的所有元素都被保留下来，不管元素是否重复。并且元素的顺序保持和参数的顺序一致。

合并对象

下面的演示了如何使用 JSON_MERGE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}'); +--------------------------------------------+ | JSON_MERGE('{"x": 1}', '{"x": 2, "y": 3}') | +--------------------------------------------+ | {"x": [1, 2], "y": 3} | +--------------------------------------------+

这里，因为 “x”: 1 和 “x”: 2 的键都是 “x”，因此他们的值合并到一个数组中，即： [1, 2]。

合并纯值

下面的演示了如何使用 JSON_MERGE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE('1', 'true', '"hello"', 'null'); +--------------------------------------------+ | JSON_MERGE('1', 'true', '"hello"', 'null') | +--------------------------------------------+ | [1, true, "hello", null] | +--------------------------------------------+

这里，纯值在合并过程中包装成数组处理，因此，他们最终合并到一个数组中。

合并数组和对象

下面的演示了如何使用 JSON_MERGE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE('{"x": 1}', '[1, 2]'); +----------------------------------+ | JSON_MERGE('{"x": 1}', '[1, 2]') | +----------------------------------+ | [{"x": 1}, 1, 2] | +----------------------------------+

这里，合并对象和数组时，对象被自动包装成数组，因此，对象和原数组中的元素都被合并到一个新数组中。

MySQL JSON_MERGE_PATCH() 函数

MySQL JSON_MERGE_PATCH() 函数对两个或多个 JSON 文档执行替换合并并返回合并的结果。

此函数和 JSON_MERGE_PRESERVE()用法相同，但是合并逻辑有所不同。

JSON_MERGE_PATCH() 语法

这里是 MySQL JSON_MERGE_PATCH() 的语法：

JSON_MERGE_PATCH(json1, json2, ...)

参数

json1

必需的。一个 JSON 对象文档。
json2

必需的。一个 JSON 对象文档。

返回值

MySQL JSON_MERGE_PATCH() 函数返回一个由参数指定的多个 JSON 文档合并后的 JSON 文档。JSON_MERGE_PATCH() 执行的是替换合并，即在相同键值时，只保留后面的值。合并的规则如下：

如果第一个参数不是对象，则合并的结果与第二个参数合并空对象的结果相同。
如果第二个参数不是对象，则合并的结果为第二个参数。
如果两个参数都是对象，则合并的对象具有以下成员：
- 只存在于第一个对象中的成员
- 只存在于第二个对象中且值不是 null 的成员
- 存在于第二个对象且值不是 null ，并且在第一个对象中有对应的相同键的成员

也就是说，只有两个对象合并的结果才是对象。如果两个参数为不同的 JSON 类型或者都不是 JSON 对象，则合并结果是第二个参数。

如果任意一个参数为 NULL， JSON_MERGE_PATCH() 函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。

JSON_MERGE_PATCH() 示例

这里列出了几个常见的 JSON_MERGE_PATCH() 用法示例。

非 JSON 对象类型合并

下面的演示了如何使用 JSON_MERGE_PATCH() 函数合并两个非 JSON 对象类型的 JSON 文档。

SELECT JSON_MERGE_PATCH('2', 'true') as `2 + true`, JSON_MERGE_PATCH('true', '2') as `true + 2`, JSON_MERGE_PATCH('[1, 2]', '2') as `[1, 2] + 2`, JSON_MERGE_PATCH('2', '[1, 2]') as `2 + [1, 2]`, JSON_MERGE_PATCH('[1, 2]', '[2, 3]') as `[1, 2] + [2, 3]`; +----------+----------+------------+------------+-----------------+ | 2 + true | true + 2 | [1, 2] + 2 | 2 + [1, 2] | [1, 2] + [2, 3] | +----------+----------+------------+------------+-----------------+ | true | 2 | 2 | [1, 2] | [2, 3] | +----------+----------+------------+------------+-----------------+

这里我们看到，两个参数都不是 JSON 对象，JSON_MERGE_PATCH() 函数返回第二个参数。

合并对象

下面的演示了如何使用 JSON_MERGE_PATCH() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}'); +----------------------------------------------------------+ | JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "y": 3}') | +----------------------------------------------------------+ | {"x": 2, "y": 3, "z": 7} | +----------------------------------------------------------+

若第二个参数中存在值为 null 的键，则该键不会出现结果对象中。

SELECT JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}'); +-------------------------------------------------------------+ | JSON_MERGE_PATCH('{"x": 1, "z": 7}', '{"x": 2, "z": null}') | +-------------------------------------------------------------+ | {"x": 2} | +-------------------------------------------------------------+

MySQL JSON_MERGE_PRESERVE() 函数

MySQL JSON_MERGE_PRESERVE() 函数合并两个或多个 JSON 文档并返回合并的结果。

此函数和 JSON_MERGE_PATCH()用法相同，但是合并逻辑有所不同。

JSON_MERGE_PRESERVE() 语法

这里是 MySQL JSON_MERGE_PRESERVE() 的语法：

JSON_MERGE_PRESERVE(json1, json2, ...)

参数

json1

必需的。一个 JSON 对象文档。
json2

必需的。一个 JSON 对象文档。

返回值

MySQL JSON_MERGE_PRESERVE() 函数返回一个由参数指定的多个 JSON 文档合并后的 JSON 文档。JSON_MERGE_PRESERVE() 按照如下规则合并多个 JSON 文档：

两个数组合并为一个数组，保留所有数组中的元素。
两个对象合并为一个对象，保留所有的键和值。
一个纯值会被包装成一个数组并作为数组进行合并
对象和数组合并时，会将对象包装到一个数组中并作为数组进行合并。

如果任意一个参数为 NULL， JSON_MERGE_PRESERVE() 函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。

JSON_MERGE_PRESERVE() 示例

这里列出了几个常见的 JSON_MERGE_PRESERVE() 用法示例。

合并数组

下面的演示了如何使用 JSON_MERGE_PRESERVE() 函数合并两个或多个 JSON 数组。

SELECT JSON_MERGE_PRESERVE('[1, 2]', '[2, 3]', '[3, 4, 5]'); +------------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '[2, 3]', '[3, 4, 5]') | +------------------------------------------------------+ | [1, 2, 2, 3, 3, 4, 5] | +------------------------------------------------------+

从结果我们可以看出所有数组中的所有元素都被保留下来，不管元素是否重复。并且元素的顺序保持和参数的顺序一致。

合并对象

下面的演示了如何使用 JSON_MERGE_PRESERVE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE_PRESERVE('{"x": 1}', '{"x": 2, "y": 3}'); +-----------------------------------------------------+ | JSON_MERGE_PRESERVE('{"x": 1}', '{"x": 2, "y": 3}') | +-----------------------------------------------------+ | {"x": [1, 2], "y": 3} | +-----------------------------------------------------+

这里，因为 “x”: 1 和 “x”: 2 的键都是 “x”，因此他们的值合并到一个数组中，即： [1, 2]。

合并纯值

下面的演示了如何使用 JSON_MERGE_PRESERVE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE_PRESERVE('1', 'true', '"hello"', 'null'); +-----------------------------------------------------+ | JSON_MERGE_PRESERVE('1', 'true', '"hello"', 'null') | +-----------------------------------------------------+ | [1, true, "hello", null] | +-----------------------------------------------------+

这里，纯值在合并过程中包装成数组处理，因此，他们最终合并到一个数组中。

合并数组和对象

下面的演示了如何使用 JSON_MERGE_PRESERVE() 函数合并两个或多个 JSON 对象。

SELECT JSON_MERGE_PRESERVE('{"x": 1}', '[1, 2]'); +-------------------------------------------+ | JSON_MERGE_PRESERVE('{"x": 1}', '[1, 2]') | +-------------------------------------------+ | [{"x": 1}, 1, 2] | +-------------------------------------------+

这里，合并对象和数组时，对象被自动包装成数组，因此，对象和原数组中的元素都被合并到一个新数组中。

MySQL JSON_OBJECT() 函数

MySQL JSON_OBJECT() 函数返回一个包含了由参数指定的所有键值对的 JSON 对象。

JSON_OBJECT() 语法

这里是 MySQL JSON_OBJECT() 的语法：

JSON_OBJECT(key, value[, key2, value2, ...])

参数

key

必需的。对象中的键。
value

必需的。对象中的 key 的值。

返回值

JSON_OBJECT() 函数评估参数中的所有的键值对，并返回一个包含了所有键值对的 JSON 对象。

由于 JSON 对象中的所有键为字符串，因此 JSON_OBJECT() 会将不是字符串类型的 key 转为字符串类型。为了保证程序的稳定性，我们一般使用字符串类型的 key.

执行过程中可能会出现如下错误：

如果 key 是 NULL， MySQL 将返回错误： ERROR 3158 (22032): JSON documents may not contain NULL member names.。
如果由奇数个参数， MySQL 将返回错误： ERROR 1582 (42000): Incorrect parameter count in the call to native function ‘json_object’。

JSON_OBJECT() 示例

这里列出了几个常见的 JSON_OBJECT() 示例。

示例 1

SELECT JSON_OBJECT('name', 'Jim', 'age', 20); +---------------------------------------+ | JSON_OBJECT('name', 'Jim', 'age', 20) | +---------------------------------------+ | {"age": 20, "name": "Jim"} | +---------------------------------------+

这里， JSON_OBJECT() 返回的 JSON 对象中由两个成员： name 和 age。其中 name 的值为 ‘Jim’， age 的值为 20。

重复的键

如果 JSON_OBJECT() 的参数中出现了重复的键值对，那么后面的键值对保留在最终返回的对象中。

SELECT JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim'); +------------------------------------------------------+ | JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim') | +------------------------------------------------------+ | {"age": 20, "name": "Tim"} | +------------------------------------------------------+

包含复杂的对象

复杂的 JSON 对象可以存储更多的信息。

SELECT JSON_OBJECT( 'name', 'Tim', 'age', 20, 'friend', JSON_OBJECT('name', 'Jim', 'age', 20), 'hobby', JSON_ARRAY('games', 'sports') ) AS object; +------------------------------------------------------------------------------------------------+ | object | +------------------------------------------------------------------------------------------------+ | {"age": 20, "name": "Tim", "hobby": ["games", "sports"], "friend": {"age": 20, "name": "Jim"}} | +------------------------------------------------------------------------------------------------+

这里，我们创建了如下 JSON 对象：

{ "age": 20, "name": "Tim", "hobby": ["games", "sports"], "friend": { "age": 20, "name": "Jim" } }

其中：

hobby 的值是个数组，通过 JSON_ARRAY()函数计算得出.
friend 的值是个对象，通过 JSON_OBJECT() 函数计算得出.

MySQL JSON_OVERLAPS() 函数

MySQL JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。

JSON_OVERLAPS() 语法

这里是 MySQL JSON_OVERLAPS() 的语法：

JSON_OVERLAPS(json1, json2)

参数

json1

必需的。一个 JSON 文档。
json2

必需的。另一个 JSON 文档。

返回值

JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。如果两个 JSON 文档有重叠的内容，JSON_OVERLAPS() 函数返回 1，否则返回 0。

JSON_OVERLAPS() 函数按照如下规则比较两个 JSON 文档：

比较两个数组时，如果两个数组至少有一个相同的元素返回 1，否则返回 0。
比较两个对象时，如果两个对象至少有一个相同的键值对返回 1，否则返回 0。
比较两个纯值时，如果两个值相同返回 1，否则返回 0。
比较纯值和数组时，如果值是这个数组中的直接元素返回 1，否则返回 0。
比较纯值和对象的结果为 0。
比较数组和对象的结果为 0。
JSON_OVERLAPS() 不会对参数的数据类型进行转换。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。

JSON_OVERLAPS() 示例

这里列出了几个常见的 JSON_OVERLAPS() 用法示例。

比较数组

SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]'); +-----------------------------------------+ | JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]') | +-----------------------------------------+ | 1 | +-----------------------------------------+

这里，由于两个数组都有共同的元素 3，因此 JSON_OVERLAPS() 返回了 1。也就是说 [1, 2, 3] 和 [3, 4, 5] 有重叠。

让我们再看一个例子：

SELECT JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]'); +-------------------------------------------+ | JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]') | +-------------------------------------------+ | 0 | +-------------------------------------------+

这里，由于 [1, 2, [3]] 中的元素 [3] 和 [3, 4, 5] 中的 3 是不同的，因此 JSON_OVERLAPS() 返回了 0。也就是说 [1, 2, [3]] 和 [3, 4, 5] 没有交集。

比较对象

SELECT JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'), JSON_OVERLAPS('{"x": 1}', '{"y": 2}'); +-----------------------------------------------+---------------------------------------+ | JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}') | JSON_OVERLAPS('{"x": 1}', '{"y": 2}') | +-----------------------------------------------+---------------------------------------+ | 1 | 0 | +-----------------------------------------------+---------------------------------------+

这里， {“x”: 1} 和 {“x”: 1, “y”: 2} 都有共同的键值对 “x”: 1，因此 JSON_OVERLAPS() 返回了 1。而 {“x”: 1} 和 {“y”: 2} 没有共同的键值对，因此 JSON_OVERLAPS() 返回了 0。

比较纯值和数组

SELECT JSON_OVERLAPS('[1, 2, 3]', '3'), JSON_OVERLAPS('[1, 2, [3]]', '3'); +---------------------------------+-----------------------------------+ | JSON_OVERLAPS('[1, 2, 3]', '3') | JSON_OVERLAPS('[1, 2, [3]]', '3') | +---------------------------------+-----------------------------------+ | 1 | 0 | +---------------------------------+-----------------------------------+

这里，3 是 [1, 2, 3] 的元素，而不是 [1, 2, [3]] 的元素，因此他们返回了不同的结果。

比较纯值

SELECT JSON_OVERLAPS('1', '1'), JSON_OVERLAPS('1', '"1"'); +-------------------------+---------------------------+ | JSON_OVERLAPS('1', '1') | JSON_OVERLAPS('1', '"1"') | +-------------------------+---------------------------+ | 1 | 0 | +-------------------------+---------------------------+

这里，由于 1 和 “1” 是不同类型的数据，因此 JSON_OVERLAPS() 返回了 0。

MySQL JSON_PRETTY() 函数

MySQL JSON_PRETTY() 函数格式化输出一个 JSON 文档，以便更易于阅读。

JSON_PRETTY() 语法

这里是 MySQL JSON_PRETTY() 的语法：

JSON_PRETTY(json)

参数

json

必需的。一个 JSON 文档或 JSON 类型的值。

返回值

JSON_PRETTY() 函数格式化输出一个 JSON 文档，以便更易于阅读。

JSON_PRETTY() 函数按照如下美化输出 JSON 文档：

每个数组元素或每个对象成员都显示在单独的行上，与其父级相比，缩进一个附加级别。
每个缩进级别都会添加两个前导空格。
分隔各个数组元素或对象成员的逗号打印在分隔两个元素或成员的换行符之前。
对象成员的键和值由冒号后跟空格分隔（:）。
空对象或数组打印在一行上。左括号和右括号之间未打印空格。
字符串标量和键名中的特殊字符使用与 JSON_QUOTE()函数相同的规则进行转义。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。

JSON_PRETTY() 示例

这里列出了几个常见的 JSON_PRETTY() 用法示例。

格式化输出数组

SELECT JSON_PRETTY('[1, 2, 3]'); +--------------------------+ | JSON_PRETTY('[1, 2, 3]') | +--------------------------+ | [ 1, 2, 3 ] | +--------------------------+

格式化输出对象

SELECT JSON_PRETTY('{"x": 1, "y": 2}'); +---------------------------------+ | JSON_PRETTY('{"x": 1, "y": 2}') | +---------------------------------+ | { "x": 1, "y": 2 } | +---------------------------------+

格式化输出复杂对象

SELECT JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}'); +------------------------------------------------------------------------------------------+ | JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}') | +------------------------------------------------------------------------------------------+ | { "x": 1, "y": [ 1, 2, 3 ], "z": { "a": "a", "b": true } } | +------------------------------------------------------------------------------------------+

MySQL JSON_QUOTE() 函数

MySQL JSON_QUOTE() 函数使用双引号包装一个值，使其成为一个 JSON 字符串值。

JSON_QUOTE() 语法

这里是 MySQL JSON_QUOTE() 的语法：

JSON_QUOTE(str)

参数

str

必需的。一个字符串。

返回值

JSON_QUOTE() 函数返回一个使用双引号包围的 JSON 字符串值。

如果参数为 NULL，JSON_QUOTE() 函数返回 NULL。

下表中的特殊字符将使用反斜杠转义：

转义序列	序列表示的字符
"	双引号 "
b	退格字符
f	换页符
n	换行符
r	回车符
t	制表符
	反斜杠
uXXXX	Unicode 值 XXXX 的 UTF-8 字节

JSON_QUOTE() 示例

这里列出了几个常见的 JSON_QUOTE() 示例。

MySQL JSON_REMOVE() 函数

MySQL JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

JSON_REMOVE() 语法

这里是 MySQL JSON_REMOVE() 的语法：

JSON_REMOVE(json, path[, path] ...)

参数

json

必需的。一个 JSON 文档。
path

必需的。一个有效的路径表达式，它不能包含 * 或 **。

返回值

JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

您可以通过参数提供多个路径表达式以供删除。多个路径参数会从左到右依次被执行。当执行下一个参数的时候，JSON 文档可能已经发生了变化。

如果 JSON 中不存在指定的路径，此函数返回原文档。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID()验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式或者等于 $ 或者其中包含 * 或 **, MySQL 将会给出错误。

JSON_REMOVE() 示例

从数组中删除

下面语句使用 JSON_REMOVE() 从一个 JSON 数组中删除索引为 0 和 3 的元素。

SELECT JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]'); +--------------------------------------------------+ | JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]') | +--------------------------------------------------+ | [1, 2] | +--------------------------------------------------+

这里，您可能会感到迷惑，为什么使用 $[2] 来删除索引位置 3 的数据？这是因为当存在多个路径的时候，JSON_REMOVE() 从左到右依次执行，步骤如下：

首先，执行 JSON_REMOVE(’[0, 1, 2, [3, 4]]’, ‘$[0]’)，返回了 JSON 文档 [1, 2, [3, 4]]。
然后，执行 JSON_REMOVE(’[1, 2, [3, 4]]’, ‘$[2]’)，返回了 JSON 文档 [1, 2]。

从对象中删除

下面语句使用 JSON_REMOVE() 从一个 JSON 对象中删除一个成员。

SELECT JSON_REMOVE('{"x": 1, "y": 2}', '$.x'); +----------------------------------------+ | JSON_REMOVE('{"x": 1, "y": 2}', '$.x') | +----------------------------------------+ | {"y": 2} | +----------------------------------------+

MySQL JSON_REPLACE() 函数

MySQL JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。

JSON_REPLACE() 语法

这里是 MySQL JSON_REPLACE() 的语法：

JSON_REPLACE(json, path, value[, path2, value2] ...)

参数

json

必需的。被修改的 JSON 文档。
path

必需的。一个有效的路径表达式，它不能包含 * 或 **。
value

必需的。新的数据。

返回值

JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。您可以提供多对 path-value 参数，以便一次替换多个数据。

JSON_REPLACE() 函数只能替换已经存在的数据。如果 JSON 文档中不存在指定的路径，则不会插入数据。

如果 value 为字符串， JSON_REPLACE() 函数会将其作为字符串写入到 JSON 文档中。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

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

JSON_REPLACE() 示例

在数组中替换

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @array = '[1, [2, 3]]';

让我们将数组的第一个元素和第三个元素替换为新值：

SELECT JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6);

这里，我们想要把数组的将第一个元素替换为 0，将数组的第三个元素替换为 6。让我们看一下结果：

+--------------------------------------------+ | JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6) | +--------------------------------------------+ | [0, [2, 3]] | +--------------------------------------------+

我们发现，数组的第一个元素成功的被替换为了 0。然后，数组的最后没有插入 6。这是因为 JSON_REPLACE() 函数只替换已经存在的数据。

写入 JSON 类型数据

让我们首先创建一个包含 JSON 对象的 JSON 文档：

SET @obj = '{"x": 1}';

现在让我们将对象中的 x 的成员修改为 true:

SELECT JSON_REPLACE(@obj, '$.x', 'true'); +-----------------------------------+ | JSON_REPLACE(@obj, '$.x', 'true') | +-----------------------------------+ | {"x": "true"} | +-----------------------------------+

我们发现， true 变成了 “true”。而不是我们希望的 {“x”: true}。

这是因为，如果 value 参数为字符串， JSON_REPLACE() 函数会将其作为字符串写入到 JSON 文档中。我们再看几个相似的例子：

SELECT JSON_REPLACE(@obj, '$.x', '[1, 2]'); +-------------------------------------+ | JSON_REPLACE(@obj, '$.x', '[1, 2]') | +-------------------------------------+ | {"x": "[1, 2]"} | +-------------------------------------+

或者

SELECT JSON_REPLACE(@obj, '$.x', '{"z": 2}'); +---------------------------------------+ | JSON_REPLACE(@obj, '$.x', '{"z": 2}') | +---------------------------------------+ | {"x": "{"z": 2}"} | +---------------------------------------+

为了解决这个问题，我们可以使用 CAST() 函数将数据转为 JSON 类型，比如：

SELECT JSON_REPLACE(@obj, '$.x', CAST('{"z": 2}' AS JSON)); +-----------------------------------------------------+ | JSON_REPLACE(@obj, '$.x', CAST('{"z": 2}' AS JSON)) | +-----------------------------------------------------+ | {"x": {"z": 2}} | +-----------------------------------------------------+

MySQL JSON_SCHEMA_VALID() 函数

MySQL JSON_SCHEMA_VALID() 函数根据指定的 JSON 模式验证一个 JSON 文档，并返回 1 表是验证通过或者返回 0 表示验证不通过。

JSON_SCHEMA_VALID() 语法

这里是 MySQL JSON_SCHEMA_VALID() 的语法：

JSON_SCHEMA_VALID(schema, json_doc)

参数

schema

必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。
json_doc

必需的。被验证的 JSON 文档。

返回值

JSON_SCHEMA_VALID() 函数返回 1 或 0； 1 表示 JSON 文档通过了验证， 0 表示 JSON 文档没有通过验证。

如果任何一个参数为 NULL，此函数将返回 NULL。

JSON_SCHEMA_VALID() 示例

本示例展示了如何使用 JSON_SCHEMA_VALID() 函数验证一个 JSON 文档是否符合一个 JSON 模式。

首先，让我们创建一个 JSON 模式：

SET @schema = '{ "id": "http://json-schema.org/geo", "$schema": "http://json-schema.org/draft-04/schema#", "description": "A geographical coordinate", "type": "object", "properties": { "x": { "type": "number", "minimum": -90, "maximum": 90 }, "y": { "type": "number", "minimum": -180, "maximum": 180 } }, "required": ["x", "y"] }';

这里，我们创建了 JSON 模式，其中：

“type”: “object” 表示 JSON 文档必须是一个 JSON 对象。
“properties” 中定义了对象中的成员列表，以及每个成员的约束。这里的定义了两个成员：
- x - 数字类型，最大值是 90，最小值是 -90。
- y - 数字类型，最大值是 180，最小值是 -180。
“required”: [“x”, “y”] 定义了对象中必须有成员 x 和 y。

接着，让我们创建一个 JSON 文档：

SET @json_doc = '{"x": 1, "y": 2}';

然后，让我们验证 JSON 文档是否和 JSON 模式匹配：

SELECT JSON_SCHEMA_VALID(@schema, @json_doc); +---------------------------------------+ | JSON_SCHEMA_VALID(@schema, @json_doc) | +---------------------------------------+ | 1 | +---------------------------------------+

这说明 @json_doc 是符合 @schema 定义的。

如果我们改一下 JSON 文档，去掉了对象中的成员 y:

SET @json_doc = '{"x": 1}';

让我们再次验证 JSON 文档是否和 JSON 模式匹配：

这里，因为 JSON 模式中定义了 y 是必须的成员，所以 JSON_SCHEMA_VALID() 函数返回了 0.

除此之外，您还可以将 JSON_SCHEMA_VALID() 用于 JSON 字段的 CHECK约束上。

MySQL JSON_SCHEMA_VALIDATION_REPORT() 函数

MySQL JSON_SCHEMA_VALIDATION_REPORT() 函数根据指定的 JSON 模式验证一个 JSON 文档，并返回一个验证报告。

JSON_SCHEMA_VALIDATION_REPORT() 语法

这里是 MySQL JSON_SCHEMA_VALIDATION_REPORT() 的语法：

JSON_SCHEMA_VALIDATION_REPORT(schema, json_doc)

参数

schema

必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。
json_doc

必需的。被验证的 JSON 文档。

返回值

JSON_SCHEMA_VALIDATION_REPORT() 函数返回一个关于验证结果的报告，它是一个 JSON 对象。这个报告中包含如下成员：

valid: true 表示 JSON 文档通过了验证， false 表示 JSON 文档没有通过验证。
reason: 验证失败的原因。
schema-location: 一个 JSON 指针 URI 片段标识符，指示验证在 JSON shcema 中的位置
document-location: 一个 JSON 指针 URI 片段标识符，指示验证在 JSON 文档中失败的位置
schema-failed-keyword: 一个字符串，包含违反的 JSON shcema 中关键字或属性的名称

如果 JSON 文档通过验证，报告中只有 valid: true 这个一个成员。其他的成员只有验证失败时才会出现在报告中。

如果任何一个参数为 NULL，此函数将返回 NULL。

如果 schema 不是 JSON 对象或者 json_doc 不是有效的 JSON 文档，MySQL 会给出一个错误提示。

JSON_SCHEMA_VALIDATION_REPORT() 示例

本示例展示了如何使用 JSON_SCHEMA_VALIDATION_REPORT() 函数验证一个 JSON 文档是否符合一个 JSON 模式。

首先，让我们创建一个 JSON 模式：

这里，我们创建了 JSON 模式，其中：

“type”: “object” 表示 JSON 文档必须是一个 JSON 对象。
“properties” 中定义了对象中的成员列表，以及每个成员的约束。这里的定义了两个成员：
- x - 数字类型，最大值是 90，最小值是 -90。
- y - 数字类型，最大值是 180，最小值是 -180。
“required”: [“x”, “y”] 定义了对象中必须有成员 x 和 y。

接着，让我们创建一个 JSON 文档：

SET @json_doc = '{"x": 1, "y": 2}';

然后，让我们验证 JSON 文档是否和 JSON 模式匹配：

SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @json_doc) AS ReportG *************************** 1. row *************************** Report: {"valid": true}

这里，返回了 {“valid”: true} 说明了 JSON 文档和 JSON schema 是匹配的。

如果我们改一下 JSON 文档，将 y 的值修改为 200:

SET @json_doc = '{"x": 1, "y": 200}';

让我们再次验证 JSON 文档是否和 JSON 模式匹配：

SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @json_doc)) AS ReportG *************************** 1. row *************************** Report: { "valid": false, "reason": "The JSON document location '#/y' failed requirement 'maximum' at JSON Schema location '#/properties/y'", "schema-location": "#/properties/y", "document-location": "#/y", "schema-failed-keyword": "maximum" }

因为在 JSON schema 中限定了 y 的范围是 -180 到 180，而现在 y 的值为 200，所以验证失败了。

注意：为了更易于阅读，这里对报告使用了 JSON_PRETTY() 函数进行了美化。

MySQL JSON_SEARCH() 函数

MySQL JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。

JSON_SEARCH() 语法

这里是 MySQL JSON_SEARCH() 的语法：

JSON_SEARCH(json, one_or_all, search_str) JSON_SEARCH(json, one_or_all, search_str, escape_char) JSON_SEARCH(json, one_or_all, search_str, escape_char, path)

参数

json

必需的。一个 JSON 文档。
one_or_all

必需的。可用值：‘one’, ‘all’。 JSON_SEARCH() 根据 one_or_all 参数决定是否返回所有匹配的路径：
- 如果是 ‘one’，JSON_SEARCH() 函数将返回第一个匹配的路径。
- 如果是 ‘all’，JSON_SEARCH() 函数将返回所有匹配的路径。所有的路径会包装在一个数组内返回。
search_str

必需的。被搜索的字符串。您可以在 search_str 参数中使用 % 和 _ 通配符，就像 LIKE 一样：
- % 匹配任意数量的任意字符。
- _ 匹配一个任意字符。
escape_char

可选的。如果 search_str 中包含 % 和 _，需要在他们之前添加转移字符。默认是。
path

可选的。只能在此路径下进行搜索。

返回值

JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。它返回一个路径字符串或者由多个路径组成的数组。

JSON_SEARCH() 函数将在以下情况下返回 NULL：

未搜索到指定的字符串
JSON 文档中不存在指定的 path
任意一个参数为 NULL

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

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

JSON_SEARCH() 示例

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

SET @json = '[ { "name": "Tim", "age": 20, "hobbies": [ { "name": "Car", "weight": 10 }, { "name": "Sports", "weight": 20 } ] }, { "name": "Tom", "age": 20, "hobbies": [ { "name": "Reading", "weight": 10 }, { "name": "Sports", "weight": 20 } ] } ]';

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

示例: 搜索字符串

SELECT JSON_SEARCH(@json, 'one', 'Tim'); +----------------------------------+ | JSON_SEARCH(@json, 'one', 'Tim') | +----------------------------------+ | "$[0].name" | +----------------------------------+

示例: one vs all

SELECT JSON_SEARCH(@json, 'one', 'Sports'), JSON_SEARCH(@json, 'all', 'Sports'); +-------------------------------------+--------------------------------------------------+ | JSON_SEARCH(@json, 'one', 'Sports') | JSON_SEARCH(@json, 'all', 'Sports') | +-------------------------------------+--------------------------------------------------+ | "$[0].hobbies[1].name" | ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] | +-------------------------------------+--------------------------------------------------+

示例: 使用通配符

SELECT JSON_SEARCH(@json, 'all', 'S%'); +--------------------------------------------------+ | JSON_SEARCH(@json, 'all', 'S%') | +--------------------------------------------------+ | ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] | +--------------------------------------------------+

MySQL JSON_SET() 函数

MySQL JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。它相当于是 JSON_INSERT()和 JSON_REPLACE()的组合。

JSON_SET() 语法

这里是 MySQL JSON_SET() 的语法：

JSON_SET(json, path, value[, path2, value2] ...)

参数

json

必需的。被修改的 JSON 文档。
path

必需的。一个有效的路径表达式，它不能包含 * 或 **。
value

必需的。要设置的数据。

返回值

JSON_SET() 函数在一个 JSON 文档中插入或更新数据并返回新的 JSON 文档。您可以提供多对 path-value 参数，以便一次设定多个数据。

如果 JSON 文档中存在参数中指定的路径，则更新路径匹配的值；如果不存在，则在对应的路径上插入数据。

如果 value 为字符串， JSON_SET() 函数会将其作为字符串写入到 JSON 文档中。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

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

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

JSON_SET() 示例

让我们首先创建一个包含 JSON 对象的 JSON 文档：

SET @obj = '{"x": 1}';

现在让我们设置一些数据:

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]'); +----------------------------------------------+ | JSON_SET(@obj, '$.x', '10', '$.y', '[1, 2]') | +----------------------------------------------+ | {"x": "10", "y": "[1, 2]"} | +----------------------------------------------+

我们发现，虽然已经设置成功，但还有些小问题，就是数组 [1, 2] 变成了 “[1, 2]”。

这是因为，如果 value 参数为字符串， JSON_SET() 函数会将其作为字符串写入到 JSON 文档中。我们再看一个相似的例子：

SELECT JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}'); +------------------------------------------------+ | JSON_SET(@obj, '$.x', '10', '$.y', '{"z": 2}') | +------------------------------------------------+ | {"x": "10", "y": "{"z": 2}"} | +------------------------------------------------+

为了解决这个问题，我们可以使用 CAST()函数将数据转为 JSON 类型，比如：

SELECT JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)); +------------------------------------------------------------+ | JSON_SET(@obj, '$.x', '10', '$.y', CAST('[1, 2]' AS JSON)) | +------------------------------------------------------------+ | {"x": "10", "y": [1, 2]} | +------------------------------------------------------------+

MySQL JSON_STORAGE_FREE() 函数

MySQL JSON_STORAGE_FREE() 函数返回一个 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所释放的空间。

JSON_STORAGE_FREE() 语法

这里是 MySQL JSON_STORAGE_FREE() 的语法：

JSON_STORAGE_FREE(json)

参数

json

必需的。一个 JSON 文档。它可以是一个 JSON 字符串，或者一个 JSON 列。

返回值

MySQL JSON_STORAGE_FREE() 函数返回一个 JSON 列在被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新后所释放的空间。它可以接受一个 JSON 字符串，或者一个 JSON 列作为参数。

如果参数是一个 JSON 字符串，JSON_STORAGE_FREE() 函数返回 0。

如果参数是一个 JSON 列，JSON_STORAGE_FREE() 函数按如下规则返回：

如果列被 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 部分更新，它返回更新后释放的空间。
如果列没有被更新过，或者不是使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE()部分更新，它返回 0.
如果列的内容在更新后变的更大了，它返回 0.

如果参数为 NULL， JSON_STORAGE_FREE() 函数返回 NULL。

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

JSON_STORAGE_FREE() 示例

本示例说明了 JSON_STORAGE_FREE() 的用法，以及它和 JSON_STORAGE_SIZE()的区别。

首先，让我们先创建一个表 test_json_storage_free：

DROP TABLE IF EXISTS test_json_storage_free; CREATE TABLE test_json_storage_free ( json_col JSON NOT NULL );

然后，让我们插入 1 行数据以供测试使用：

INSERT INTO test_json_storage_free VALUES ('{"x": 1, "y": "abcd"}');

然后，让我们看一下 JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 的返回值：

SELECT json_col, JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE, JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE FROM test_json_storage_free; +-----------------------+-------------------+-------------------+ | json_col | JSON_STORAGE_SIZE | JSON_STORAGE_FREE | +-----------------------+-------------------+-------------------+ | {"x": 1, "y": "abcd"} | 26 | 0 | +-----------------------+-------------------+-------------------+

我们可以看到，由于数据刚刚插入，也未进行任何更新，因此 JSON_STORAGE_FREE() 函数返回了 0，而 JSON_STORAGE_SIZE 函数返回了占用的空间。

接着，让我们修改一下 JSON 列的值：

UPDATE test_json_storage_free SET json_col = '{"x": 1, "y": "abcd", "z": 3}';

然后，让我们看一下 JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 的返回值：

SELECT json_col, JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE, JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE FROM test_json_storage_free; +-------------------------------+-------------------+-------------------+ | json_col | JSON_STORAGE_SIZE | JSON_STORAGE_FREE | +-------------------------------+-------------------+-------------------+ | {"x": 1, "y": "abcd", "z": 3} | 34 | 0 | +-------------------------------+-------------------+-------------------+

我们可以看到，由于 json_col 列的内容变了，因此 JSON_STORAGE_SIZE() 返回值发生了变化。但是 JSON_STORAGE_FREE() 函数仍然返回了 0，这是因为没有使用 JSON_SET()、JSON_REPLACE()或 JSON_REMOVE()更新 json_col 列。

然后，让我们使用 JSON_REMOVE() 删除 json_col 列中的成员 z：

UPDATE test_json_storage_free SET json_col = JSON_REMOVE(json_col, '$.z');

然后，让我们看一下 JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 的返回值：

SELECT json_col, JSON_STORAGE_SIZE(json_col) AS JSON_STORAGE_SIZE, JSON_STORAGE_FREE(json_col) AS JSON_STORAGE_FREE FROM test_json_storage_free; +-----------------------+-------------------+-------------------+ | json_col | JSON_STORAGE_SIZE | JSON_STORAGE_FREE | +-----------------------+-------------------+-------------------+ | {"x": 1, "y": "abcd"} | 34 | 8 | +-----------------------+-------------------+-------------------+

我们可以看到，由于使用 JSON_REPLACE() 更新了 json_col 列的内容，因此 JSON_STORAGE_FREE() 函数返回了 8。

MySQL JSON_STORAGE_SIZE() 函数

MySQL JSON_STORAGE_SIZE() 函数返回存储一个 JSON 文档的二进制表示所占用的字节数。

JSON_STORAGE_SIZE() 语法

这里是 MySQL JSON_STORAGE_SIZE() 的语法：

JSON_STORAGE_SIZE(json)

参数

json

必需的。一个 JSON 文档。它可以是一个 JSON 字符串，或者一个 JSON 列。

返回值

MySQL JSON_STORAGE_SIZE() 函数返回存储一个 JSON 文档的二进制表示所占用的字节数。它可以接受一个 JSON 字符串，或者一个 JSON 列作为参数。

如果参数是一个 JSON 字符串，JSON_STORAGE_SIZE() 函数返回由 JSON 字符串解析成的 JSON 类型的值存储时所占用的字节数。

如果参数是一个 JSON 列，JSON_STORAGE_SIZE() 函数返回插入到列中的 JSON 文档所占用的存储空间。当然，这个数字可能随着以后更新而变化。

如果参数为 NULL， JSON_STORAGE_SIZE() 函数返回 NULL。

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

JSON_STORAGE_SIZE() 示例

示例: 数字

SELECT 'Size' AS `Json`, JSON_STORAGE_SIZE('0') AS `0`, JSON_STORAGE_SIZE('1') AS `1`, JSON_STORAGE_SIZE('1000') AS `1000`, JSON_STORAGE_SIZE('100000') AS `100000`; +------+---+---+------+--------+ | Valu | 0 | 1 | 1000 | 100000 | +------+---+---+------+--------+ | Size | 3 | 3 | 3 | 5 | +------+---+---+------+--------+

示例: 字符串

SELECT 'Size' AS `Json`, JSON_STORAGE_SIZE('"a"') AS `a`, JSON_STORAGE_SIZE('"Hello World"') AS `Hello World`; +------+---+-------------+ | Json | a | Hello World | +------+---+-------------+ | Size | 3 | 13 | +------+---+-------------+

示例: 布尔值

SELECT 'Size' AS `Json`, JSON_STORAGE_SIZE('true') AS `true`, JSON_STORAGE_SIZE('false') AS `false`; +------+------+-------+ | Json | true | false | +------+------+-------+ | Size | 2 | 2 | +------+------+-------+

从结果我们看出，存储 JSON 布尔值 true 或者 false 占用 2 个字节。

示例: null

SELECT 'Size' AS `Json`, JSON_STORAGE_SIZE('null') AS `null`; +------+------+ | Json | null | +------+------+ | Size | 2 | +------+------+

从结果我们看出，存储 JSON null 值占用 2 个字节。

示例: 数组和对象

SELECT 'Size' AS `Json`, JSON_STORAGE_SIZE('[1, 2]') AS `[1, 2]`, JSON_STORAGE_SIZE('[1, 2, 3]') AS `[1, 2, 3]`, JSON_STORAGE_SIZE('{"x": 1}') AS `{"x": 1}`, JSON_STORAGE_SIZE('{"x": 1, "y": 2}') AS `{"x": 1, "y": 2}`; +------+--------+-----------+----------+------------------+ | Json | [1, 2] | [1, 2, 3] | {"x": 1} | {"x": 1, "y": 2} | +------+--------+-----------+----------+------------------+ | Size | 11 | 14 | 13 | 21 | +------+--------+-----------+----------+------------------+

示例：字段

本示例说明了如何使用 JSON_STORAGE_SIZE() 函数计算一个 JSON 列占用的空间。

首先，让我们先创建一个表 test_json_storage_size：

DROP TABLE IF EXISTS test_json_storage_size; CREATE TABLE test_json_storage_size ( json_col JSON NOT NULL );

然后，让我们插入 1 行数据以供测试使用：

INSERT INTO test_json_storage_size VALUES ('{"x": 1, "y": 2}');

然后，让我们使用 JSON_STORAGE_SIZE() 函数查看 json_col 占用的存储空间：

SELECT json_col, JSON_STORAGE_SIZE(json_col) FROM test_json_storage_size; +------------------+-----------------------------+ | json_col | JSON_STORAGE_SIZE(json_col) | +------------------+-----------------------------+ | {"x": 1, "y": 2} | 21 | +------------------+-----------------------------+

接着，让我们修改一下 JSON 列的值：

UPDATE test_json_storage_size SET json_col = '{"x": 1, "y": 2, "z": 3}';

最后，让我们 JSON_STORAGE_SIZE() 函数查看 json_col 占用的存储空间：

SELECT json_col, JSON_STORAGE_SIZE(json_col) FROM test_json_storage_size; +--------------------------+-----------------------------+ | json_col | JSON_STORAGE_SIZE(json_col) | +--------------------------+-----------------------------+ | {"x": 1, "y": 2, "z": 3} | 29 | +--------------------------+-----------------------------+

我们发现 JSON_STORAGE_SIZE() 返回值发生了变化，这是因为 JSON 字段的值已经发生了变化。

MySQL JSON_TABLE() 函数

MySQL JSON_TABLE() 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。

JSON_TABLE() 语法

这里是 MySQL JSON_TABLE() 的语法：

JSON_TABLE( json, path COLUMNS (column[, column[, ...]]) )


column:

    name FOR ORDINALITY

    |  name type PATH string_path [on_empty] [on_error]

    |  name type EXISTS PATH string_path

    |  NESTED [PATH] path COLUMNS (column[, column[, ...]])
on_empty:

    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error: {NULL | DEFAULT json_string | ERROR} ON ERROR

参数

json

必需的。一个 JSON 文档。
path

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

必需的。定义一个列。您可以使用如下 4 中方式定义一个列：
- name FOR ORDINALITY: 生成一个从 1 开始的计数器列，名字为 name。
- name type PATH string_path [on_empty] [on_error]: 将由路径表达式 string_path 指定的值放在名字为 name 的列中。
- name type EXISTS PATH string_path：根据 string_path 指定的位置是否有值将 1 或 0 放在名字为 name 的列中。
- NESTED [PATH] path COLUMNS (column[, column[, …]]): 将内嵌的对象或者数组中的数据拉平放在一行中。
{NULL | ERROR | DEFAULT value} ON EMPTY

可选的。如果指定了，它决定了指定路径下没有数据时的返回值：
- NULL ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将使用 NULL，这是默认的行为。
- DEFAULT value ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将使用 value。
- ERROR ON EMPTY: 如果指定路径下没有数据，JSON_TABLE() 函数将抛出一个错误。
{NULL | ERROR | DEFAULT value} ON ERROR

可选的。如果指定了，它决定了处理错误的逻辑：
- NULL ON ERROR: 如果有错误，JSON_TABLE() 函数将使用 NULL，这是默认的行为。
- DEFAULT value ON ERROR: 如果有错误，JSON_TABLE() 函数将使用 value。
- ERROR ON ERROR: 如果有错误，JSON_TABLE() 函数将抛出一个错误。

返回值

MySQL JSON_TABLE() 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。您可以像普通的表一样使用 JSON_TABLE() 返回的表。

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

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

JSON_TABLE() 示例

简单示例

在本例中，将数组中的每个对象元素转为一个关系表中的每行。关系表中的列对应了每个对象中的成员。

SELECT * FROM JSON_TABLE( '[{"x": 10, "y": 11}, {"x": 20, "y": 21}]', '$[*]' COLUMNS ( id FOR ORDINALITY, x INT PATH '$.x', y INT PATH '$.y' ) ) AS t; +------+------+------+ | id | x | y | +------+------+------+ | 1 | 10 | 11 | | 2 | 20 | 21 | +------+------+------+

这里， JSON 数组中有两个对象：{“x”: 10, “y”: 11} 和 {“x”: 20, “y”: 21}。

路径表达式 $[*] 则表示数组中的每个元素，也就是数组中的那两个对象。

COLUMNS 子句定义了关系表中的 3 个列：

id FOR ORDINALITY: 列名为 id，列的内容为从 1 开始的自增序列。
x INT PATH '$.x: 列名为 x，列的内容是对应了对象中的成员 x。
y INT PATH '$.y: 列名为 y，列的内容是对应了对象中的成员 y。

其中 $.x 和 $.y 中的 $ 代表了当前的上下文对象，也就是数组中的每个对象。

默认值

SELECT * FROM JSON_TABLE( '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]', '$[*]' COLUMNS ( id FOR ORDINALITY, x INT PATH '$.x' DEFAULT '100' ON EMPTY, y INT PATH '$.y' ) ) AS t; +------+------+------+ | id | x | y | +------+------+------+ | 1 | 10 | 11 | | 2 | 100 | 21 | | 3 | 30 | NULL | +------+------+------+

这里，注意下面的一行的列定义：

x INT PATH '$.x' DEFAULT '100' ON EMPTY,

其中 DEFAULT ‘100’ ON EMPTY 定义了当对象中不存在成员 x 或者 x 的值为空时要使用默认值 100。

提取指定的行

SELECT * FROM JSON_TABLE( '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]', '$[1]' COLUMNS ( x INT PATH '$.x', y INT PATH '$.y' ) ) AS t; +------+------+ | x | y | +------+------+ | NULL | 21 | +------+------+

这里，路径表达式 $[1] 指示了只提取 JSON 数组中的第 2 元素，也就是 {“y”: 21}。因此，SELECT 语句只返回一行。

拉平内嵌的数组

SELECT * FROM JSON_TABLE( '[{"x":10,"y":[11, 12]}, {"x":20,"y":[21, 22]}]', '$[*]' COLUMNS ( x INT PATH '$.x', NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$') ) ) AS t; +------+------+ | x | y | +------+------+ | 10 | 11 | | 10 | 12 | | 20 | 21 | | 20 | 22 | +------+------+

这里，数组中对象的成员 y 是个数组，注意下面的列定义：

NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')

这里使用了 NESTED PATH ‘$.y[*]’ 子句展开 y 对应的数组，并将 y 数组中的每个元素放入名称为 y 的列中。

因为每个 y 数组中都有 2 个元素，因此一个 y 数组转化为关系表中的两行。

拉平内嵌的对象

SELECT * FROM JSON_TABLE( '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]', '$[*]' COLUMNS ( x INT PATH '$.x', NESTED PATH '$.y' COLUMNS ( ya INT PATH '$.a', yb INT PATH '$.b' ) ) ) AS t; +------+------+------+ | x | ya | yb | +------+------+------+ | 10 | 11 | 12 | | 20 | 21 | 22 | +------+------+------+

这里使用了 NESTED PATH ‘$.y’ 子句将对象 y 中成员提取到 2 列：

成员 a 被提取到列 ya
成员 b 被提取到列 yb

MySQL JSON_TYPE() 函数

MySQL JSON_TYPE() 函数返回一个给定的 JSON 值的类型。

JSON_TYPE() 语法

这里是 MySQL JSON_TYPE() 的语法：

JSON_TYPE(json_value)

参数

json_value

必需的。一个 JSON 值。

返回值

JSON_TYPE() 函数返回一个 utf8mb4 字符串，它代表了给定的 JSON 值的类型。JSON_TYPE() 函数将返回下面值中的一个：

OBJECT: JSON 对象
ARRAY: JSON 数组
BOOLEAN: JSON 布尔值
NULL: JSON null 值
INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT,INT 和 BIGINT 类型的值
DOUBLE: MySQL DOUBLE和 FLOAT 类型的值
DECIMAL: MySQL DECIMAL 和 NUMERIC 类型的值
DATETIME: MySQL DATETIME 和 TIMESTAMP 类型的值
DATE: MySQL DATE 类型的值
TIME: MySQL TIME 类型的值
STRING: MySQL CHAR, VARCHAR, TEXT, ENUM 和 SET
BLOB: MySQL BINARY, VARBINARY, BLOB 和 BIT
OPAQUE: 以上之外的类型

如果参数为 NULL，此函数将返回 NULL。

如果参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_TYPE() 示例

这里列出了几个常见的 JSON_TYPE() 用法示例。

示例 1

示例 2: 数字

示例 3: 数组

示例 4: 对象

SELECT JSON_TYPE('{}'), JSON_TYPE('{"x": 1}'); +-----------------+-----------------------+ | JSON_TYPE('{}') | JSON_TYPE('{"x": 1}') | +-----------------+-----------------------+ | OBJECT | OBJECT | +-----------------+-----------------------+

MySQL JSON_UNQUOTE() 函数

MySQL JSON_UNQUOTE() 函数取消双引号引用 JSON 值，并将结果作为字符串返回。

JSON_UNQUOTE() 语法

这里是 MySQL JSON_UNQUOTE() 的语法：

JSON_UNQUOTE(json_val)

参数

json_val

必需的。一个字符串。

返回值

JSON_UNQUOTE() 函数取消双引号引用 JSON 值，并将结果作为字符串返回。

如果参数为 NULL，JSON_UNQUOTE() 函数返回 NULL。

如果值以双引号开头和结尾，但不是有效的 JSON 字符串文本，MySQL 将返回类似的错误： ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: xxx.。

MySQL 能够识别下表中的转义字符：

转义序列	序列表示的字符
"	双引号 "
b	退格字符
f	换页符
n	换行符
r	回车符
t	制表符
	反斜杠
uXXXX	Unicode 值 XXXX 的 UTF-8 字节

JSON_UNQUOTE() 示例

本示例展示了如何使用 JSON_UNQUOTE() 函数去除 JSON 字符串值两边的双引号。

SELECT JSON_UNQUOTE('"abc"'); +-----------------------+ | JSON_UNQUOTE('"abc"') | +-----------------------+ | abc | +-----------------------+

您也可以传入一个 JSON 类型的值，比如：

SELECT JSON_UNQUOTE(CAST('"abc"' AS JSON)); +-----------------------+ | JSON_UNQUOTE('"abc"') | +-----------------------+ | abc | +-----------------------+

这里，我们使用了 CAST() 函数将一个字符串转为 JSON 类型。

MySQL JSON_VALID() 函数

MySQL JSON_VALID() 函数返回 0 和 1 来指示给定的参数是否是一个有效的 JSON 文档。

JSON_VALID() 语法

这里是 MySQL JSON_VALID() 的语法：

JSON_VALID(str)

参数

str

必需的。需要被验证的内容。

返回值

JSON_VALID() 函数验证给定的参数是否是一个有效的 JSON 文档。如果给定的参数是有效的 JSON 文档，JSON_VALID() 函数返回 1，如果不是 JSON 文档，JSON_VALID() 函数返回 0。

如果参数为 NULL，此函数将返回 NULL。

JSON_VALID() 示例

这里列出了几个常见的 JSON_VALID() 用法示例。

示例 1

SELECT JSON_VALID(1), JSON_VALID('1'); +---------------+-----------------+ | JSON_VALID(1) | JSON_VALID('1') | +---------------+-----------------+ | 0 | 1 | +---------------+-----------------+

示例 2

SELECT JSON_VALID(true), JSON_VALID('true'); +------------------+--------------------+ | JSON_VALID(true) | JSON_VALID('true') | +------------------+--------------------+ | 0 | 1 | +------------------+--------------------+

示例 3

SELECT JSON_VALID('abc'), JSON_VALID('"abc"'); +-------------------+---------------------+ | JSON_VALID('abc') | JSON_VALID('"abc"') | +-------------------+---------------------+ | 0 | 1 | +-------------------+---------------------+

示例 4

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('{a: 1}'); +------------------------+----------------------+ | JSON_VALID('{"a": 1}') | JSON_VALID('{a: 1}') | +------------------------+----------------------+ | 1 | 0 | +------------------------+----------------------+

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 子句设定默认值：

MySQL MEMBER OF() 函数

MySQL MEMBER OF() 函数检查一个指定的值是否是一个 JSON 数组中的元素。

MEMBER OF() 语法

这里是 MySQL MEMBER OF() 的语法：

value MEMBER OF(json_array)

参数

value

必需的。一个值。它可以是任意类型。
json_array

必需的。一个 JSON 数组。

返回值

如果 json_array 中包含了 value，那么 MEMBER OF() 返回 1, 否则返回 0。

如果 value 和 json_array 都是纯值且相等，那么 MEMBER OF() 返回 1。

如果参数 json_array 不是有效的 JSON 文档，MySQL 将会给出一个错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

MEMBER OF() 示例

基本用法

SELECT 1 MEMBER OF('[1, 2, "a"]'), 'a' MEMBER OF('[1, 2, "a"]'); +----------------------------+------------------------------+ | 1 MEMBER OF('[1, 2, "a"]') | 'a' MEMBER OF('[1, 2, "a"]') | +----------------------------+------------------------------+ | 1 | 1 | +----------------------------+------------------------------+

纯值

如果 value 和 json_array 都是纯值且相等，那么 MEMBER OF() 返回 1。

SELECT 1 MEMBER OF('1'), 'a' MEMBER OF('"a"'), CAST('true' AS JSON) MEMBER OF('true'); +------------------+----------------------+----------------------------------------+ | 1 MEMBER OF('1') | 'a' MEMBER OF('"a"') | CAST('true' AS JSON) MEMBER OF('true') | +------------------+----------------------+----------------------------------------+ | 1 | 1 | 1 | +------------------+----------------------+----------------------------------------+

这里使用了 CAST() 函数将 ‘true’ 由字符串转为 JSON 值。