概述
本章，您将学习 MySQL 的特殊数据类型 —— JSON 。
了解 JSON
JSON：一种轻量级的数据交换格式，被广泛应用于 Web 开发和数据传输领域。
数据交换格式：指不同计算机程序之间或编程语言之间用来交换数据时所使用的一种标准化格式。除了 JSON，还有 XML、YAML、CSV 等。
JSON 语法
基本语法规则：
- 数据始终以 name/value 对的形式进行表示
- JSON 对象书写在大括号中
{...}，对象可以包含多个 name/value 对 - JSON 数组书写在方括号中
[...]，数组可以包含多个 value - 所有 name 必须 使用双引号包含
- JSON 文件的文件后缀为
.json - 空对象用
{}表示 - 空数组用
[]表示
name/value 对的集合：在不同编程语言中给出的名称定义不同，如对象（object）、记录（record）、结构（strut）、关联数组（associative array）等，在大多数情况下，对象这个名称最常用。
值的有序列表：在绝大多数编程语言中被称为数组（array）
对象 是一个无序的 name/value 对集合，以 "{" 开始，以 "}" 结束，每个 name 后面跟一个冒号，name/value 对之间使用逗号分隔（最后一个 name/value 的后面不需要再使用逗号分隔）。比如这样的一个 JSON 对象：
{
"name": "FrankLee",
"age": 20,
"city": "GuangZhou"
}数组 是值的有序集合，以 "[" 开始，以 "]" 结束，值之间用逗号分隔（最后一个值不需要再使用逗号分隔）。比如这样的：
{
"link": [
{
"name": "bing",
"url": "https://bing.com"
},
{
"name": "mysql",
"url": "https://mysql.com"
},
{
"name": "docs",
"url": "https://docs.rockylinux.org"
}
]
} 在这个数组中，link 是 name，value 是 [{...},{...},{...}] 这样包含三个值的数组，每个值都是一个对象。数组的下标用来定义值的位置，从 0 开始记。
通过 该网站 可在线校验 JSON 语法
value 类型
在 JSON 中，value 可以是：
- 数字 - 整数或浮点
- 字符串 - 需要使用双引号包含
- 逻辑值 - true 或 false
- 数组 - 使用方括号包含
- 对象 - 使用花括号包含
- null
不同的 value 类型之间可以互相嵌套。
MySQL 中 JSON 数据类型
若需要将某字段的数据类型定义为 JSON，使用到的关键字为 json
use home;
# 创建表
create table if not exists jsontable(
jsondoc json
);
# 插入两行 JSON 对象数据
## 插入数据时，请使用单引号将 JSON 格式的内容包含
insert into
jsontable (jsondoc)
values
(
'{"name": "FrankLee", "age": 20, "city": "GuangZhou"}'
),
(
'{"link": [
{
"name": "bing",
"url": "https://bing.com"
},
{
"name": "mysql",
"url": "https://mysql.com"
},
{
"name": "docs",
"url": "https://docs.rockylinux.org"
}
]
}'
);
# 查询数据
select * from jsontable;
jsondoc
{"age":20,"city":"GuangZhou","name":"FrankLee"}
{"link":[{"url":"https://bing.com","name":"bing"},{"url":"https://mysql.com","name":"mysql"},{"url":"https://docs.rockylinux.org","name":"docs"}]}通常来说，单个 JSON 数据类型的数据占用的存储空间与 longblob 或 longtext 数据类型大致相同，但是由于需要存储额外的二进制编码信息，因此会产生额外的存储空间开销（例如存储 JSON 当中的字符串就需要占用额外的 4~10 字节）
# 输出 56
select json_storage_size('{"age":20,"city":"GuangZhou","name":"FrankLee"}');
# 输出 183
select json_storage_size(
'{"link": [
{
"name": "bing",
"url": "https://bing.com"
},
{
"name": "mysql",
"url": "https://mysql.com"
},
{
"name": "docs",
"url": "https://docs.rockylinux.org"
}
]
}'
);MySQL 中 JSON 的路径表达式
假设有这样的一个 JSON 对象，包含一个 name/value 对，value 是包含三个值的数组
{"link": [
{
"name": "bing",
"url": "https://bing.com"
},
{
"name": "mysql",
"url": "https://mysql.com"
},
{
"name": "docs",
"url": "https://docs.rockylinux.org"
}
]
}$- 表示正在操作的 JSON 文档$.link- 表示 link 这条 JSON 路径$.link[2]- 表示 link 路径上下标为 2 的值，JSON 数组的下标从 0 开始记$.link[*]- * 表示数组中的所有下标$.*- 表示 JSON 对象中的所有 name/value 对（MySQL 中称为 key/value）$[M to N]- 对于数组，用下标的起始范围表示连续的数组值，例如$.link[0 to 2]
MySQL 中 JSON 值的比较
支持常见的条件表达式，如：
>- 表示大于<- 表示小于>=- 表示大于等于<=- 表示小于等于<>- 表示不等于<=>- 表示安全等于，不太常用=- 表示等于
对于 JSON 值，以下的不支持：
-
between ... and- 范围值，包含边界，也就是闭区间，大家很熟悉 -
in( )- 包含的值，很常见且熟悉 -
least( )- 返回多个值参数中最小的那个select least(100, 50, 200, 10) asmin-value; +-----------+ | min-value | +-----------+ | 10 | +-----------+ 1 row in set (0.00 sec) -
greatest( )- 返回多个值参数中最大的那个。select greatest(100, 50, 200, 10) asmax-value; +-----------+ | max-value | +-----------+ | 200 | +-----------+ 1 row in set (0.00 sec)
常见的 JSON 函数
所有的 JSON 函数信息参阅这里 —— https://dev.mysql.com/doc/refman/8.4/en/json-function-reference.html
创建 JSON 值的函数
-
json_array( ) 函数 - 将一个或多个参数转换为 JSON 数组格式
# 输出为 ["id",null,576,"name"] select json_array('id',null,576,'name'); -
json_object( ) 函数 - 将一个或多个 key-value 对转换为 JSON 对象，其用法为
JSON_OBJECT([key, val[, key, val] ...])。在 MySQL 8 中，JSON 当中的 name/value 对被定义为 key/value 对，因此在使用相应的 JSON 函数时需注意。# 输出为 {"age":20,"city":"GuangZhou"} select json_object('city','GuangZhou','age',20); -
json_quote( ) 函数 - 将给定的字符串转义为合法的 JSON 值字符串。
# 输出为 {"welcome":"\"json\""} select json_object('welcome',json_quote('json')); -
json_unquote( ) 函数 - 与 json_quote 函数相对，用来取消双引号包含的 JSON 值
select json_unquote('"string"'); +--------------------------+ | json_unquote('"string"') | +--------------------------+ | string | +--------------------------+ 1 row in set (0.00 sec)
搜索 JSON 值的函数
通过下面的函数，您可以对 JSON 值执行搜索或比较操作，以从中提取数据或者报告其中某个位置是否存在数据或报告数据的路径位置。
-
json_contains( ) 函数 - 用来检查一个 JSON 文档中是否包含指定的 JSON 文档或值，用法为
json_contains(target, candidate[, path])。在用法中，target 参数表示您要检查的目标 JSON 文档，candidate 参数表示要查找的内容，path 为可选参数，表示搜索的 JSON 路径，默认为整个文档。函数的返回值如下：- 1 - 目标 JSON 文档包含所选的内容
- 0 - 目标 JSON 文档不包含所选的内容
- null - 参数为 NULL
# jsondoc 字段中，JSON 文档中的 city 这个 name 对应的 value 是否包含 GuangZhou 字符串，如果有，则输出对应的 JSON 行数据 ## 字符串最外层是单引号，单引号里面是双引号包含的字符串 ## 输出为 {"age":20,"city":"GuangZhou","name":"FrankLee"} select * from jsontable where json_contains(jsondoc,'"GuangZhou"', '$.city'); # JSON 对象中是否包含特定的 name/value 对 ## 输出为 {"age":20,"city":"GuangZhou","name":"FrankLee"} select * from jsontable where json_contains(jsondoc,'{"name": "FrankLee"}'); # 搜索 link 这个 name（key） 中包含特定值的 JSON 行数据 select * from jsontable where json_contains( jsondoc,'{"name": "docs","url": "https://docs.rockylinux.org"}','$.link' ); -
json_search( ) 函数 - 用来精准定位 JSON 行数据，用法为
json_search(json_doc, one_or_all, search_str[, escape_char[, path] ...])。其中 json_doc 参数表示目标文档；one 表示第一个匹配的路径，all 表示以数组形式显示所有搜索的路径；search_str 给定搜索的字符串，支持 like 关键字风格的通配符（如%a%）；escape_char 表示可选的转义字符；path 表示可选的 JSON 路径。众所周知，MySQL 中的变量分为系统变量、用户变量以及特殊的局部变量。系统变量有全局级（global，影响所有的会话）和会话级（session，仅影响当前连接的会话），查询与修改变量值时，系统变量需要用到
@@；用户变量只能作用于当前的连接会话，查询与赋值时需要用到@，断开连接后释放变量；局部变量用在begin...end代码块中，必须通过DECLARE关键字进行显式声明，然后用set关键字进行赋值。我们可以使用
set关键字来设置一个用户变量，例如：# 声明变量并且赋值 ## 这里我们创建了一组 JSON 数组，包含两个用户的基本信息 set @customjson:='[ { "name": "tom", "age": 20, "info": [ {"ipbind": "192.168.100.20", "software": "mysql","version": 8.4}, {"weight": "65kg","height": "175cm"} ] }, { "name": "jessica", "age": "22", "info": [ {"ipbind": "192.168.1.10", "software": "Redis","version": 7}, {"weight": "65kg", "height": "160cm"} ] } ]'; select @customjson; # 搜索字符串 ## 输出内容表示搜索值所在的路径 select json_search(@customjson, 'one', 22); +-------------------------------------+ | json_search(@customjson, 'one', 22) | +-------------------------------------+ | "$[1].age" | +-------------------------------------+ 1 row in set (0.00 sec) # one 参数与 all 参数的区别 select json_search(@customjson, 'one', '65kg') as a,json_search(@customjson, 'all', '65kg') as b; +-----------------------+------------------------------------------------+ | a | b | +-----------------------+------------------------------------------------+ | "$[0].info[1].weight" | ["$[0].info[1].weight", "$[1].info[1].weight"] | +-----------------------+------------------------------------------------+ 1 row in set (0.00 sec) -
json_extract( ) 函数 - 给定路径提取 JSON 中的数据值，用法为
json_extract(json_doc, path[, path] ...)，json_doc 表示要处理的 JSON 文档，PATH 表示 JSON 的路径。例如：select json_extract(@customjson, '$[1]'); +--------------------------------------------------------------------------------------------------------------------------------------------------+ | {"age": "22", "info": [{"ipbind": "192.168.1.10", "version": 7, "software": "Redis"}, {"height": "160cm", "weight": "65kg"}], "name": "jessica"} | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) # 也可从一个 JSON 对象中提取数据值，例如 select json_extract('{"x":1, "y": 10 , "z": [100,1000]}', '$.z', '$.x') asc1; +------------------+ | c1 | +------------------+ | [[100, 1000], 1] | +------------------+ 1 row in set (0.00 sec) -
json_keys( ) 函数 - 以数组形式返回一个 JSON 对象中最顶层的所有 name （key），用法为
json_keys(json_doc[, path])，json_doc 表示要处理的 JSON 对象（不能是其他类型，否则返回 NULL），path 表示可选的路径。select json_keys('true'),json_keys('[a,b]'),json_keys('"json"'); +-------------------+------------------------+---------------------+ | json_keys('true') | json_keys('["a","b"]') | json_keys('"json"') | +-------------------+------------------------+---------------------+ | NULL | NULL | NULL | +-------------------+------------------------+---------------------+ 1 row in set (0.00 sec) select json_keys('{"x":1, "y": 10 , "z": [100,1000]}') askeys; +-----------------+ | keys | +-----------------+ | ["x", "y", "z"] | +-----------------+ 1 row in set (0.00 sec)
返回 JSON 值属性的函数
-
json_type( ) 函数 - 接受一个 JSON 参数，并解析为相应的类型。
# 返回 OBJECT select json_type('{"age":20,"city":"GuangZhou","name":"FrankLee"}'); # 返回 ARRAY select json_type('["2025-09-05", "2025-09-05 14:08"]'); # 返回 STRING select json_type('"hello"'); -
json_depth( ) 函数 - 接受一个 JSON 参数，用来返回 JSON 文档的最大深度。深度指的是 JSON 文档结构中的嵌套层级。
# 返回 2 select json_depth('{"age":20,"city":"GuangZhou","name":"FrankLee"}');JSON 文档深度的计算规则：
- 空数组、空对象或标量值的深度为 1
- 仅包含深度为 1 的元素的非空数组，或仅包含深度为 1 的成员值的非空对象，其深度为 2
- 除此之外的其他 JSON 文档的深度都大于 2
-
json_length( ) 函数 - 用来返回 JSON 文档的长度，用法为
json_length( json_doc[, path])，json_doc 参数指定要计算的 JSON 文档，path 参数指定计算长度的路径# 返回 3 select json_length('{"age":20,"city":"GuangZhou","name":"FrankLee"}'); # 返回 3，即数组的元素个数 select json_length('{"a": 1 , "x": 100 , "y": 50, "z": ["ab","cd","dz"]}', '$.z');长度的计算规则：
- 标量值的长度为 1
- 数组的长度等于元素的数量
- 对象的长度等于 name/value 对的数量
- 嵌套的数组或对象不参与到长度的计算
-
json_valid( )函数 - 以 0 或 1 返回值来检查 JSON 文档是否有效
# 返回 1 select json_valid('{"a": 1 , "x": 100 , "y": 50, "z": ["ab","cd","dz"]}');
JSON 表函数
使用 json_table( )函数 可从指定的 JSON 文档中提取数据并返回一个具有指定列的关系表，用法为 json_table(expr, path COLUMNS (column_list) [AS] alias)，其完整的用法为：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
column_list:
column[, column][, ...]
column:
name FOR ORDINALITY
| name type PATH string_path [on_empty] [on_error]
| name type EXISTS PATH string_path
| NESTED [PATH] path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR- expr - 必需参数，指定 JSON 文档。可以是 MySQL 中表的列，也可以是常量或给定的 JSON 文档
- path - 必需参数，JSON 问的路径表达式
-
column - 必需参数，指定一个或多个列。该函数支持以下 4 种类型的列：
- name FOR ORDINALITY - 生成一个名称为 name 且初始为 1 的计数器（相当于创建表时将列定义为自增长列（
auto_incrment）） - name type PATH string_path [on_empty] [on_error] - 将路径表达式 string_path 指定的值放入到 name 列中
- name type EXISTS PATH string_path - 若路径表达式 string_path 对应的位置存在值，则将该值放入到 name 列中。type 可以是任何 MySQL 数据类型，但通常为某种 int 类型
- NESTED [PATH] path COLUMNS (column_list) - 将内嵌的对象或者数组中的数据拉平放在一行中
on_empty 表示在数据缺失的情况下执行的操作，可以是以下值：
- NULL ON EMPTY - 该列设置为 NULL，这是默认行为
- DEFAULT json_string ON EMPTY - 提供的 json_string 被解析为 JSON，只要它是有效的，将存储并代替缺失的值
- ERROR ON EMPTY - 直接抛出错误
on_error 用来处理错误逻辑，可以是以下值：
- NULL ON ERROR - 该列设置为 NULL；这是默认行为。
- DEFAULT json_string ON ERROR - json_string 被解析为 JSON（假设它有效）并存储
- ERROR ON ERROR - 抛出错误
# 简单示例 select * from json_table( '[{"x": "json", "y": "line"},{"x": "list", "y": "yes"}]', '$[*]' COLUMNS( xcol varchar(10) PATH '$.x', ycol varchar(10) PATH '$.y' ) ) ast1; +------+------+ | xcol | ycol | +------+------+ | json | line | | list | yes | +------+------+ 2 rows in set (0.00 sec) # 提取指定路径的 JSON 数组 select * from json_table( '[{"x": "json", "y": "line"},{"x": "list", "y": "yes"}]', '$[1]' COLUMNS( xcol varchar(10) PATH '$.x', ycol varchar(10) PATH '$.y' ) ) ast2; +------+------+ | xcol | ycol | +------+------+ | list | yes | +------+------+ 1 row in set (0.00 sec) # 使用自增长 select * from json_table( '[{"x": "json", "y": "line"},{"x": "list", "y": "yes"}]', '$[*]' COLUMNS( id FOR ORDINALITY, xcol varchar(10) PATH "$.x", ycol varchar(10) PATH "$.y" ) ) ast3; +------+------+------+ | id | xcol | ycol | +------+------+------+ | 1 | json | line | | 2 | list | yes | +------+------+------+ 2 rows in set (0.01 sec) # 当对应的 JSON 数据缺失，使用默认值 select * from json_table( '[{"x": 100, "y": "line"},{"y": "yes"}]', '$[*]' COLUMNS( id FOR ORDINALITY, xcol int PATH '$.x' DEFAULT '20' ON EMPTY, ycol varchar(10) PATH '$.y' ) ) ast3; +------+------+------+ | id | xcol | ycol | +------+------+------+ | 1 | 100 | line | | 2 | 20 | yes | +------+------+------+ 2 rows in set (0.04 sec) # 拉平嵌套的数组 ## 这里使用了 NESTED PATH '$.b[*]' 子句展开 b 对应的数组，并将 b 数组中的每个元素放入名称为 b1 的列中。因为每个 b 数组中都有 2 个元素，因此一个 b 数组转化为关系表中的两行。 select * from json_table( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]} ]', '$[*]' COLUMNS( a int PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$') ) ) ast4; +------+------+ | a | b1 | +------+------+ | 1 | 11 | | 1 | 111 | | 2 | 22 | | 2 | 222 | +------+------+ 4 rows in set (0.00 sec) # 拉平嵌套的对象 select * from json_table( '[ {"x": 10, "y": {"y1": 12, "y2": 33} }, {"x": 50, "y": {"y1": 36, "y2": 69} } ]', '$[*]' COLUMNS( x int PATH '$.x', NESTED PATH '$.y' COLUMNS ( one INT PATH '$.y1', two INT PATH '$.y2' ) ) ) ast5; +------+------+------+ | x | one | two | +------+------+------+ | 10 | 12 | 33 | | 50 | 36 | 69 | +------+------+------+ 2 rows in set (0.00 sec) - name FOR ORDINALITY - 生成一个名称为 name 且初始为 1 的计数器（相当于创建表时将列定义为自增长列（
JSON 实用函数
-
json_storage_size( ) 函数 - JSON 文档的二进制表示所占用的大小，以字节为单位，用法为
json_storage_size(json_val)，json_val 参数可以是一个 JSON 文档、JSON 字符串、逻辑值、null 、数据类型为 json 的列、MySQL 用户变量等。# 一个 JSON 对象 select json_storage_size('{"a": 1 , "x": 100 , "y": 50, "z": ["ab","cd","dz"]}') asc1; +------+ | c1 | +------+ | 59 | +------+ 1 row in set (0.00 sec) # 一个 JSON 字符或 JSON 字符串 select json_storage_size('"a"') asc2, json_storage_size('"hello"') asc3; +------+------+ | c2 | c3 | +------+------+ | 3 | 7 | +------+------+ 1 row in set (0.00 sec) # 一个逻辑值 select json_storage_size('true') asc4, json_storage_size('false') asc5; +------+------+ | c4 | c5 | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) # NULL select json_storage_size('null') asnullcol; +---------+ | nullcol | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) # 字段。这个表的创建在文章的开头且有两行 JSON 数据 use home; select json_storage_size(jsondoc) asc6from jsontable; +------+ | c6 | +------+ | 56 | | 183 | +------+ 2 rows in set (0.01 sec) -
json_pretty( ) 函数 - 对 JSON 文档进行美化，使其输出格式更加便于人类阅读，用法为
json_pretty(json_val)。若 json_val 参数不是一个有效的 JSON 文档，则此函数会输出错误的信息。select json_pretty('{"a": 1 , "x": 100 , "y": 50, "z": ["ab","cd","dz"]}') as "p1"; +--------------------------------------------------------------------------------+ | p1 | +--------------------------------------------------------------------------------+ | { "a": 1, "x": 100, "y": 50, "z": [ "ab", "cd", "dz" ] } | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -
json_storage_free( ) 函数 - 当 JSON 文档被特定的 JSON 函数（如 json_set( )、json_replace( ) 、json_remove( )）更新后，输出释放存储大小的信息，其用法为
json_storage_free(json_val)。# 新增 id 字段至表首 alter table jsontable add id int primary key auto_increment first; # 查阅表结构 desc jsontable; +---------+------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | jsondoc | json | YES | | NULL | | +---------+------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) # 查看所有数据 select * from jsontable; | id | jsondoc | +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | {"age": 20, "city": "GuangZhou", "name": "FrankLee"} | | 2 | {"link": [{"url": "https://bing.com", "name": "bing"}, {"url": "https://mysql.com", "name": "mysql"}, {"url": "https://docs.rockylinux.org", "name": "docs"}]} | +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) # 查看 id 为 1 的这行 JSON 数据占用的空间 select json_storage_size(jsondoc) asctempfrom jsontable where id=1; +-------+ | ctemp | +-------+ | 56 | +-------+ 1 row in set (0.00 sec) # 更新 id 为 1 的这行 json 数据 update jsontable set jsondoc='{"age": 20, "city": "GuangZhou", "name": "FrankLee", "level": 3}' where id=1; select json_storage_free(jsondoc) asfreefrom jsontable where id=1; +------+ | free | +------+ | 0 | +------+ 1 row in set (0.00 sec) update jsontable set jsondoc='{"age": 20, "city": "GuangZhou", "name": "FrankLee", "level": [1,2,3]}' where id=1; select json_storage_free(jsondoc) asfreefrom jsontable where id=1; +------+ | free | +------+ | 0 | +------+ 1 row in set (0.00 sec)如您所见，使用
update...set语法的方式了特定 JSON 文档，但 json_storage_free( ) 函数的输出依然为 0，为什么？因为我们没有使用 json_set( )、json_replace( ) 、json_remove( ) 这样的函数去更新。
修改 JSON 值的函数
-
json_array_append( ) 函数 - 向 JSON 文档中的指定数组追加值并返回追加后的 JSON 文档结果。该函数的用法为
json_array_append(json_doc, path, val[, path, val] ...)，其中 json_doc、path、val 都是必需的参数，json_doc 参数表示要修改的目标 JSON 文档；path 参数表示有效的 JSON 路径；val 参数表示追加的新值。# 在数组的结尾追加值 select json_array_append('["blue", "red", "green"]', '$', "white"); +-------------------------------------------------------------+ | json_array_append('["blue", "red", "green"]', '$', "white") | +-------------------------------------------------------------+ | ["blue", "red", "green", "white"] | +-------------------------------------------------------------+ 1 row in set (0.00 sec) # 向嵌套数组的结尾追加值。数组的下标从 0 开始记，表示第一个值 select json_array_append('[1, ["blue", "red", "green"], "info"]', '$[1]', "white"); +-----------------------------------------------------------------------------+ | json_array_append('[1, ["blue", "red", "green"], "info"]', '$[1]', "white") | +-----------------------------------------------------------------------------+ | [1, ["blue", "red", "green", "white"], "info"] | +-----------------------------------------------------------------------------+ # 在嵌套数组的 0 下标位置追加新值并生成新的另外一组数组 select json_array_append('[1, ["blue", "red", "green"], "info"]', '$[1][0]', "white"); +--------------------------------------------------------------------------------+ | json_array_append('[1, ["blue", "red", "green"], "info"]', '$[1][0]', "white") | +--------------------------------------------------------------------------------+ | [1, [["blue", "white"], "red", "green"], "info"] | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) # 向 JSON 对象中的数组追加新值 select json_array_append('{"config": "yes", "item":["bind","include","max"]}', '$.item', 'min'); +------------------------------------------------------------------------------------------+ | json_array_append('{"config": "yes", "item":["bind","include","max"]}', '$.item', 'min') | +------------------------------------------------------------------------------------------+ | {"item": ["bind", "include", "max", "min"], "config": "yes"} | +------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -
json_array_insert( ) 函数 - 向 JSON 文档中指定数组的指定位置插入新值，用法为
json_array_insert(json_doc, path, val[, path, val] ...)，这里的 json_doc、path、val 参数都是必需的。# 向数组的开头插入新值 select json_array_insert('["blue", "red", "green"]', '$[0]', 'white') ascol-insert; +-----------------------------------+ | col-insert | +-----------------------------------+ | ["white", "blue", "red", "green"] | +-----------------------------------+ 1 row in set (0.00 sec) # 将新值插入到第一个值的后面，新值的下标此时为 1 select json_array_insert('["blue", "red", "green"]', '$[1]', 'black') ascol-insert; +-----------------------------------+ | col-insert | +-----------------------------------+ | ["blue", "black", "red", "green"] | +-----------------------------------+ 1 row in set (0.00 sec) # 将新值插入到对象嵌套的数组中 select json_array_insert('{"config": "yes", "item":["bind","include","max"]}','$.item[1]', 'cache') ascol-insert; +----------------------------------------------------------------+ | col-insert | +----------------------------------------------------------------+ | {"item": ["bind", "cache", "include", "max"], "config": "yes"} | +----------------------------------------------------------------+ 1 row in set (0.00 sec) -
json_insert( ) 函数 - 向 JSON 文档中插入新的数据，用法为
json_insert(json_doc, path, val[, path, val] ...)，这里的 json_doc、path、val 参数都是必需的。# 在 JSON 对象中添加新的 name/value 对，即 "y": [1,5,10,9] select json_insert('{"config": "yes", "item":["bind","include","max"]}', '$.y', '[1,5,10,9]') ascol-insert; +--------------------------------------------------------------------------+ | col-insert | +--------------------------------------------------------------------------+ | {"y": "[1,5,10,9]", "item": ["bind", "include", "max"], "config": "yes"} | +--------------------------------------------------------------------------+ 1 row in set (0.01 sec) # 在数组中插入新值。请注意新值需要插入到相应下标没有值的位置，如下所示，这样是无法插入的，因为 $[1] 位置已经有 "red" 值了，这点与 json_array_insert( ) 函数是不同的 select json_insert('["blue", "red", "green"]', '$[1]', 'black') ascol-insert; +--------------------------+ | col-insert | +--------------------------+ | ["blue", "red", "green"] | +--------------------------+ 1 row in set (0.00 sec) ## 正确的做法 select json_insert('["blue", "red", "green"]', '$[3]', 'black') ascol-insert; +-----------------------------------+ | col-insert | +-----------------------------------+ | ["blue", "red", "green", "black"] | +-----------------------------------+ 1 row in set (0.00 sec) -
json_remove( ) 函数 - 从 JSON 文档的指定路径位置删除数据，用法为
json_remove(json_doc, path[, path] ...)，json_doc 与 paht 参数都是必需的。# 从数组中移除 red 这个值 select json_remove('["blue", "red", "green"]','$[1]') ascol-remove; +-------------------+ | col-remove | +-------------------+ | ["blue", "green"] | +-------------------+ 1 row in set (0.01 sec) # 从对象中移除一个 name/value 对 select json_remove('{"config": "yes", "item":["bind","include","max"]}', '$.item') ascol-remove; +-------------------+ | col-remove | +-------------------+ | {"config": "yes"} | +-------------------+ 1 row in set (0.00 sec) -
json_replace( ) 函数 - 替换 JSON 文档中已经存在的数据，用法为
json_replace(json_doc, path, val[, path, val] ...)，其中 json_doc、path、val 都是必需的参数。# 从 JSON 数组中替换已经存在的数据 select json_replace('["blue", "red", "green"]', '$[1]', 'yellow') ascol-replace; +-----------------------------+ | col-replace | +-----------------------------+ | ["blue", "yellow", "green"] | +-----------------------------+ 1 row in set (0.00 sec) # 输出的 true 是字符串，并不是预期的 {"z": true} 这样的逻辑值 select json_replace('{"z": 100}', '$.z', 'true') ascol-replace; +---------------+ | col-replace | +---------------+ | {"z": "true"} | +---------------+ 1 row in set (0.00 sec) ## 正确做法。若没有达到预期的效果，也可用 cast 函数进行转换 select json_replace('{"z": 100}', '$.z', TRUE) ascol-replace; +-------------+ | col-replace | +-------------+ | {"z": true} | +-------------+ 1 row in set (0.00 sec) -
json_set( ) 函数 - 在 JSON 文档中插入新数据或更新已有数据，相当于 json_replace( ) 函数和 json_insert( ) 函数的结合。其用法为
json_set(json_doc, path, val[, path, val] ...)，其中 json_doc、path、val 都是必需的参数。# 这里的值被识别为了字符串，不是预期的 [1,100,1000] select json_set('{"y": 100}', '$.x', '[1,100,1000]') ascol-set; +---------------------------------+ | col-set | +---------------------------------+ | {"x": "[1,100,1000]", "y": 100} | +---------------------------------+ 1 row in set (0.00 sec) ## 用 cast 函数进行转换 select json_set( '{"y": 100}', '$.x', cast('[1,100,1000]' as json) ) ascol-set; +---------------------------------+ | col-set | +---------------------------------+ | {"x": [1, 100, 1000], "y": 100} | +---------------------------------+ 1 row in set (0.02 sec)
提示
针对 JSON 文档，在进行插入或替换时，若出现不符合预期的输出，请使用 cast( ) 函数进行转换。
版权声明:「自由转载-保持署名-非商业性使用-禁止演绎 3.0 国际」（CC BY-NC-ND 3.0）
用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。
暂无评论
