likes
comments
collection
share

玩转MySQL 8：掌握JSON数据处理的艺术

作者站长头像
站长
· 阅读数 21

引言

MySQL 8作为市场上领先的关系型数据库管理系统，不仅在性能上进行了显著提升，也在功能上紧跟潮流，为开发者提供了强大的JSON数据处理能力。本文将带您深入了解MySQL 8中的JSON功能，从基础语法到高级操作，全面探索如何在MySQL中高效地存储、查询和操作JSON数据。我们将通过实际示例，一步步展示如何利用MySQL 8的JSON支持，来简化数据处理流程，优化性能，并最终掌握JSON数据处理的艺术。

正文

MySQL 8提供了一系列的JSON函数，允许在SQL查询中直接操作JSON数据。以下是一些常用JSON函数的使用示例和讲解：

创建和构造JSON数据

  1. JSON_OBJECT(key, value[, key, value] ...)
  • 创建一个JSON对象。
SELECT JSON_OBJECT('id', 1, 'name', 'John Doe') AS json_object;

这将输出一个JSON对象：{"id": 1, "name": "John Doe"}。

  1. JSON_ARRAY([value[, value] ...])
  • 创建一个JSON数组。
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURDATE()) AS json_array;

这将输出一个JSON数组：[1, "abc", null, true, "2023-04-01"]。

查询和修改JSON数据

  1. JSON_EXTRACT(json_doc, path[, path] ...) 或使用操作符 ->
  • 从JSON文档中提取值。
SET @json = '{"id": 1, "name": {"first": "John", "last": "Doe"}}';
SELECT JSON_EXTRACT(@json, '$.name.first') AS first_name;
-- 或者使用 -> 操作符
SELECT @json->'$.name.first' AS first_name;

这将返回"John"。

  1. JSON_SET(json_doc, path, value[, path, value] ...)
  • 如果路径不存在，则在JSON文档中添加值；如果路径存在，则更新值。
SET @json = '{"id": 1, "name": "John Doe"}';
SELECT JSON_SET(@json, '$.age', 30) AS new_json;

这将返回：{"id": 1, "name": "John Doe", "age": 30}。

  1. JSON_INSERT(json_doc, path, value[, path, value] ...)
  • 只有当路径不存在时，才在JSON文档中添加值。
SET @json = '{"id": 1, "name": "John Doe"}';
SELECT JSON_INSERT(@json, '$.age', 30) AS new_json;

这将返回：{"id": 1, "name": "John Doe", "age": 30}。

  1. JSON_REPLACE(json_doc, path, value[, path, value] ...)
  • 替换JSON文档中已存在的值。
SET @json = '{"id": 1, "name": "John Doe", "age": 28}';
SELECT JSON_REPLACE(@json, '$.age', 30) AS new_json;

这将返回：{"id": 1, "name": "John Doe", "age": 30}。

  1. JSON_REMOVE(json_doc, path[, path] ...)
  • 从JSON文档中删除指定的路径。
SET @json = '{"id": 1, "name": "John Doe", "age": 30}';
SELECT JSON_REMOVE(@json, '$.age') AS new_json;

这将返回：{"id": 1, "name": "John Doe"}。

合并JSON文档

  1. JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
  • 合并多个JSON文档，保留重复的键。
SET @json1 = '{"id": 1, "name": "John Doe"}';
SET @json2 = '{"age": 30}';
SELECT JSON_MERGE_PRESERVE(@json1, @json2) AS merged_json;

这将返回：{"id": 1, "name": "John Doe", "age": 30}。

  1. JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
  • 合并多个JSON文档，如果有重复的键，则后者覆盖前者。
SET @json1 = '{"name": "John Doe", "age": 28}';
SET @json2 = '{"age": 30, "city": "New York"}';
SELECT JSON_MERGE_PATCH(@json1, @json2) AS merged_json;

这将返回：{"name": "John Doe", "age": 30, "city": "New York"}。

其他有用的JSON函数

  1. JSON_VALID(json_val)
  • 检查值是否是有效的JSON文档。
SELECT JSON_VALID('{"name": "John Doe"}') AS is_valid;

这将返回：1（表示true）。

  1. JSON_KEYS(json_doc[, path])
  • 返回JSON文档中的键。
SET @json = '{"id": 1, "name": "John Doe"}';
SELECT JSON_KEYS(@json) AS keys;

这将返回：["id", "name"]。

  1. JSON_QUOTE(string)
  • 引用一个字符串为JSON字符串。
SELECT JSON_QUOTE('John Doe') AS quoted_string;

这将返回："John Doe"。

  1. JSON_UNQUOTE(json_val)
  • 取消引用JSON值中的字符串。
SET @json = '"John Doe"';
SELECT JSON_UNQUOTE(@json) AS unquoted_string;

