极客笔记MySQL 解析 JSON
简介
在 MySQL 5.7 版本及以后的版本中，MySQL 引入了对 JSON 数据的支持。这使得开发人员可以在关系型数据库中存储和操作 JSON 数据，无需转换成字符串或二进制来处理。MySQL 提供了一套强大的 JSON 函数和操作符，用于解析和操作 JSON 数据。
本文将详细介绍如何在 MySQL 数据库中解析 JSON 数据，包括提取 JSON 中的值、修改 JSON 中的键值对、过滤 JSON 数据以及在查询中使用 JSON 函数等。
JSON 数据类型
在 MySQL 中，JSON 是一种原生的数据类型，类似于字符串、数字和日期等数据类型。JSON 数据可以直接存储在 JSON 列中，无需声明类型或进行额外的转换。
在创建表时，可以使用 JSON 数据类型来定义一列为 JSON 类型。例如，创建一个名为 users 的表，其中包含一个 JSON 列 data：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON
);
JSON_VALUE函数
JSON_VALUE 函数用于从 JSON 字符串中获取指定路径的值。
函数语法为：
JSON_VALUE(json_doc, path)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定 JSON 字符串中的值的路径。
让我们以一个示例来说明如何使用 JSON_VALUE 函数。假设我们有一个名为 users 的表，其中的 data 列存储了用户的 JSON 数据。下面是一个示例 JSON 数据：
{
"name": "John",
"age": 30,
"address": {
"street": "123 Main St",
"city": "New York",
"state": "NY"
},
"emails": [
"john@example.com",
"john.doe@gmail.com"
]
}
要从上面的 JSON 数据中获取 name 的值，可以使用以下查询：
SELECT JSON_VALUE(data, '$.name') FROM users;
查询结果将返回 John。
JSON_TYPE函数
JSON_TYPE 函数用于返回 JSON 字符串或 JSON 文档的类型。
函数语法为：
JSON_TYPE(json_doc)
其中，json_doc 是一个包含 JSON 字符串的表达式。
让我们以一个示例来说明如何使用 JSON_TYPE 函数。继续以上面的 users 表为例，下面是一个示例 JSON 数据：
{
"name": "John",
"age": 30,
"address": {
"street": "123 Main St",
"city": "New York",
"state": "NY"
},
"emails": [
"john@example.com",
"john.doe@gmail.com"
]
}
要获取上述 JSON 数据的类型，可以使用以下查询：
SELECT JSON_TYPE(data) FROM users;
查询结果将返回 OBJECT。
JSON_KEYS函数
JSON_KEYS 函数用于返回 JSON 对象中所有的键。
函数语法为：
JSON_KEYS(json_doc)
其中，json_doc 是一个包含 JSON 字符串的表达式。
继续以上面的 JSON 数据为例，要获取上述 JSON 数据中的所有键，可以使用以下查询：
SELECT JSON_KEYS(data) FROM users;
查询结果将返回 ["name", "age", "address", "emails"]。
JSON_ARRAY函数
JSON_ARRAY 函数用于构造一个 JSON 数组。
函数语法为：
JSON_ARRAY(value1, value2, ...)
可以传入一个或多个值，每个值都可以是常量、列或子查询的结果。
以下是一个示例，展示如何使用 JSON_ARRAY 函数创建一个包含多个值的 JSON 数组：
SELECT JSON_ARRAY(1, 2, 'a', 'b') AS json_array;
查询结果将返回 "[1, 2, "a", "b"]"。
JSON_OBJECT函数
JSON_OBJECT 函数用于构造一个 JSON 对象。
函数语法为：
JSON_OBJECT(key1, value1, key2, value2, ...)
可以传入一对或多对键值对，每个键值对都通过逗号分隔。键可以是常量、列或子查询的结果，值可以是常量、列或表达式。
以下是一个示例，展示如何使用 JSON_OBJECT 函数创建一个包含多个键值对的 JSON 对象：
SELECT JSON_OBJECT('name', 'John', 'age', 30, 'city', 'New York') AS json_object;
查询结果将返回 "{ "name": "John", "age": 30, "city": "New York" }"。
JSON_ARRAY_APPEND函数
JSON_ARRAY_APPEND 函数用于向 JSON 数组的末尾追加一个或多个元素。
函数语法为：
JSON_ARRAY_APPEND(json_doc, path, value1, value2, ...)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定 JSON 数组的路径，value1, value2, ... 是要追加到数组的值。
以下是一个示例，展示如何使用 JSON_ARRAY_APPEND 函数向 JSON 数组追加多个值：
UPDATE users SET data = JSON_ARRAY_APPEND(data, '$.emails', 'john@example.com', 'john.doe@gmail.com') WHERE id = 1;
以上查询将向 id 为 1 的用户的 data 列中的 emails 数组中追加两个新的值。
JSON_OBJECT_APPEND函数
JSON_OBJECT_APPEND 函数用于向 JSON 对象的末尾添加一个或多个键值对。
函数语法为：
JSON_OBJECT_APPEND(json_doc, path, key1, value1, key2, value2, ...)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定 JSON 对象的路径，key1, value1, key2, value2, ... 是要追加到对象的键值对。
以下是一个示例，展示如何使用 JSON_OBJECT_APPEND 函数向 JSON 对象追加多个键值对：
UPDATE users SET data = JSON_OBJECT_APPEND(data, '$', 'new_key1', 'new_value1', 'new_key2', 'new_value2') WHERE id = 1;
以上查询将向 id 为 1 的用户的 data 列中的 JSON 对象中追加两个新的键值对。
JSON_CONTAINS函数
JSON_CONTAINS 函数用于检查 JSON 文档是否包含指定的路径和值。
函数语法为：
JSON_CONTAINS(json_col, expr[, path])
其中，json_col 是一个 JSON 列，expr 是要检查的值，path 是可选的搜索路径。
以下是一个示例，展示如何使用 JSON_CONTAINS 函数检查 JSON 数组是否包含指定的值：
SELECT JSON_CONTAINS(data, 'john.doe@gmail.com', '$.emails') FROM users WHERE id = 1;
以上查询将返回 1，表示 JSON 数组中包含指定的值。
JSON_SEARCH函数
JSON_SEARCH 函数用于在 JSON 文档中搜索指定值的路径。
函数语法为：
JSON_SEARCH(json_doc, value[, path[, escape_char[, strict_match]]])
其中，json_doc 是一个包含 JSON 字符串的表达式，value 是要搜索的值，path 是可选的搜索路径，escape_char 是可选的转义字符，strict_match 是可选的严格匹配参数。
以下是一个示例，展示如何使用 JSON_SEARCH 函数搜索 JSON 对象中指定值的路径：
SELECT JSON_SEARCH(data, 'John', '$') FROM users WHERE id = 1;
以上查询将返回 "$['name']"，表示搜索到了指定值的路径。
JSON_EXTRACT函数
JSON_EXTRACT 函数用于从 JSON 文档中提取指定路径的值。
函数语法为：
JSON_EXTRACT(json_doc, path[, path]...)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定 JSON 字符串中的路径。
以下是一个示例，展示如何使用 JSON_EXTRACT 函数提取 JSON 数据中指定路径的值：
SELECT JSON_EXTRACT(data, '$.name') FROM users WHERE id = 1;
以上查询将返回 JSON 数据中键 name 对应的值。
JSON_ARRAY_INSERT函数
JSON_ARRAY_INSERT 函数用于向 JSON 数组的指定位置插入一个或多个元素。
函数语法为：
JSON_ARRAY_INSERT(json_doc, path, value1, value2, ...)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定 JSON 数组的路径，value1, value2, ... 是要插入到数组的值。
以下是一个示例，展示如何使用 JSON_ARRAY_INSERT 函数向 JSON 数组插入多个值：
UPDATE users SET data = JSON_ARRAY_INSERT(data, '$.emails[0]', 'new_email@example.com') WHERE id = 1;
以上查询将向 id 为 1 的用户的 data 列中的 emails 数组的第一个位置插入一个新的值。
JSON_REMOVE函数
JSON_REMOVE 函数用于从 JSON 文档中移除指定路径的键值对或数组元素。
函数语法为：
JSON_REMOVE(json_doc, path[, path]...)
其中，json_doc 是一个包含 JSON 字符串的表达式，path 是指定要移除的路径。
以下是一个示例，展示如何使用 JSON_REMOVE 函数从 JSON 数据中移除指定路径的值：
UPDATE users SET data = JSON_REMOVE(data, '$.address.state') WHERE id = 1;
以上查询将移除 id 为 1 的用户的 data 列中的 address 对象中的 state 键值对。
总结
本文介绍了 MySQL 中解析 JSON 数据的方法和函数，包括 JSON_VALUE、JSON_TYPE、JSON_KEYS、JSON_ARRAY、JSON_OBJECT、JSON_ARRAY_APPEND、JSON_OBJECT_APPEND、JSON_CONTAINS、JSON_SEARCH、JSON_EXTRACT、JSON_ARRAY_INSERT 和 JSON_REMOVE 函数等。通过这些函数，我们可以方便地对存储在 MySQL 数据库中的 JSON 数据进行操作和使用。有了这些强大的函数支持，我们可以更灵活地处理 JSON 数据，提取和修改其中的键值对，满足实际项目中对 JSON 数据的需求。