03的开发日志
0%

MySQL JSON操作

发表于 更新于 分类于
本文字数： 12k 阅读时长 ≈ 11 分钟
  1. 写在前面
  2. JSON字段新增与修改
    1. JSON Object
      1. 设置数据（强行设置）：JSON_SET
      2. 新增数据（若存在则不新增）：JSON_INSERT
      3. 替换数据（若不存在则不替换）：JSON_REPLACE
    2. JSON Array
      1. 插入元素：JSON_ARRAY_INSERT / JSON_ARRAY_APPEND
  3. 字段合并
    1. JSON_MERGE
  4. 删除字段
    1. JSON_REMOVE
  5. JSON数据查询
    1. 查询值 JSON_EXTRACT
    2. 查询长度 JSON_LENGTH
    3. 查询深度 JSON_DEPTH
    4. 查询数据类型 JSON_TYPE
    5. 检查是否为有效的JSON数据 JSON_VALID
    6. 查询对象键名 JSON_KEYS
    7. 查询键和值是否存在 JSON_CONTAINS / JSON_CONTAINS_PATH
    8. 查询索引位置 JSON_SEARCH

写在前面

本文主要用于介绍MySQL中JSON相关的函数操作，示例均使用SELECT+JSON函数演示。

关于如何将JSON函数的结果应用到表的字段中，请看以下示例：

  1. 创建表
1
2
3
4
5
CREATE TABLE `example_table` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `json_data` json DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  1. 插入三条空数据
1
2
3
INSERT INTO example_table(json_data) VALUES ("{}");
INSERT INTO example_table(json_data) VALUES ("{}");
INSERT INTO example_table(json_data) VALUES ("{}");

image-20250217174904924

  1. 修改数据（以INSERT为例，其他功能以此类推，用法类似）
1
2
UPDATE example_table SET json_data=JSON_SET(json_data, '$.a', 'hello world') WHERE ID=1;
# 执行结果：{"a": "hello world"}

JSON字段新增与修改

JSON Object

设置数据（强行设置）：JSON_SET

不管JSON Object中是否存在这个键，都为其强行设置一个值。

示例1：新增一个值

1
2
SELECT JSON_SET('{}', '$.a', 'hello world');
# {"a": "hello world"}

示例2：新增多个值

1
2
SELECT JSON_SET('{}', '$.a', 'hello world', '$.b', 'world hello');
# {"a": "hello world", "b": "world hello"}

新增数据（若存在则不新增）：JSON_INSERT

如果要新增的键在JSON Object中存在，则不新增，否则新增数据。

示例1：INSERT无效

1
2
3
# 没有效果，因为键a已经存在
SELECT JSON_INSERT('{"a": "hello world"}', '$.a', '000');
# {"a": "hello world"}

示例2：INSERT有效

1
2
SELECT JSON_INSERT('{"a": "hello world"}', '$.b', '000');
# {"a": "hello world", "b": "000"}

替换数据（若不存在则不替换）：JSON_REPLACE

如果要替换的键在JSON Object中存在，则进行替换，否则不生效。

示例1：REPLACE有效

1
2
SELECT JSON_REPLACE('{"a": "hello world"}', '$.a', '000');
# {"a": "000"}

示例2：REPLACE无效

1
2
SELECT JSON_REPLACE('{"a": "hello world"}', '$.b', '000');
# {"a": "hello world"}

JSON Array

插入元素：JSON_ARRAY_INSERT / JSON_ARRAY_APPEND

这俩有啥区别？

JSON_ARRAY_INSERT必须用会报错，示例：

1
2
3
4
5
6
7
8
9
10
SELECT JSON_ARRAY_INSERT('[]', '$[0]', '111');
# ["111"]
SELECT JSON_ARRAY_INSERT('["111"]', '$[0]', '222');
# ["222", "111"]
SELECT JSON_ARRAY_INSERT('["222", "111"]', '$[2]', '333');
# ["222", "111", 333]
SELECT JSON_ARRAY_INSERT('["222", "111"]', '$[999999]', 333);
# ["222", "111", 333]（索引超界了也没事）
SELECT JSON_ARRAY_INSERT('["222", "111"]', '$[999999]', 333, '$[999999]', 444);
# ["222", "111", 333, 444] （多个值）

JSON_ARRAY_APPEND只能在屁股后面添加，使用$表示根数组，使用$[idx]、$.key表示子元素。
若选定的$、$[idx]、$.key的类型不是数组，则会强行改变其类型为数组。
示例：

