系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL实现查询处理JSON数据的示例详解

时间:2023-10-29来源:系统城装机大师作者:佚名

前言

最近在做项目时,表 A 有多个字段,其中一个字段 info 把当前项目用不到的冗余的数据按照 JSON 格式都存了进来。随着项目的推进,有些冗余字段需要单独成一列。新增一列之后,需要把 info中对应的数据刷入新增列,这就需要从 MySQL 中读取 JSON 数据。

当时想到的方法,就是写个程序,批量查询数据,把 info 字段查询出来后反序列化,取出其中的key-value,然后再存入数据库。后面查询资料,发现 MySQL 已经提供了从 JSON 数据中查找和比较的函数,极大地方便了数据处理!

这是在没有写这篇文章前,查询资料写出来的刷数据SQL,其实还有优化的空间,等文章最后我们一起看下吧!

1
2
3
4
5
6
select id,
       replace(replace(json_extract(`info`, '$.budget_mode'), '"'''), 'null'''),
       replace(replace(json_extract(`info`, '$.budget'), '"'''), 'null', 0),
       replace(replace(json_extract(`info`, '$.bid'), '"'''), 'null', 0),
from table
where code = 'xxx';

其次,为了方便后面的学习和测试,我们新建一张表,建表语句如下:

1
2
3
4
5
create table `userinfo`
(
    `id`   bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    `info` longtext        NOT NULL COMMENT '用户信息'
);

接下来我们就看下MySQL提供的 JSON 查询和比较函数,比较常用的应该就是 JSON_EXTRACT 、column->path、column->>path 和 JSON_VALUE 四个函数,可以按需学习哦!

JSON_CONTAINS

该函数用于判断一个 JSON 文档是否包含另一个 JSON 文档。如果提供了路径,用于判断 JSON 文档相应路径下的数据是否包含另一个JSON 文档。

语法

JSON_CONTAINS(target,candidate[,path])

  • target: 必填。目标 JSON 文档
  • candidate: 必填。被包含的 JSON 文档
  • path: 可选。路径

返回值

  • 如果 target 或者 target 在 path 路径下的数据包含 candidate,返回 1;否则返回 0
  • 如果任意一个必填参数为 NULL,或者路径 path 在 target 中不存在,返回 NULL
  • 如果 target 或者 candidate 不是一个有效的JSON 文档,查询报错
  • 如果提供的 path 不是一个有效的路径表达式,或者 path 包含通配符 '*' 或者 '**' ,查询报错

规则

  • 对于两个简单类型的变量,如果两者类型相同、该类型可比较且值相等,则 target 包含 candidate
  • 对于两个数组类型的变量,如果 candidate 数组中的每个元素,都存在于 target 中的某些元素中,则 target 包含 candidate
  • 对于一个非数组类型 candidate 和数组类型 target,如果 candidate 存在于 target 的某些元素中,则 target 包含 candidate
  • 对于两个对象,如果 candidate 的每个 key 都在 target 中存在,且对应的 value 值也被包含,则 target 包含 candidate

测试