这将返回：John Doe。

  1. JSON_TYPE(json_val)
  • 返回JSON值的类型。
SET @json = '{"name": "John Doe"}';
SELECT JSON_TYPE(@json) AS json_type;

这将返回：OBJECT。

  1. JSON_PRETTY(json_val)
  • 以易读的格式返回JSON文档。
SET @json = '{"name": "John Doe", "age": 30}';
SELECT JSON_PRETTY(@json) AS pretty_json;
  1. JSON_DEPTH(json_doc)
  • 返回JSON文档的最大深度。
SET @json = '{"a": {"b": {"c": {}}}}';
SELECT JSON_DEPTH(@json) AS depth;

这将返回4，因为JSON文档中最深的路径包含4级。

  1. JSON_LENGTH(json_doc[, path])
  • 返回JSON文档中的元素个数。
SET @json = '{"name": "John Doe", "hobbies": ["Reading", "Traveling"]}';
SELECT JSON_LENGTH(@json, '$.hobbies') AS length;

这将返回2，因为"hobbies"数组包含两个元素。

  1. JSON_CONTAINS(json_doc, val[, path])
  • 检查JSON文档是否包含特定的值。
SET @json = '{"name": "John Doe", "hobbies": ["Reading", "Traveling"]}';
SELECT JSON_CONTAINS(@json, '"Reading"', '$.hobbies') AS contains;

这将返回1（表示true），因为"hobbies"数组包含字符串"Reading"。

  1. JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
  • 检查JSON文档中是否包含给定路径。
SET @json = '{"name": "John Doe", "age": 30}';
SELECT JSON_CONTAINS_PATH(@json, 'one', '$.name', '$.age') AS contains_path;

这将返回1（表示true），因为JSON文档包含这两个路径。

  1. JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
  • 在JSON文档中搜索给定的字符串。
SET @json = '{"name": "John Doe", "hobbies": ["Reading", "Traveling"]}';
SELECT JSON_SEARCH(@json, 'one', 'Reading') AS search_result;

这将返回"$.hobbies[0]"，表示找到的匹配项在"hobbies"数组的第一个元素。

  1. JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)
  • 向JSON数组的末尾添加一个值。
SET @json = '{"hobbies": ["Reading"]}';
SELECT JSON_ARRAY_APPEND(@json, '$.hobbies', 'Traveling') AS new_json;

这将返回{"hobbies": ["Reading", "Traveling"]}。

  1. JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)
  • 在JSON数组中的指定位置插入一个值。
SET @json = '{"hobbies": ["Reading", "Traveling"]}';
SELECT JSON_ARRAY_INSERT(@json, '$.hobbies[1]', 'Hiking') AS new_json;

这将返回{"hobbies": ["Reading", "Hiking", "Traveling"]}。

  1. JSON_OVERLAPS(json_doc1, json_doc2)
  • 检查两个JSON文档是否有共同的键值对或数组元素。
SET @json1 = '{"hobbies": ["Reading", "Traveling"]}';
SET @json2 = '{"hobbies": ["Traveling", "Sports"]}';
SELECT JSON_OVERLAPS(@json1, @json2) AS overlaps;

这将返回1（表示true），因为两个JSON文档在"hobbies"数组中都包含"Traveling"。

  1. JSON_TABLE(expr, path COLUMNS (column_list))
  • 将JSON文档转换为关系型数据表。
SET @json = '{"students": [{"name": "John", "score": 90}, {"name": "Jane", "score": 88}]}';
SELECT * FROM JSON_TABLE(@json, '$.students[*]' COLUMNS (
  name VARCHAR(40) PATH '$.name',
  score INT PATH '$.score'
)) AS jt;

这将返回一个表���，包含两行数据，每行包含学生的名字和分数。

这些JSON函数大大扩展了在MySQL中处理JSON格式数据的能力，使得可以直接在数据库层面进行复杂的JSON操作，而不需要在应用层编写额外的逻辑。这些功能尤其适用于需要处理非结构化数据或灵活数据模型的应用。

以上是MySQL 8中常用的JSON函数的使用示例。这些函数让能够在SQL层面上灵活地处理JSON数据，无需在应用层进行复杂的数据转换。

总结：

经过对MySQL 8中JSON功能的深入探讨，可以看到，无论是在数据存储、查询还是操作层面，MySQL 8都提供了丰富的JSON函数，让处理JSON数据变得既简单又高效。通过本文的介绍，现在应该能够熟练地在MySQL中创建JSON对象和数组，执行复杂的JSON路径查询，甚至将JSON数据转换成关系型数据进行分析。总而言之，MySQL 8的JSON支持为开发者们打开了一个全新的数据处理领域，能够在保持数据结构化的同时，享受非结构化数据带来的便利。

转载自:https://juejin.cn/post/7390319492121919524
评论
请登录