JSON 数据操作

Exisi 2023-01-19 12:51:05

Categories： Tags：

与将 JSON 格式的字符串存储在字符串列中相比，JSON 数据类型具有以下优势：

自动验证存储在 JSON 列中的 JSON 文档。无效的文档会产生错误。

优化的存储格式。存储在 JSON 列中的 JSON 文档被转换为允许对文档元素进行快速读取访问的内部格式。

当服务器稍后必须读取以这种二进制格式存储的 JSON 值时，不需要从文本表示中解析该值。

二进制格式的结构使服务器能够直接通过键值对或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

注

存储 JSON 文档所需的空间与 LONGBLOB 或 LONGTEXT 大致相同。存储在 JSON 列中的任何 JSON 文档的大小都限于 max_allowed_packet 系统变量的值。（当服务器在内存中内部操作 JSON 值时，它可以大于此值；限制适用于服务器存储它。）

在 MySQL 8.0.13 之前，JSON 列不能有非 NULL 默认值

在 MySQL 中，JSON 值被写为字符串。 MySQL 解析在需要 JSON 值的上下文中使用的任何字符串，如果它作为 JSON 无效，则会产生错误。

JSON 值的创建

JSON 数组包含由逗号分隔并包含在 [ 和 ] 字符内的值列表：

["abc", 10, null, true, false]

JSON 对象包含一组键值对，由逗号分隔并包含在 { 和 } 字符内：

{"k1": "value", "k2": 10}

JSON 数组和对象可以是字符串或数字、JSON 空或 JSON 布尔真或假字面量。 JSON 对象中的键必须是字符串。时间（日期、时间或日期时间）标量值也是允许的：

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

在 JSON 数组元素和 JSON 对象键值中允许嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}

示例

CREATE TABLE tab_base_info (

id BIGINT NOT NULL PRIMARY KEY auto_increment,

content json

);

JSON 值的插入

JSON列存储的数据，必须是JSON格式数据，或者NULL，否则会报错。

JSON数据类型是没有默认值的（声明时"DEFAULT NULL"）

示例

NSERT INTO tab_base_info (content)

VALUES

(

'{"author": "Captain&D", "blog": "https://www.cnblogs.com/captainad"}'

);

JSON 值的部分更新

在 MySQL 8.0 中，优化器可以对 JSON 列执行部分就地更新，而不是删除旧文档并将新文档全部写入列。可以针对满足以下条件的更新执行此优化：

正在更新的列被声明为 JSON。

UPDATE 语句使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 三个函数中的任何一个来更新列。列值的直接分配（例如，UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作为部分更新执行。

可以以这种方式优化单个 UPDATE 语句中多个 JSON 列的更新； MySQL 只能对那些使用刚刚列出的三个函数更新其值的列执行部分更新。

输入列和目标列必须是同一列； UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100) 之类的语句不能作为部分更新执行。只要输入列和目标列相同，更新就可以任意组合使用对上一项中列出的任何函数的嵌套调用。

所有更改都将现有数组或对象值替换为新值，并且不会向父对象或数组添加任何新元素。

被替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。

JSON 值的规范化

当字符串被解析并发现是有效的JSON文档时，它也会被规范化。这意味着，如果成员的密钥与文档中稍后找到的密钥重复，并且从左到右读取，则该成员将被丢弃。

以下 JSON_object() 调用生成的对象值只包括第二个 key1 元素，因为该键名出现在值的前面，如下所示：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

+-------------------------------------------------------------+

|JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |

+-------------------------------------------------------------+

|{"key1": "def", "key2": "abc"} |

+-------------------------------------------------------------+

将值插入JSON列时也会执行规范化，如下所示：

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES

> ('{"x": 17, "x": "red"}'),

> ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;

+------------------+

|c1 |

+------------------+

|{"x": "red"} |

|{"x": [3, 5, 7]} |

+------------------+

在8.0.3之前的MySQL版本中，具有与文档中先前找到的密钥重复的密钥的成员被丢弃。以下JSON_object() 调用生成的对象值不包括第二个key1元素，因为该键名出现在值的前面：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

+-------------------------------------------------------------+

|JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |

