站点图标 AI技术聚合

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

扎眼的阳光

前言

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

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

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

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';

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

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

测试

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

测试

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
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 只有两个参数时的缩写。

如下两个查询是等价的：

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 条件等

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

测试

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 相同，则为重叠

测试

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

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

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

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 完全一样

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, 没有相同元素

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

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 将会给出错误。

测试

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 类型

测试

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

测试

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 值，也想替换成默认值

{
    "ulink":null,
    "budget_mode":"BUDGET_MODE_DAY",
    "hide_if_exists":0
}

之前我们的SQL 是这样的

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 简化：

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查询处理JSON数据内容请搜索aitechtogether.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持aitechtogether.com！

退出移动版