1
2
3
4
5
6
7
8
SELECT JSON_ARRAY_APPEND('[]', '$', 1, '$', '2', '$', 3, '$', JSON_OBJECT(), '$', JSON_ARRAY());
# [1, "2", 3, {}, []] （一口气加了五个，每一个数据之前都要加个$）
SELECT JSON_ARRAY_APPEND('["000", []]', '$[1]', 123);
# ["000", [123]] （给数组里面加个数字）
SELECT JSON_ARRAY_APPEND('["000", []]', '$[0]', 123);
# [["000", 123], []] （如果不是数组，则会将其变成数组，原先的元素变成数组成员）
SELECT JSON_ARRAY_APPEND('{"a":"str"}', '$.a', 123);
# {"a": ["str", 123]}

字段合并

JSON_MERGE

示例1：对象+对象=对象，重复的键变成数组

1
2
SELECT JSON_MERGE('{"a":"123","b":321}', '{"b":123,"c":"000"}')
# {"a": "123", "b": [321, 123], "c": "000"}

示例2：数组+对象=数组

1
2
SELECT JSON_MERGE('{"b":123,"c":"000"}', '[123,"123"]')
# [{"b": 123, "c": "000"}, 123, "123"]

示例3：数组+数组=数组

1
2
SELECT JSON_MERGE('[1,"2"]', '["3",4]')
# [1, "2", "3", 4]

删除字段

JSON_REMOVE

1
2
3
4
5
6
7
8
9
10
SELECT JSON_REMOVE('{"a":{"a":123}}', '$.a');
# {}
SELECT JSON_REMOVE('{"a":{"a":123}}', '$.a.a');
# {"a": {}}
SELECT JSON_REMOVE('[1,"2",3]', '$[0]');
# ["2", 3]
SELECT JSON_REMOVE('[1,"2",[1,2,3]]', '$[2][1]');
# [1, "2", [1, 3]]
SELECT JSON_REMOVE('[1,"2",[1,2,3]]', '$');
### 报错

JSON数据查询

查询值 JSON_EXTRACT

该函数等价于箭头符号->

1
2
3
SELECT JSON_EXTRACT(json_data, '$.a') FROM json_demo;
# 等价于
SELECT json->'$.a' FROM json_demo;

由于SELECT+JSON函数调用不涉及具体表，因此使用JSON_EXTRACT函数进行示例。

示例：

1
2
3
4
5
6
7
8
SELECT JSON_EXTRACT('[1,"2",[1,2,3]]', '$');
# [1, "2", [1, 2, 3]]
SELECT JSON_EXTRACT('[1,"2",[1,2,3]]', '$[2][2]');
# 3
SELECT JSON_EXTRACT('{"a":123}', '$.a');
# 123
SELECT JSON_EXTRACT('{"a":123}', '$.b');
# 空值：NULL

查询长度 JSON_LENGTH

数组的长度是元素数量，对象的长度是键值对数量。

示例：