+-------------------------------------------------------------+

|{"key1": 1, "key2": "abc"} |

+-------------------------------------------------------------+

MySQL还丢弃原始JSON文档中键、值或元素之间的额外空白，并在显示时在每个逗号（，）或冒号（：）后留下（或在必要时插入）一个空格。这样做是为了提高可读性

JSON 值的合并

MySQL 8.0.3（及更高版本）支持两种合并算法

它们在处理重复键的方式上有所不同：

JSON_MERGE_PRESERVE() 保留重复键的值
JSON_MERGE_PATCH() 丢弃除最后一个值之外的所有值。

接下来的几段将解释这两个函数中的每一个如何处理 JSON 文档的不同组合（即对象和数组）的合并。

合并数组。在组合多个数组的上下文中，这些数组被合并为一个数组。

JSON_MERGE_PRESERVE() 通过将稍后命名的数组连接到第一个数组的末尾来完成此操作。 JSON_MERGE_PATCH() 将每个参数视为由单个元素组成的数组（因此其索引为 0），然后应用“最后一个重复键获胜”逻辑来仅选择最后一个参数。

您可以比较此查询显示的结果：

mysql> SELECT

-> JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,

-> JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G

***************************1. row ***************************

Preserve:[1, 2, "a", "b", "c", true, false]
Patch:[true, false]

合并多个对象时会生成一个对象。
- JSON_MERGE_PRESERVE() 通过在一个数组中组合该键的所有唯一值来处理具有相同键的多个对象；然后，该数组将用作结果中该键的值。
- JSON_MERGE_PATCH() 从左到右丢弃找到重复键的值，以便结果只包含该键的最后一个值。

以下查询说明了重复密钥a的结果差异：

mysql> SELECT

-> JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,

-> JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G

***************************1. row ***************************

Preserve:{"a":[1, 4], "b":2, "c":[3, 5], "d":3}
Patch:{"a":4, "b":2, "c":5, "d":3}

在需要数组值的上下文中使用的非数组值是自动包装的：该值由[and]字符包围，以将其转换为数组。在下面的语句中，每个参数都自动包装为一个数组（[1]，[2]）。然后合并这些结果以生成单个结果数组；与前两种情况一样，
- JSON_MERGE_PRESERVE（）组合具有相同密钥的值，
- JSON_MERGE_PATCH（）丢弃除最后一个密钥外的所有重复密钥的值，

如下所示：

mysql> SELECT

-> JSON_MERGE_PRESERVE('1', '2') AS Preserve,

-> JSON_MERGE_PATCH('1', '2') AS Patch\G

***************************1. row ***************************

Preserve:[1, 2]
Patch:2

通过将对象自动包装为数组并通过组合值或根据合并函数（分别为JSON_MERGE_PRESERVE() 或JSON_MERGE_PATCH() ）的选择“last duplicate key wins”来合并数组和对象值，

如本例所示：

mysql> SELECT

-> JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,

-> JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G

***************************1. row ***************************

Preserve:[10, 20, {"a":"x", "b":"y"}]
Patch:{"a":"x", "b":"y"}

JSON 值的搜索和修改

JSON路径表达式在JSON文档中选择一个值。

路径表达式对于提取JSON文档的一部分或修改JSON文档的函数非常有用，可以指定在该文档中操作的位置。例如，以下查询从JSON文档中提取具有name键的成员的值：

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

+------------------------------------------------------------------+

|JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |

+------------------------------------------------------------------+

|"Aztalan" |

+------------------------------------------------------------------+

Path语法使用前导$字符来表示正在考虑的JSON文档，可选地后跟选择器，以依次指示文档中更具体的部分：
- 后跟键名的句点用给定键命名对象中的成员。如果不带引号的名称在路径表达式中不合法（例如，如果包含空格），则必须在双引号内指定键名。

[N] 附加到选择数组的路径后，将数组中位置N处的值命名。数组位置是从零开始的整数。如果路径未选择数组值，则路径[0]的计算结果与路径相同：

mysql> SELECT JSON_SET('"x"', '$[0]', 'a');

+----------------------------------+

|JSON_SET('"x"', '$[0]', 'a') |

+----------------------------------+

