MySQL JSON函数详解

一、MySQL JSON函数概述

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式，目前常用于前后段数据传输。MySQL 从 5.7.8 版本开始提供 JSON 数据类型及相关函数，其通过新增json对象类型和相应的函数来解析和生成 JSON 格式数据，使得 MySQL 在处理 JSON 数据方面更加高效便捷。

在 MySQL 中，JSON 对象可以用字符串类型表示，可以使用 JSON 函数对 JSON 对象进行解析和操作。下面将围绕 MySQL JSON 函数展开介绍。

二、MySQL JSON函数大全

1. mysql_json_extract()

mysql_json_extract(json_object, json_path)函数按照指定的路径从JSON对象中提取结果，并以字符串格式返回结果。

json_object参数可以是以下数据类型之一：

JSON类型的列
JSON类型的文本字面值

json_path参数可以指向以下数据：

整个JSON文档
单个元素
JSON数组

示例：

SELECT JSON_EXTRACT('{"id": 1, "info":{"name": "Mary", "age": 25}}', '$.info.name');
-- 返回 "Mary"
SELECT JSON_EXTRACT('{"id": 2, "info":{"name": "John", "age": 30}}', '$.info.age');
-- 返回 30

2. mysql_json_replace()

mysql_json_replace(json_object, json_path, replace_with)函数用于替换 JSON 对象中指定路径的值。如果替换的结果与原来的 JSON 对象不同，则返回替换后的 JSON 对象；否则返回原 JSON 对象。

示例：

SELECT JSON_REPLACE('{"id": 1, "name": "Mary"}', '$.name', 'John');
-- 返回 '{"id":1,"name":"John"}'

3. mysql_json_merge()

mysql_json_merge(json_object1, json_object2) 函数用于合并两个 JSON 对象，如果两个 JSON 对象的键是相同的，则后者的值会覆盖前者的值。

示例：

SELECT JSON_MERGE('{"id": 1, "name": "Mary"}', '{"name": "John", "age": "30"}');
-- 返回 '{"id": 1, "name": "John", "age": "30"}'

4. mysql_json_array()

mysql_json_array(value1, value2, ..., valueN)函数用于创建一个JSON数组。可以传入1到多个参数作为数组元素。

示例：

SELECT JSON_ARRAY(1, 'Mary', 30);
-- 返回 '[1,"Mary",30]'

三、MySQL JSON函数详解

1. mysql_json_path()

mysql_json_path(path, value) 函数用于在 JSON 对象中设置指定路径下的值。

path参数是JSON路径表达式。

value参数是要在JSON对象的路径表达式中设置的值。

示例：

SELECT JSON_SET('{"id": 1, "name": "Mary"}', '$.age', 25);
-- 返回 '{"id":1,"name":"Mary","age":25}'

2. mysql_json_unquote()

mysql_json_unquote(json_string) 函数用于去掉双引号并输出字符串类型的JSON值。

示例：

SELECT JSON_UNQUOTE('{"id": 1, "name": "Mary"}');
-- 返回 '{id: 1, name: Mary}'

3. mysql_json_array_append()

mysql_json_array_append(json_array, ..., valueN)函数用于将一个或多个值附加到已有的 JSON数组中。

json_array是当前的JSON数组。

value1到valueN是要添加到JSON数组的值。

示例：

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);
-- 返回 '[1,2,3,4]'

4. mysql_json_extract_path()

mysql_json_extract_path(json_object, ..., path, ..., pathN) 函数用于从JSON对象中提取多个路径的结果，并以JSON数组的形式返回结果。

json_object参数可以是以下数据类型之一：

JSON类型的列
JSON类型的文本字面值

path1到pathN参数可以指向以下数据：

整个JSON文档
单个元素
JSON数组

示例：

SELECT JSON_EXTRACT_PATH('{"id": 1, "info":{"name": "Mary", "age": 25}}', '$.info.name', '$.id');
-- 返回 '["Mary", 1]'

四、MySQL JSON函数使用实例

1. 按年龄分组，求出每组中年龄最大的人的信息

SELECT MAX(JSON_EXTRACT(info, "$.age")) AS max_age, 
JSON_EXTRACT(info, "$.name") AS name, 
JSON_EXTRACT(info, "$.gender") AS gender 
FROM students 
GROUP BY JSON_EXTRACT(info, "$.gender")

2. 按城市信息统计学生数量

SELECT COUNT(*) AS num, 
JSON_EXTRACT(info, "$.city") AS city 
FROM students 
GROUP BY JSON_EXTRACT(info, "$.city")

3. 对学生成绩进行排名

SELECT JSON_EXTRACT(info, "$.name") AS name, 
JSON_EXTRACT(info, "$.score") AS score, 
(
SELECT COUNT(*)+1 
FROM students b 
WHERE 
  JSON_EXTRACT(b.info, "$.score") > JSON_EXTRACT(a.info, "$.score")
) AS rank 
FROM students a