1
2
3
4
5
6
7
8
9
10
11
insert into userinfo (id, info) values (1,'{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}');
select  JSON_CONTAINS(info,'1'from userinfo where id=1; # 0, target 不包含 JSON '1'
select  JSON_CONTAINS(info,'1','$.a'from userinfo where id=1; # 1, 两个简单类型, 1 包含 1
select  JSON_CONTAINS(info,'1','$.d'from userinfo where id=1; # 1, 非数组和数组类型比较, [1,2,3] 包含 1
select  JSON_CONTAINS(info,'[1,2]','$.d'from userinfo where id=1; # 1, 两个数组类型比较, [1,2,3] 包含 数组类型 [1,2]
select  JSON_CONTAINS(info,'[1,2,4]','$.d'from userinfo where id=1; # 0, 两个数组类型比较, [1,2,3] 不包含 数组类型 [1,2,4]
select  JSON_CONTAINS(info,'{"a":1}'from userinfo where id=1; # 1, 两个对象比较, target 中存在 key 'a',且 value 包含
select  JSON_CONTAINS(info,'{"a":2}'from userinfo where id=1; # 0, 两个对象比较, target 存在 key 'a',但 value 不包含
select  JSON_CONTAINS(info,'{"d":2}'from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'd',且 value 包含
select  JSON_CONTAINS(info,'{"a":1,"d":2}'from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'a' 和 'd' ,且 value 均包含
select  JSON_CONTAINS(info,'{"a":1,"d":[2,3]}'from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'a' 和 'd' ,且 value 均包含

JSON_CONTAINS_PATH

该函数用于判断一个 JSON 文档是否包含一个或者多个路径 path

语法

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path...])]

  • json_doc: 必填。一个 JSON 文档
  • one_or_all: 必填。值为 'one' 或者 'all',指定至少一个还是所有 path 存在于 json_doc
  • path: 必填。至少填写一个路径

返回值

  • one_or_all = 'one' 时,如果存在一个 path 存在于 json_doc,返回 1 ; 否则返回 0
  • one_or_all = 'all' 时,所有 path 存在于 json_doc 返回 1 ; 否则返回 0
  • 如果有参数为 NULL,则返回 NULL
  • 如果 json_doc 不是有效的JSON数据,或者 path 不是合法的表达式,或者 one_or_all 参数 取值不是 'one' 或者 'all',返回 error

测试

1
2
3
4
5
insert into userinfo (id, info) values (2,'{"a": 1, "b": 2, "c": {"d": 4}}');
select JSON_CONTAINS_PATH(info,'one','$.a'from userinfo where id=2; # 1, a 存在于 路径中
select JSON_CONTAINS_PATH(info,'one','$.a','$.e'from userinfo where id=2 ; # 1, 至少一个存在即可,且路径 a 存在
select JSON_CONTAINS_PATH(info,'all','$.a','$.e'from userinfo where id=2; # 0, 必须所有路径都存在,但路径 e 不存在中
select JSON_CONTAINS_PATH(info,'all','$.c.d'from userinfo where id=2; # 1, 路径 c.d 存在

JSON_EXTRACT

该函数用于从 JSON 字段中查询路径 path 对应的 value 值

语法

JSON_EXTRACT(json_doc, path[,path...])

  • json_doc: 必填。一个 JSON 文档
  • path: 必填。至少填写一个路径

返回值

  • 如果只匹配到一个path,则返回对应的 value
  • 如果匹配到多个 path,则将所有的 value 组合成一个数组返回,value 在数组的顺序和 提供的 path 顺序保持一致
  • 如果参数为 NULL,或者未在 json_doc 中匹配到对应的 path,则返回NULL
  • 如果 json_doc 不是合法的 JSON 文档,或者 path 不是合法的路径表达式,则返回error
1
2
3
4
5
6
7
8
insert into userinfo (id, info) values (3,'{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}');
select json_extract(info,'$.a'from userinfo where id=3; # 1
select json_extract(info,'$.c.d'from userinfo where id=3; # 4
select json_extract(info,'$.d'from userinfo where id=3; # [1,2,3]
select json_extract(info,'$.d[0]'from userinfo where id=3; # 1
select json_extract(info,'$.d[3]'from userinfo where id=3; # NULL
select json_extract(info,'$.f'from userinfo where id=3; # NULL
select json_extract(info,'$.a','$.b','$.c','$.d','$.e.name','$.e.age','$.f'from userinfo where id=3; # [1, 2, {"d": 4}, [1, 2, 3], "tom", 12]

如果只查询一个 path,可以使用接下来介绍的 -> 操作符

column->path

JSON_EXTRACT 只有两个参数时的缩写。

如下两个查询是等价的:

1
2
3
4
5
6
7
8
9
select info,info->'$.a' as info_a from userinfo where info->'$.a' >0 ;
select info,JSON_EXTRACT(info,'$.a'as info_a  from userinfo where JSON_EXTRACT(info,'$.a')>0;
+-----------------------------------------------------------------------+------+
|info                                                                   |info_a|
+-----------------------------------------------------------------------+------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1     |
|{"a": 1, "b": 2, "c": {"d": 4}}                                        |1     |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1     |
+-----------------------------------------------------------------------+------+

和列操作一样,这个符号可以用于 where条件、order by 条件等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select info,info->'$.a' as a, info->'$.c.d' as info_c_d from userinfo where info->'$.d' is not null ;
+-----------------------------------------------------------------------+-+--------+
|info                                                                   |a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1|4       |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4       |
+-----------------------------------------------------------------------+-+--------+
select info,info->'$.a' as a,info->'$.c.d' as info_c_d from userinfo where info->'$.d[0]'>0 order by '$.a';
+-----------------------------------------------------------------------+-+--------+
|info                                                                   |a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1|4       |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4       |
+-----------------------------------------------------------------------+-+--------+

column->>path

'->>' 符号相对于 '->',增加了去除引号的功能。如果一个 JSON 文档中,key 对应的 value 是字符串类型,那么如下三个表达式返回相同的结果:

  • JSON_UNQUOTE(JSON_EXTRACT(column,path))
  • JSON_UNQUOTE(column->path)
  • column->>path
1
2
3
4
select info->'$.e.name' as name from userinfo where id=3; # "tom"
select json_unquote(json_extract(info,'$.e.name')) as name from userinfo where id =3; # tom
select json_unquote(info->'$.e.name'as name from userinfo where id=3; # tom
select info->>'$.e.name' as name from userinfo where id=3; # tom

JSON_KEYS

该函数用于返回 JSON 文档或者指定 path 下最顶层的所有 key

语法

JSON_KEYS(json_doc,[path])

  • json_doc: 必填。一个 JSON 文档
  • path: 选填。路径

返回值

  • 返回 json_doc 或者指定 path 下最顶层的 key 数组
  • 如果任意参数为 NULL,或者 json_doc 不是一个对象(可能是个数组),或者根据 path 没有定位到数据,则返回NULL
  • 如果 json_doc不是 JSON 对象,或者指定的路径不合法,返回error

测试

1
2
3
4
5
6
7
8
9
10
11
12
select info,json_keys(info) from userinfo where id=1;
+-------------------------------------------+--------------------+
|info                                       |json_keys(info)     |
+-------------------------------------------+--------------------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}|["a""b""c""d"]|
+-------------------------------------------+--------------------+
select info,json_keys(info->'$.c'from userinfo where id=1;
+-------------------------------------------+----------------------+
|info                                       |json_keys(info->'$.c')|
+-------------------------------------------+----------------------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}|["d"]                 |
+-------------------------------------------+----------------------+

JSON_OVERLAPS

该函数用于判断两个JSON文档是否有重叠

语法

JSON_OVERLAPS(json_doc1, json_doc2)

  • json_doc1: 必填。JSON文档1
  • json_doc2: 必填。JSON文档2

返回值

  • 如果两个JSON文档有重叠,返回 1;否则返回 0
  • 如果参数为NULL,返回NULL

重叠逻辑

  • 如果两个JSON文档均为简单类型,相当于判等操作,相等则为重叠
  • 如果两个JSON文档均为数组,如果至少有一个元素相同,则为重叠
  • 如果两个JSON文档均为对象,如果至少有 key-value 相同,则为重叠

测试

两个基础元素,就是简单的判等操作

1
2
SELECT JSON_OVERLAPS('5''5'); # 1, 相等
SELECT JSON_OVERLAPS('"5"''5'); # 0, 类型不同,不相等

对于数组,需要有元素相同;如果是多维数组,子数组元素需要完全一样

1
2
3
4
5
SELECT JSON_OVERLAPS("[1,3,5,7]""[2,5,7]");  # 1, 存在相同的元素 5 和 7
SELECT JSON_OVERLAPS("[1,3,5,7]""[2,6,7]"); # 1, 存在相同的元素 7
SELECT JSON_OVERLAPS("[1,3,5,7]""[2,6,8]"); # 0, 没有相同元素
SELECT JSON_OVERLAPS('[[1,2],[3,4],5]''[1,[2,3],[4,5]]'); # 0, 没有相同元素
SELECT JSON_OVERLAPS('[[1,2],[3,4],5]''[[1,2],[2,3],[4,5]]'); # 1, 有相同元素 [1,2]

如果是对象,需要 key-value 完全一样

1
2
3
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}''{"c":1,"e":10,"f":1,"d":10}'); # 1, 相同key-value "d":10
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}''{"a":5,"e":10,"f":1,"d":20}'); # 0, 没有相同元素
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":[20,30]}''{"a":5,"e":10,"f":1,"d":[20]}'); # 0, 没有相同元素

如果一个基础类型和数组类型比较,基础类型会被转成数组类型

1
2
SELECT JSON_OVERLAPS('[4,5,6,7]''6');  # 1, [4,5,6,7]和 [6] 有相同元素 6
SELECT JSON_OVERLAPS('[4,5,6,7]''"6"'); # 0, 类型不同,没有相同元素

JSON_SEARCH

对于给定的字符串,返回该字符串在 JSON 文档中的路径

语法

JSON_SEARCH(json_doc, one_or_all, search_str, escape_char, path...)

json_doc: 必填。JSON文档

one_or_all: 必填。取值只能为 one 或者 all

  • one: 返回第一个匹配的路径
  • all: 以数组的形式返回所有匹配到的路径,去重,无顺序

search_str: 必填。要查询的字符串,可以使用通配符

  • %: 匹配0个或多个字符
  • _: 匹配一个字符

escape_char: 可选。如果 search_str 中包含 % 和 _,需要在他们之前添加转移字符。默认是 \

path: 可选。指定在具体路径下搜索

返回值

JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。它返回一个路径字符串或者由多个路径组成的数组。

JSON_SEARCH() 函数将在以下情况下返回 NULL:

  • 未搜索到指定的字符串
  • JSON 文档中不存在指定的 path
  • 任意一个参数为 NULL

JSON_SEARCH() 函数将在以下情况下返回错误:

  • 如果参数 json 不是有效的 JSON 文档,MySQL 将会给出错误。
  • 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
SET @json_doc = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
select JSON_SEARCH(@json_doc, 'one''abc'); # "$[0]"
select JSON_SEARCH(@json_doc, 'all''abc'); # ["$[0]""$[2].x"]
select JSON_SEARCH(@json_doc, 'all''ghi'); # null
select JSON_SEARCH(@json_doc, 'all''10'); # "$[1][0].k"  
-- 指定路径
select JSON_SEARCH(@json_doc, 'all''10'NULL'$[*][0].k'); # "$[1][0].k" 
select JSON_SEARCH(@json_doc, 'all''10'NULL'$[1][0]'); # "$[1][0].k" 
select JSON_SEARCH(@json_doc, 'all''abc'NULL'$[2]'); # "$[2].x" 
-- 通配符
select JSON_SEARCH(@json_doc, 'all''%a%'); # ["$[0]""$[2].x"]  
select JSON_SEARCH(@json_doc, 'all''%b%'); # ["$[0]""$[2].x""$[3].y"
select JSON_SEARCH(@json_doc, 'all''%b%'NULL'$[2]'); # "$[2].x"   

JSON_VALUE

该函数的作用是:查询 JSON 文档 path 下的值

语法

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:NULLERROR | DEFAULT valueON EMPTY

on_error:NULLERROR | DEFAULT valueON ERROR

参数

json_doc: 必填。JSON文档

path: 必填。指定的路径

RETURNING type: 可选。将结果转为指定的类型,可以为如下类型:

  • FLOAT
  • DOUBLE
  • DECIMAL
  • SIGNED
  • UNSIGNED
  • DATE
  • TIME
  • DATETIME
  • YEAR (MySQL 8.0.22 and later)
  • CHAR
  • JSON

NULLERROR | DEFAULT valueON EMPTY

可选。如果指定了,它决定了指定路径下没有数据的返回值:

NULL ON EMPTY: 如果指定路径下没有数据,JSON_VALUE() 函数将返回 NULL,这是默认的行为。

DEFAULT value ON EMPTY: 如果指定路径下没有数据,JSON_VALUE`() 函数将返回 value。

ERROR ON EMPTY: 如果指定路径下没有数据,JSON_VALUE() 函数将抛出一个错误。

NULLERROR | DEFAULT valueON ERROR

可选的。如果指定了,它决定了处理错误的逻辑:

  • NULL ON ERROR: 如果有错误,JSON_VALUE() 函数将返回 NULL,这是默认的行为。
  • DEFAULT value ON ERROR: 如果有错误,JSON_VALUE() 函数将返回 value。
  • ERROR ON ERROR: 如果有错误,JSON_VALUE() 函数将抛出一个错误。

返回值

默认以字符串的格式,返回 JSON 文档在指定的路径上的值;如果使用 RETURNING type 子句,会把结果转为 type 类型

测试

1
2
3
SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}''$.fname'); # Joe
SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}''$.price' RETURNING DECIMAL(4,2)); # 49.95
SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}''$.total' DEFAULT 100.00 ON EMPTY); # 100.00

MEMBEROF

该函数用于判断value,是否是数组 json_array 的元素

语法

value MEMBER_OF (json_array)

value: 必填。任意值,可以是一个简单类型或者 JSON

json_array: 必填。一个JSON数组

返回值

如果 value 是 json_array 中的元素,返回1;否则返回0

测试

1
2
3
4
5
SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]'); # 1
SELECT '17' MEMBER OF('[23, "abc", 17, "ab", 10]'); # 0, 类型不一致
SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]'); # 1
SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]'); # 1
SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]'); # 1

现在我们可以回过头来看下文章开头要优化的SQL:

这是表 info 字段存储的数据,如果字段有数据,存储对应的数据类型;如果没有数据,存储 null。但是 string 类型的 value 有引号,我们想去掉引号;其次对于 null 值,也想替换成默认值

1
2
3
4
5
{
    "ulink":null,
    "budget_mode":"BUDGET_MODE_DAY",
    "hide_if_exists":0
}

之前我们的SQL 是这样的

1
2
3
4
5
6
select id,
       replace(replace(json_extract(`info`, '$.budget_mode'), '"'''), 'null'''),
       replace(replace(json_extract(`info`, '$.budget'), '"'''), 'null', 0),
       replace(replace(json_extract(`info`, '$.bid'), '"'''), 'null', 0),
from table
where code = 'xxx';

json_extract 是为了拿到对应的 value,里面的 replace()是为了去掉引号,外面的 replace 是为了将 null 替换为默认值。对于去掉引号,我们可以使用 column ->> path 简化:

1
2
3
4
5
6
select id,
       replace(info ->> '$.budget_mode''null'''),
       replace(info ->> '$.budget''null', 0),
       replace(info ->> '$.bid''null', 0)
from ad_ad
where id = 6993;

总结

本篇文章一共介绍了如下几个函数:

  • JSON_CONTAINS:判断一个 JSON 文档是否包含另一个 JSON 文档
  • JSON_CONTAINS_PATH:判断一个JSON文档,是否包含一个或者多个路径 path
  • JSON_EXTRACT:从 JSON 文档中查询路径对应的 value 值
  • column->path:JSON_EXTRACT 只有两个参数时的缩写
  • column->>path:相对于 '->',增加了去除 引号 的功能
  • JSON_KEYS:返回 JSON 文档或者指定 path 下最顶层的所有 key
  • JSON_OVERLAPS:判断两个 JSON 文档是否有重叠
  • JSON_SEARCH:返回给定字符串在 JSON 文档中的路径
  • JSON_VALUE:查询 JSON 文档 path 下的值
  • MEMBEROF:判断一个值是否为一个 JSON 数组中的元素

到此这篇关于MySQL实现查询处理JSON数据的示例详解的文章就介绍到这了,

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载