mysql的json类型

create table if not exists products (id int auto_increment primary key, details json);

json类型字段可以指定为not null，但不能指定默认值。事实上，一共有4种类型不能指定默认值，分别是blob、text、geometry、json。

json类型字段在插入时会校验数据是否是json格式，如果不是的话，插入时会报错。varchar类型字段，如果存的是json格式的字符串，那么同样可以使用各种json函数，如果不是json格式字符串，那么使用json函数会报错。json格式既包括json对象格式，如'{"x":1}'，又包括json数组格式，如'[{"x":1},{"x":2}]'。

初始化数据：

insert into products (details) values ('{"tags":["tag1","tag2","tag3"]}'),('{"tags":["tag1","tag4","tag5"]}');

插入json数据和插入varchar数据一样，都要用单引号包住。

1、json_object

构造一个json对象。语法是

json_object(key, value[, key2, value2, ...])

key和value是成对出现的，如果key有重复，那么后面的key-value会覆盖前面的key-value。

select json_object('name', 'Jim', 'age', 20)，会返回{"age": 20, "name": "Jim"}

json_object可以嵌套，如select json_object('name', 'Tim', 'age', 20, 'friend', json_object('name', 'Jim', 'age', 20))，会返回{"age": 20, "name": "Tim", "friend": {"age": 20, "name": "Jim"}}

2、json_array

构造一个json数组。语法是

json_array(value1[, value2, ...])

元素可能会自动转换，如TRUE被转换为true，FALSE被转换为false，NULL被转换为null，日期、时间、日期时间被转换为字符串。

如select json_array(1,2,3)会返回[1,2,3]

select json_array(TRUE,FALSE,NULL)，会返回[true, false, null, "2026-05-25", "15:12:10.000000", "2026-05-25 15:12:10.000000"]

json_array可以嵌套，如select json_array(json_array(123,456),json_array("abc","def"))，会返回[[123,456],["abc","def"]]

select json_array(json_object('name','Jim','age',20),json_object('name','Tim','age',18))，会返回

select json_contains(json_array(1,2,3),'3')，会返回1

3、json_valid

校验入参是否是json文档，语法是

json_valid(str)

如果参数为null，则会返回null。否则，如果是一个有效的json文档，就返回1，如果不是，就返回0。

select json_valid(10)，返回0。

select json_valid('"10"')，返回1。

select json_valid(true)，返回0。

select json_valid('true')，返回1。

select json_valid('abc')，返回0。

select json_valid('"abc"')，返回1。

select json_valid('{"x":1}')，返回1。

select json_valid('{a:1}')，返回0。

4、json_type

返回给定的json值的类型，语法是

json_type(json_value)

如果参数为null，则将返回null，否则将返回一个字符串，可选项有：

OBJECT，表示json值是一个json对象。如select json_type(json_object('x', 1, 'y', 2))

ARRAY，表示json值是一个json数组。如select json_type(json_array(1, 2, 3))

BOOLEAN，表示json值是一个json布尔值。如select json_type('true')

NULL，表示json值是json null值。如select json_type('null')

INTEGER，表示json值是一个json。如json_type('5')

DOUBLE，如select json_type('5.0')

STRING，如select json_type('"5"')

DECIMAL

DATETIME

DATE

TIME

BLOB

OPAQUE，其他情况

5、json_contains

判断json对象格式的文档是否包括某个键值对，或者json数组格式的文档是否包含某个元素。语法是

json_contains(target_json, candidate_json[, path])

path是一个路径表达式，如果提供了path参数，则会检查path匹配的部分是否包含candidate_json，否则会检查target_json是否包含candidate_json。

如果target_json或者candidate_sjon为null，或者path指定的部分不存在，则会返回null。

select json_contains('{"x":1,"y":2}','{"x":1}')，返回1，表示包含。

select json_contains('{"x":1,"y":2}','{"x":2}')，返回0

select json_contains('{"x":1,"y":[2,3],"z":[4,5]}','4','$.z')，返回1。通过$.z指定从json对象的z属性值中查找，即从[4,5]中查找。

select json_contains('[1,2,3]','1')，返回1

select json_contains('[1,2,3]','"2"')，返回0

select json_contains('[1,2,[3,4]]','2','$[2]')，返回0。通过$[2]指定从json数组的第三个元素中找，即从[3,4]中查找。

6、json_contains_path

判断一个json文档在指定的路径上是否有值存在，语法是

json_contains_path(json, one_or_all, path[, path, ...])

one_or_all，要么是'one'，要么是'all'，如果是one的话，那么只要任一路径有值，就返回1。如果是all，则全部路径都有值时，才返回1。

path是路径表达式，至少要指定一个路径表达式。