|"a" |

+----------------------------------+

1 row in set (0.00 sec)

[M到N]指定数组值的子集或范围，从位置M处的值开始，到位置N处的值结束。

last作为最右边数组元素索引的同义词受支持。还支持数组元素的相对寻址。如果path未选择数组值，则path[last]的计算结果与path相同，如本节后面所示（请参阅最右边的数组元素）。

路径可以包含*或**通配符：

[*]计算为JSON对象中所有成员的值。
[*]计算为JSON数组中所有元素的值。
前缀**后缀计算为以命名前缀开头，以命名后缀结尾的所有路径。
文档中不存在的路径（计算结果为不存在的数据）计算结果为NULL

让$用三个元素引用这个JSON数组：

[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[0]的计算结果为3。
$[1]的计算结果为{“a”：[5,6]，“b”：10}。
$[2]的计算结果为[99100]。
$[3]的计算结果为NULL（它指的是第四个数组元素，它不存在）。
由于$[1]和$[2]的计算结果是非标度值，因此它们可以用作选择嵌套值的更具体路径表达式的基础。例如：
$[1].a的计算结果为[5,6]。
$[1].a[1]的计算结果为6。
$[1].b评估为10。
$[2][0]的计算结果为99。

如前所述，如果未加引号的键名在路径表达式中不合法，则必须对命名键的路径组件加引号。让$参考这个值：

{"a fish": "shark", "a bird": "sparrow"}

两个键都包含空格，并且必须引用：

$."a fish" 的结果为 shark.
$."a bird" 的结果为 sparrow.

使用通配符的路径计算为可以包含多个值的数组：

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');

+-------------------------------------------------------------+

|JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |

+-------------------------------------------------------------+

|[1, 2, [3, 4, 5]] |

+-------------------------------------------------------------+

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');

+----------------------------------------------------------------+

|JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |

+----------------------------------------------------------------+

|[3, 4, 5] |

+----------------------------------------------------------------+

在以下示例中，路径 $**.b 计算为多个路径（$.a.b 和 $.c.b）并生成匹配路径值的数组：

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

+---------------------------------------------------------------+

|JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |

+---------------------------------------------------------------+

|[1, 2] |

+---------------------------------------------------------------+

来自 JSON 数组的范围。您可以使用带 to 关键字的范围来指定 JSON 数组的子集。例如，$[1 to 3] 包含数组的第二个、第三个和第四个元素，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');

+----------------------------------------------------+

|JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |

+----------------------------------------------------+

|[2, 3, 4] |

+----------------------------------------------------+

1 row in set (0.00 sec)

语法是 M 到 N，其中 M 和 N 分别是 JSON 数组中一系列元素的第一个和最后一个索引。 N必须大于M； M 必须大于或等于 0。数组元素的索引从 0 开始。
您可以在支持通配符的上下文中使用范围。

最右边的数组元素。last关键字支持作为数组中最后一个元素的索引的同义词。last-N形式的表达式可用于相对寻址和范围内定义，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]'); +--------------------------------------------------------------+

|JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') | +--------------------------------------------------------------+

|[2, 3, 4] |

+--------------------------------------------------------------+

1 row in set (0.01 sec)

如果根据非数组的值计算路径，则计算结果与将该值包装在单个元素数组中的结果相同：

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);

+-----------------------------------------------+

|JSON_REPLACE('"Sakila"', '$[last]', 10) |

+-----------------------------------------------+

|10 |

+-----------------------------------------------+

1 row in set (0.00 sec)

您可以使用带有JSON列标识符的column->path和JSON路径表达式作为JSON_EXTRACT（column，path）的同义词。

有些函数获取现有的JSON文档，以某种方式对其进行修改，然后返回修改后的文档。路径表达式指示要在文档中的何处进行更改。例如，JSON_SET()、JSON_INSERT()v和JSON_REPLACE() 函数分别获取一个JSON文档，以及一个或多个描述在何处修改文档和使用值的路径值对。这些函数在处理文档中现有值和不存在值的方式上有所不同。

来自 <https://dev.mysql.com/doc/refman/8.0/en/json.html>

类型使用规范

char 和 varchar的区别