1
2
3
4
5
6
SELECT JSON_LENGTH('[1,"2",123,{"a":0},[3,2,1]]')
# 5
SELECT JSON_LENGTH(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$[4]'))
# 3
SELECT JSON_LENGTH(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$[3]'))
# 2

查询深度 JSON_DEPTH

返回数组/对象的深度。

示例：

1
2
3
4
5
6
7
8
9
10
SELECT JSON_DEPTH('[]')
SELECT JSON_DEPTH('{}')
# 1
SELECT JSON_DEPTH('[1]')
SELECT JSON_DEPTH('{"a":1}')
SELECT JSON_DEPTH('{"a":{}}')
# 2
SELECT JSON_DEPTH('[{"a":{}}]')
SELECT JSON_DEPTH('{"a":{"b":1}}')
# 3

查询数据类型 JSON_TYPE

返回数据类型。

示例：

1
2
3
4
5
6
7
8
SELECT JSON_TYPE(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$'))
# ARRAY
SELECT JSON_TYPE(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$[0]'))
# INTEGER
SELECT JSON_TYPE(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$[1]'))
# STRING
SELECT JSON_TYPE(JSON_EXTRACT('[1,"2",123,{"a":0,"b":1},[3,2,1]]', '$[3]'))
# OBJECT

检查是否为有效的JSON数据 JSON_VALID

返回0表示无效，1表示有效。（看不懂，也不知道有啥用，为啥数组中的”hello”有效，直接打个字符串就无效？为啥字符串数字有效？）

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT JSON_VALID(JSON_EXTRACT('[1,"2"]', '$[0]'))
SELECT JSON_VALID(JSON_EXTRACT('[1,"2"]', '$[1]'))
SELECT JSON_VALID(JSON_EXTRACT('[1,"hello"]', '$[1]'))
SELECT JSON_VALID(JSON_EXTRACT('{"a":1}', '$.a'))
# 1
SELECT JSON_VALID(JSON_EXTRACT('[1,"2"]', '$[2]'))  # 超界
SELECT JSON_VALID(JSON_EXTRACT('{"a":1}', '$[0]'))  # 不是数组
SELECT JSON_VALID(JSON_EXTRACT('{"a":1}', '$.b'))  # 不存在
# 0
SELECT JSON_VALID("") # 0
SELECT JSON_VALID("{") # 0
SELECT JSON_VALID("[") # 0
SELECT JSON_VALID("{}") # 1
SELECT JSON_VALID("[]") # 1
SELECT JSON_VALID("hello") # 0
SELECT JSON_VALID("123") # 1

查询对象键名 JSON_KEYS

返回对象的键名数组（对数组无效）。

示例：

1
2
3
4
SELECT JSON_KEYS('{"a":1,"b":2}')
# ["a", "b"]
SELECT JSON_KEYS('[1,{"a":1}]')
# 空值NULL

查询键和值是否存在 JSON_CONTAINS / JSON_CONTAINS_PATH

JSON_CONTAINS用于查询键+值是否存在，JSON_CONTAINS_PATH用于查询路径是否存在

示例1：查询键值是否存在，使用三个参数（第二个参数只能是字符串，用双引号区分类型）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT JSON_CONTAINS('{"a":1,"b":{"c":3}}', '1', '$.a');  # 1
SELECT JSON_CONTAINS('{"a":1,"b":{"c":3}}', '2', '$.a');  # 0
SELECT JSON_CONTAINS('{"a":"1","b":{"c":3}}', '1', '$.a');  # 0
SELECT JSON_CONTAINS('{"a":"1","b":{"c":3}}', '"1"', '$.a');  # 1

SELECT JSON_CONTAINS('[1,"2"]', '1');  # 1
SELECT JSON_CONTAINS('[1,"2"]', '2');  # 0
SELECT JSON_CONTAINS('[1,"2"]', '"2"');  # 1

SELECT JSON_CONTAINS('[1,"2",3]', '3', '"2"');  # 语法错误
SELECT JSON_CONTAINS('[1,"2",3]', '"2"', '3');  # 语法错误
SELECT JSON_CONTAINS('[1,"2",3]', '"2"', '$[1]', '3', '$[2]');  # 语法错误
SELECT JSON_CONTAINS('[1,"2",3]', '"2"', '$[1]');  # 1
SELECT JSON_CONTAINS('[1,"2",3]', '"2"', '4');  # 语法错误

示例2：使用JSON_OBJECT或JSON_ARRAY函数嵌套

1
2
3
4
5
6
7
8
9
10
11
12
SELECT JSON_CONTAINS('{"a":1,"b":{"c":3}}', JSON_OBJECT('a', 1));  # 1
SELECT JSON_CONTAINS('{"a":1,"b":{"c":3}}', JSON_OBJECT('a', 2));  # 0
SELECT JSON_CONTAINS('{"a":"1","b":{"c":3}}', JSON_OBJECT('a', 1));  # 0
SELECT JSON_CONTAINS('{"a":"1","b":{"c":3}}', JSON_OBJECT('a', '1'));  # 1

SELECT JSON_CONTAINS('[1,"2"]', JSON_ARRAY(1));  # 1
SELECT JSON_CONTAINS('[1,"2"]', JSON_ARRAY("1"));  # 0
SELECT JSON_CONTAINS('[1,"2"]', JSON_ARRAY("2"));  # 1

SELECT JSON_CONTAINS('[1,"2",3]', JSON_ARRAY(3,"2"));  # 1
SELECT JSON_CONTAINS('[1,"2",3]', JSON_ARRAY("2",3));  # 1
SELECT JSON_CONTAINS('[1,"2",3]', JSON_ARRAY("2",4));  # 0

示例3：JSON_CONTAINS_PATH用法（第二个参数用于决定是否全部匹配多个值）

1
2
3
4
5
6
7
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'one', '$.a');  # 1
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'one', '$.c');  # 0
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'one', '$.b.c');  # 1
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'one', '$.a', '$.c');  # 1
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'one', '$.c', '$.d');  # 0
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'all', '$.a', '$.c');  # 0
SELECT JSON_CONTAINS_PATH('{"a":"1","b":{"c":3}}', 'all', '$.a', '$.b');  # 1

返回查询数据的索引位置。

示例：

1
2
3
SELECT JSON_SEARCH('{"a":"123","b":{"a":123,"b":"123"}}', 'all', '123');  # ["$.a", "$.b.b"]
SELECT JSON_SEARCH('{"a":"123","b":{"a":123,"b":"123"}}', 'one', '123');  # "$.a"
SELECT JSON_SEARCH('{"a":"123","b":{"a":123,"b":"123"}}', 'one', '12');  # NULL