如果有参数为null，则会返回null。

select json_contains_path('[1, 2, {"x": 3}]', 'one', '$[1]')，判断json数组是否有第二个元素，返回1。

select json_contains_path('[1, 2, {"x": 3}]', 'one', '$[2].x')，判断json数组第三个元素是否有x属性，返回1。

select json_contains_path('[1, 2, 3]', 'one', '$[2]', '$[3]')，判断json数组是否有第三个元素或者是否有第四个元素，返回1。

select json_contains_path('[1, 2, 3]', 'all', '$[2]', '$[3]')，判断json数组是否有第三个元素以及是否有第四个元素，返回0，因为没有第四个元素。

7、json_extract

返回json文档中指定路径的值，语法是

json_extract(json, path[, ...])

json文档可以是json对象，也可以是json数组。如果路径表达式匹配了一个值，就返回该值。如果路径表达式匹配了多个值，或者有多个路径表达式，则会返回一个json数组。

select json_extract('{"x": 1, "y": 2}', '$.x')，返回1，是json integer类型。

select json_extract('{"x":1, "y":2, "z":3}', '$.x', '$.z')，返回[1, 3]，是json数组类型。

select json_extract('[1, 2, 3]', '$[0]')，返回1，是json integer类型。

select json_extract('[1, 2, 3]', '$[0]', '$[2]')，返回[1, 3]，是json数组类型。

select json_extract('[1, 2, {"x": 1}]', '$[2]')，返回{"x": 1}，是json对象类型。

select json_extract('[1, 2, {"x": 3}]', '$[2].x', '$[1]')，返回[3, 2]，是json数组类型。

8、json_value

返回json文档中指定路径的值。返回的不是json类型，而是标量sql类型。语法是

json_value(json, path returning type [on_empty] [on_error])

type的可选项有float、double、decimal、signed、unsigned、date、time、datetime、year、char、json。没有int，如果想返回整数，可以用signed、unsigned，前者表示整数，后者表示正整数。没有varchar，如果想返回字符串，可以用char。

select
json_extract('[10000,"2025-09-09 12:12:12",30000]','$[1]'),
json_extract('[10000,"2025-09-09 12:12:12",30000]','$[1]', '$[2]'),
json_value('[10000,"2025-09-09 12:12:12",30000]','$[1]' returning char)
from t limit 1

json_value等价于cast(json_unquote(json_extract(json_doc, path)) as type)，是后者的简写。

9、json_overlaps

检测两个json文档是否有交集，如两个json对象是否有相同的键值对，两个json数组是否有相同元素。语法是

json_overlaps(json1, json2)

如果有参数为null，则会返回null。否则，

比较两个json对象时，如果有相同的键值对，则返回1，否则返回0。

比较两个json数组时，如果有相同的元素，则返回1，否则返回0。

比较两个纯值时，如果两个值相同，则返回1，否则返回0。

比较纯值和json数组时，如果值是这个数组中的元素，则返回1，否则返回0。

比较纯值和json对象的结果为0，比较json对象和json数组的结果为0。

select json_overlaps('{"x": 1, "y": 2}', '{"x": 2, "y": 3}')，返回0。

select json_overlaps('{"x": 1, "y": 2}', '{"x": 2, "y": 2}')，返回1。

select json_overlaps('[1, 2, 3]', '[3, 4, 5]')，返回1。

select json_overlaps('1', '1')，返回1。

select json_overlaps('1', '[1, 2, 3]')，返回1。

json_overlaps函数不会对参数的数据类型进行转换，如select json_overlaps('1', '"1"')，返回0。

10、json_search

返回一个给定字符串在一个json文档中的路径。语法是

json_search(json, one_or_all, search_str[, escape_char[, path]])

one_or_all，值是'one'或者'all'。如果是'one'，则会返回第一个匹配的路径 ，否则会把所有匹配的路径包装在一个数组内返回。

search_str可以使用%或_通配符。%可以匹配任意数量的任意字符，_可以匹配一个任意字符。

如果有参数为null，或者未搜索到指定的字符串，或者指定的path不存在，则json_search函数会返回null。

select json_search('[1, 2, 3, 4]', 'one', '1')，返回null。

select json_search('["1", 2, 3, 4]', 'one', '1')，返回"$[0]"。

select json_search('{"name": "Tim", "hobbies": [{"name": "TikTok", "year": 10}]}', 'one', 'Tim')，返回"$.name"。

select json_search('{"name": "Tim", "hobbies": [{"name": "TikTok", "year": 10}]}', 'all', 'T%')，返回["$.name", "$.hobbies[0].name"]。

还有其他json函数，不一一列举，使用时可以参考https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html。