MySQL查询处理 JSON 数据

前言

最近在做项目时，表 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
12
13
14
15
16
17
18
19
20
21


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
6


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
 9
10


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
10
11


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
15
16
17
18


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
13
14


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
3


SELECT JSON_OVERLAPS('5', '5'); # 1, 相等

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

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

1
2
3
4
5
6
7
8
9


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
4
5


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
3


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
14
15
16


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: {NULL | ERROR | DEFAULT value} ON EMPTY

on_error: {NULL | ERROR | DEFAULT value} ON ERROR

参数

json_doc: 必填。JSON文档
path: 必填。指定的路径
RETURNING type: 可选。将结果转为指定的类型，可以为如下类型：
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR (MySQL 8.0.22 and later)
- CHAR
- JSON
{NULL | ERROR | DEFAULT value} ON EMPTY

可选。如果指定了，它决定了指定路径下没有数据的返回值：
- NULL ON EMPTY: 如果指定路径下没有数据，JSON_VALUE() 函数将返回 NULL，这是默认的行为。
- DEFAULT value ON EMPTY: 如果指定路径下没有数据，JSON_VALUE`() 函数将返回 value。
- ERROR ON EMPTY: 如果指定路径下没有数据，JSON_VALUE() 函数将抛出一个错误。
{NULL | ERROR | DEFAULT value} ON 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
4
5


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
6
7


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
7
8



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 数组中的元素

微信公众号：CodePlayer

MySQL查询处理 JSON 数据

文章目录

前言

JSON_CONTAINS

JSON_CONTAINS_PATH

JSON_EXTRACT

column->path

column-»path

JSON_KEYS

JSON_OVERLAPS

JSON_SEARCH

JSON_VALUE

MEMBEROF

总结

更多