MySQL操作JSON

最新推荐文章于 2024-02-18 14:36:53 发布
最新推荐文章于 2024-02-18 14:36:53 发布
阅读量743 收藏 1
点赞数
分类专栏： 所有文章 文章标签： mysql json sql 大数据 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45203607/article/details/120248113
版权

MySQL操作JSON

总所周知，mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0…解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍。

如何创建json格式字段

这里我们先创建一个简单的含json格式的数据库表，其中json_value就为json格式的字段。

当然也可以是text类型但是这样就没有有json格式的校验了,插入的数据是不是json都不会限制

创建json格式的dept表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些测试数据

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

在创建一个json格式的dept_leade表

CREATE TABLE `dept_leader` (
  `id` int(11) NOT NULL,
  `leaderName` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些测试数据

insert into dept_leader VALUES(1,'leader1','{"name": "王一", "id": "1", "leaderId": "1"}');
insert into dept_leader VALUES(2,'leader2','{"name": "王二", "id": "2", "leaderId": "3"}');
insert into dept_leader VALUES(3,'leader3','{"name": "王三", "id": "3", "leaderId": "4"}');
insert into dept_leader VALUES(4,'leader4','{"name": "王四", "id": "4", "leaderId": "5"}');
insert into dept_leader VALUES(5,'leader5','{"name": "王五", "id": "5", "leaderId": "5"}');

以下操作就是根据上面2个表进行演示

JSON函数

在操作json数据表前我们需要知道MySQL给我们提供了什么操作JSON的函数 不然演示你也看不懂

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html (JSON官方地址)

以下演示常用的json函数 其他的自己到官网上找

JSON_OBJECT

  1. JSON_OBJECT([key, val[, key, val] …]) (常用)

    返回包含这些值的JSON对象。如果任何键名是NULL或参数数目为奇数，则会发生错误。

    mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

JSON_ARRAY

  1. JSON_ARRAY([val[, val] …]) 返回包含这些值的JSON数组

    mysql> SELECT JSON_ARRAY(1, JSON_OBJECT('id', 87, 'name', 'carrot'), NULL, TRUE, CURTIME());
+-------------------------------------------------------------------------------+
| JSON_ARRAY(1, JSON_OBJECT('id', 87, 'name', 'carrot'), NULL, TRUE, CURTIME()) |
+-------------------------------------------------------------------------------+
| [1, {"id": 87, "name": "carrot"}, null, true, "18:12:36.000000"]              |
+-------------------------------------------------------------------------------+
1 row in set (0.06 sec)

JSON_QUOTE

  1. JSON_QUOTE(json_val)

    将字符串作为json值引用，方法是用双引号字符包装字符串，并转义内部引号和其他字符，然后将结果以utf8bm4字符串返回 NULL如果参数为，则 返回 NULL。

    此函数通常用于生成有效的JSON字符串文字，以包含在JSON文档中。

    mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"你好"'),JSON_QUOTE(null);
+--------------------+----------------------+------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"你好"') | JSON_QUOTE(null) |
+--------------------+----------------------+------------------+
| "null"             | "\"你好\""           | NULL             |
+--------------------+----------------------+------------------+
1 row in set (0.04 sec)

JSON_CONTAINS

  1. JSON_CONTAINS(target, candidate[, path]) (常用)

    查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。

    列: 查询@j JSON变量里 a=1 的json对象 是否存在

    SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS(@j,JSON_OBJECT('a',1));

    结果为1 存在

    列: 查询@j JSON变量里 b=1 的json对象 是否存在

    SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS(@j,JSON_OBJECT('b',1));

    结果为0 不存在

JSON_CONTAINS_PATH

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

    查询是否存在指定路径(key)，存在则返回1，否则返回0。

    one_or_all选择项:只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。

    SET @j = '{"a": 1, "b": 2, "c": {"d": 4},"e":null}';
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.e');  -- 结果1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');   -- 结果1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a','$.b'); -- 结果1
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a','$.b','$.f');  -- 结果0

JSON_EXTRACT

  1. JSON_EXTRACT(json_doc, path[, path] …) (常用)

    从json文档里抽取数据。如果有参数就返回值，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。

    对象形式

    
SELECT JSON_EXTRACT('{"id":1,"name":"xx1","hobby":["游泳","跑步"]}', '$.id');  -- 1

SELECT JSON_EXTRACT('{"id":1,"name":"xx1","hobby":["游泳","跑步"]}', '$.hobby[1]');  -- "跑步"

    数组形式

    SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); -- 20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); -- [20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[*]'); -- [10, 20, [30, 40]]

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][0]','$[2][1]'); -- [30, 40]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); -- [30, 40]

    数组对象形式

    SELECT JSON_EXTRACT('[{"id":1,"name":"xx1"},{"id":2,"name":"xx2"}]', '$[0]'); -- {"id": 1, "name": "xx1"}

SELECT JSON_EXTRACT('[{"id":1,"name":"xx1"},{"id":2,"name":"xx2"}]', '$[1]'); -- {"id": 2, "name": "xx2"}

SELECT JSON_EXTRACT('[{"id":1,"name":"xx1"},{"id":2,"name":"xx2"}]', '$[1].id'); -- 2

    在MySQL 5.7.9+里可以用"->"替代JSON_EXTRACT函数进行简化操作。

    表在开始的时候就已经让大家创建过了,没有创建的小伙伴自己翻到最上案例中进行创建

    mysql> SELECT * FROM  dept
WHERE json_value -> "$.deptId"="1";
+----+-------+-----------------------------------------------------------+
| id | dept  | json_value                                                |
+----+-------+-----------------------------------------------------------+
|  1 | 部门1 | {"deptId": "1", "deptName": "部门1", "deptLeaderId": "3"} |
+----+-------+-----------------------------------------------------------+
1 row in set (0.04 sec)

    我们还可以控制显示指定的字段

    mysql> SELECT id, leaderName, 
json_value->"$.id" as deptId,
json_value->"$.name" as name 
FROM  dept_leader
WHERE json_value -> "$.id"="1";
+----+------------+--------+--------+
| id | leaderName | deptId | name   |
+----+------------+--------+--------+
|  1 | leader1    | "1"    | "王一" |
+----+------------+--------+--------+
1 row in set (0.05 sec)

    在MySQL 5.7.13+，还可以用"->>" 去掉结果集里的"号

    下面三种效果是一样的：

    • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
    • JSON_UNQUOTE(column -> path)
    • column->>path
    mysql> SELECT id, leaderName, 
json_value->>"$.id" as deptId,
json_value->>"$.name" as name 
FROM  dept_leader
WHERE json_value -> "$.id"="1";
+----+------------+--------+------+
| id | leaderName | deptId | name |
+----+------------+--------+------+
|  1 | leader1    | 1      | 王一 |
+----+------------+--------+------+
1 row in set (0.04 sec)

JSON_KEYS

  1. JSON_KEYS(json_doc[, path])

    获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.01 sec)

    把key a 和key b 都返回出来了

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+
1 row in set (0.01 sec)

    把指定 key b 内的所有key返回出来

JSON_SEARCH

  1. JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

    查询包含指定字符串的paths(位置)，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。

    • one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
    • search_str：要查询的字符串。 可以用LIKE里的’%'或‘_'匹配。
    • path：在指定path下查。

    先设置一个变量

    SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

    下面案例就是根据上面变量进行的

    案例1 : 查询 @j 里 abc的位置 只查询一个

     SELECT JSON_SEARCH(@j, 'one', 'abc');

    结果: “$[0]”

    案例2 : 查询 @j 里所有包含 abc的位置

    SELECT JSON_SEARCH(@j, 'all', 'abc');

    结果: [" [ 0 ] " , " [0]", " [0]","[2].x"]

    案例3: 查询 @j 里所有包含 ghi的位置

    SELECT JSON_SEARCH(@j, 'all', 'ghi');

    结果: null

    案例4: 查询@j里所有包含10的位置

    SELECT JSON_SEARCH(@j, 'all', '10');

    结果: “$[1][0].k”

    案例5: 查询指定位置里中10的位置

    SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');

    结果: “$[1][0].k”

    案例6: 模糊查询内容包含a的全部位置

     SELECT JSON_SEARCH(@j, 'all', '%a%');

    结果: [" [ 0 ] " , " [0]", " [0]","[2].x"]

    案例7:指定区域模糊查询内容包含b的全部位置

     SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');

JSON_ARRAY_APPEND

  1. JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

    在指定path的json 数组尾部追加值。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。

    SET @j = '{"name":"hu","age":"123"}';
SELECT JSON_ARRAY_APPEND(@j, '$[0]', "abc");

    结果: [{“age”: “123”, “name”: “hu”}, “abc”]

    可以看到结果拼接成数组了

    实例数组的操作:

    先设置一个变量

    SET @j = '["a", ["b", "c"], "d"]';

    以下实例都是基于这个变量的值演示

    SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);

    结果: [[“a”, 2], [“b”, “c”], “d”]

    SELECT JSON_ARRAY_APPEND(@j, '$[1]', 3);

    结果: [“a”, [“b”, “c”, 3], “d”]

    实例对象的操作:

    先设置一个变量

    SET @j = '{"a": 1, "b": [2, 3], "c": 4}';

    SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');

    结果: {“a”: 1, “b”: [2, 3, “x”], “c”: 4}

    SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');

    结果: {“a”: 1, “b”: [2, 3], “c”: [4, “y”]}

    SET @j = '{"a": 1}';
SELECT JSON_ARRAY_APPEND(@j, '$', 'z');

    结果: [{“a”: 1}, “z”]

JSON_ARRAY_INSERT

  1. JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

    在path指定的json 数组元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。

    设置一个变量

    SET @j = '["a", {"b": [1, 2]}, [3, 4]]';

    以下案例都是基于此变量操作

    SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');

    结果: [“a”, “x”, {“b”: [1, 2]}, [3, 4]]

    SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');

    结果: [“a”, {“b”: [1, 2]}, [3, 4], “x”]

    SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');

    结果: [“a”, {“b”: [“x”, 1, 2]}, [3, 4]]

    SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');

    结果: [“a”, {“b”: [1, 2]}, [3, “y”, 4]]

    SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');

    结果: [“x”, “a”, {“b”: [1, 2]}, [3, 4]]

JSON_INSERT

  1. JSON_INSERT(json_doc, path, val[, path, val] …) (常用)

    在指定path下插入数据 追加在结尾，如果path已存在，则忽略此val（不存在才插入）。

    SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@j, '$.c', 10);  
SELECT JSON_INSERT(@j, '$.c', 10, '$.d', '[true, false]');

    结果1: {“a”: 1, “b”: [2, 3], “c”: 10}

    结果2: {“a”: 1, “b”: [2, 3], “c”: 10, “d”: “[true, false]”}

JSON_REPLACE

  1. JSON_REPLACE(json_doc, path, val[, path, val] …)

    替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。

    SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_REPLACE(@j, '$.a', 10);
SELECT JSON_REPLACE(@j, '$.a', 10, '$.b', '[true, false]');

    结果1: {“a”: 10, “b”: [2, 3]}

    结果2: {“a”: 10, “b”: “[true, false]”}

JSON_SET

  1. JSON_SET(json_doc, path, val[, path, val] …) (常用)

    设置指定路径的数据（不管是否存在）。存在替换 不存在追加

    SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_SET(@j, '$.a', 10);
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');

    结果1: {“a”: 10, “b”: [2, 3]}

    结果2: {“a”: 10, “b”: [2, 3], “c”: “[true, false]”}

JSON_MERG

  1. JSON_MERGE(json_doc, json_doc[, json_doc] …)

    将json进行拼接

    merge多个json文档。规则如下：

    • 如果都是json array，则结果自动merge为一个json array；

      SELECT JSON_MERGE('[1, 2]', '[true, false]');

      结果: [1, 2, true, false]

    • 如果都是json object，则结果自动merge为一个json object；

      SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');

      结果: {“id”: 47, “name”: “x”}

    • 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。

       SELECT JSON_MERGE('1', 'true');
 SELECT JSON_MERGE('[1, 2]', '{"id": 47}');

      结果1: [1, true]

      结果2: [1, 2, {“id”: 47}]

JSON_REMOVE

  1. JSON_REMOVE(json_doc, path[, path] …) (常用)

    移除指定路径的数据，如果某个路径不存在则略过此路径。

    SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j, '$[1]');

    结果: [“a”, “d”]

    SET @j = '[{"ac":"xx","bc":"xxx"},"a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j, '$[0].ac');

    结果: [{“bc”: “xxx”}, “a”, [“b”, “c”], “d”]

    SET @j = '{"ac":"xx","bc":"xxx"}';
SELECT JSON_REMOVE(@j, '$.ac');

    结果: {“bc”: “xxx”}

JSON_UNQUOTE

  1. JSON_UNQUOTE(val)

    去掉结果集内值的引号。

    SET @j = '"abc"';
SELECT @j, JSON_UNQUOTE(@j);

    结果 原数据: “abc” 修改后数据: abc

JSON_DEPTH

  1. JSON_DEPTH(json_doc)

    获取json文档的深度。 空的json array、json object或标量的深度为1。

    SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');

    结果: 1 1 1

    SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');

    结果: 2 2

    SELECT JSON_DEPTH('[10, {"a": 20}]');

    结果: 3

JSON_LENGTH

  1. JSON_LENGTH(json_doc[, path])

    获取指定路径下的长度。

    长度的计算规则：

    • 标量的长度为1；
    • json array的长度为元素的个数；
    • json object的长度为key的个数。
    SELECT JSON_LENGTH('[1, 2, {"a": 3}]');

    结果: 3

    SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');

    结果: 2

    从指定的key开始查询

    SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');

    结果: 1

JSON_TYPE

  1. JSON_TYPE(json_val)

    获取json文档的具体类型

    SELECT JSON_TYPE('{"a": 1, "b": {"c": 30}}');

    结果: OBJECT (对象类型)

    SELECT JSON_TYPE('[{},{}]');

    结果: ARRAY (数组类型)

JSON_VALID

  1. JSON_VALID(val)

    判断val是否为有效的json格式，是为1，不是为0。

    SELECT JSON_VALID('{"a": 1}');

    结果: 1

    SELECT JSON_VALID('hello'), JSON_VALID('"hello"');

    结果: 0 1

利用JSON函数操作数据库表数据

查询操作

1、使用 json字段名->’$.json属性’ 进行查询条件

json字段名->’$.json属性’ 效果和 函数JSON_CONTAINS() 一样

举个例子：如果我想查询deptLeader=张五的数据，那么sql语句如下：

mysql> SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
+----+-------+-----------------------------------------------------------+
| id | dept  | json_value                                                |
+----+-------+-----------------------------------------------------------+
|  3 | 部门3 | {"deptId": "3", "deptName": "部门3", "deptLeaderId": "5"} |
|  4 | 部门4 | {"deptId": "4", "deptName": "部门4", "deptLeaderId": "5"} |
|  5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"} |
+----+-------+-----------------------------------------------------------+
3 rows in set (0.04 sec)

如果涉及多个条件也是支持的

比如我想查dept为“部门3” 和 deptLeaderId=5的数据，sql如下：

mysql> SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and dept='部门3';
+----+-------+-----------------------------------------------------------+
| id | dept  | json_value                                                |
+----+-------+-----------------------------------------------------------+
|  3 | 部门3 | {"deptId": "3", "deptName": "部门3", "deptLeaderId": "5"} |
+----+-------+-----------------------------------------------------------+
1 row in set (0.04 sec)

如果涉及json中多个字段关系查询

比如我想查询json格式中deptLeader=张五和deptId=5的数据

mysql> SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
+----+-------+-----------------------------------------------------------+
| id | dept  | json_value                                                |
+----+-------+-----------------------------------------------------------+
|  5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"} |
+----+-------+-----------------------------------------------------------+
1 row in set (0.05 sec)

如果涉及到关联表查询

这里我们要连表查询在dept 表中部门leader在dept_leader 中的详情

mysql> 
SELECT * from dept,dept_leader 
WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
+----+-------+-----------------------------------------------------------+----+------------+----------------------------------------------+
| id | dept  | json_value                                                | id | leaderName | json_value                                   |
+----+-------+-----------------------------------------------------------+----+------------+----------------------------------------------+
|  1 | 部门1 | {"deptId": "1", "deptName": "部门1", "deptLeaderId": "3"} |  3 | leader3    | {"id": "3", "name": "王三", "leaderId": "4"} |
|  2 | 部门2 | {"deptId": "2", "deptName": "部门2", "deptLeaderId": "4"} |  4 | leader4    | {"id": "4", "name": "王四", "leaderId": "5"} |
|  3 | 部门3 | {"deptId": "3", "deptName": "部门3", "deptLeaderId": "5"} |  5 | leader5    | {"id": "5", "name": "王五", "leaderId": "5"} |
|  4 | 部门4 | {"deptId": "4", "deptName": "部门4", "deptLeaderId": "5"} |  5 | leader5    | {"id": "5", "name": "王五", "leaderId": "5"} |
|  5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"} |  5 | leader5    | {"id": "5", "name": "王五", "leaderId": "5"} |
+----+-------+-----------------------------------------------------------+----+------------+----------------------------------------------+
5 rows in set (0.06 sec)

2、函数JSON_CONTAINS()：JSON格式数据是否在字段中包含特定对象

用法: JSON_CONTAINS(target, candidate[, path])
例:如果我们想查询包含deptName=部门5的对象

mysql> select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"));
+----+-------+-----------------------------------------------------------+
| id | dept  | json_value                                                |
+----+-------+-----------------------------------------------------------+
|  5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"} |
+----+-------+-----------------------------------------------------------+
1 row in set (0.06 sec)

查询json里的json对象

比如我们添加这么一组数据到dept表中：

insert into dept 
VALUES(6,'部门9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');

我们可以看到deptName中还有一个对象，里面还有dept和depp两个属性字段，那么我们应该怎么查询depp=dd的员工呢。

用法：JSON_OBJECT([key, val[, key, val] …]) 返回指定的JSON对象。如果任何键名是NULL或参数数目为奇数，则会发生错误。

例：

mysql> 
SELECT * from (SELECT *,json_value->'$.deptName' as deptName FROM dept) t 
WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));
+----+-------+--------------------------------------------------------------------------------+------------------------------+
| id | dept  | json_value                                                                     | deptName                     |
+----+-------+--------------------------------------------------------------------------------+------------------------------+
|  6 | 部门9 | {"deptId": "5", "deptName": {"depp": "dd", "dept": "de"}, "deptLeaderId": "5"} | {"depp": "dd", "dept": "de"} |
+----+-------+--------------------------------------------------------------------------------+------------------------------+
1 row in set (0.05 sec)

这条语句有点复杂我解析下:

  1. 先使用子查询将数据从表里查询出来形成t(虚拟)表

    mysql>  SELECT * from (SELECT *,json_value->'$.deptName' as deptName FROM dept) as t ;
+----+-------+--------------------------------------------------------------------------------+------------------------------+
| id | dept  | json_value                                                                     | deptName                     |
+----+-------+--------------------------------------------------------------------------------+------------------------------+
|  1 | 部门1 | {"deptId": "1", "deptName": "部门1", "deptLeaderId": "3"}                      | "部门1"                      |
|  2 | 部门2 | {"deptId": "2", "deptName": "部门2", "deptLeaderId": "4"}                      | "部门2"                      |
|  3 | 部门3 | {"deptId": "3", "deptName": "部门3", "deptLeaderId": "5"}                      | "部门3"                      |
|  4 | 部门4 | {"deptId": "4", "deptName": "部门4", "deptLeaderId": "5"}                      | "部门4"                      |
|  5 | 部门5 | {"deptId": "5", "deptName": "部门5", "deptLeaderId": "5"}                      | "部门5"                      |
|  6 | 部门9 | {"deptId": "5", "deptName": {"depp": "dd", "dept": "de"}, "deptLeaderId": "5"} | {"depp": "dd", "dept": "de"} |
+----+-------+--------------------------------------------------------------------------------+------------------------------+
6 rows in set (0.06 sec)

    可以发现多出一个deptName列是筛选后的虚拟列

  2. 在更具t表的deptName列数据,进行使用JSON_CONTAINS函数进行筛选deptName列里的包含指定json对象的那一条数据

显示JSON指定字段

写到这里大家都发现了，我们查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？

函数 json_extract（）：从json中返回想要的字段

用法：json_extract(字段名,$.json字段名)

例：

mysql> select id,json_extract(json_value,'$.deptName') as deptName from dept;
+----+----------+
| id | deptName |
+----+----------+
|  1 | "部门1"  |
|  2 | "部门2"  |
|  3 | "部门3"  |
|  4 | "部门4"  |
|  5 | "部门5"  |
+----+----------+
5 rows in set (0.06 sec)

或者使用 字段->’$.key’

select id,json_value->'$.deptName' deptName from dept;

结果可以发现是有双引号的那么如何去掉呢?

因为在很多时候我们只查询一个key的值 那么传递到后端时候是不需要双引号的 简单方式使用 ->>

这个是MySQL 5.7.9开始支持

select json_value ->>'$.id' from dept_leader
where JSON_CONTAINS(json_value,JSON_OBJECT('id','1'))

或者使用函数

select JSON_UNQUOTE(json_value->'$.id') from dept_leader
where JSON_CONTAINS(json_value,JSON_OBJECT('id','1'))

更新JSON字段值

将json_value字段中name=王一 的改为胡

UPDATE dept_leader 
	SET json_value = JSON_SET( json_value, '$.name', '胡' ) 
WHERE
	json_value -> '$.name' = '王一'

删除JSON字段中某key

UPDATE dept_leader 
	SET json_value = JSON_REMOVE(json_value,'$.name')
WHERE
	json_value -> '$.name' = '胡'

追加JSON字段

UPDATE dept_leader 
	SET json_value =JSON_INSERT(json_value,'$.name','王一')
WHERE
	json_value -> '$.id' = '1'

替换JSON字段key

简单来说就是删除原来字段然后在追加新字段

UPDATE dept_leader 
SET json_value = JSON_REMOVE( json_value, '$.name' ),
json_value = JSON_INSERT( json_value, '$.name1', '王一' ) 
WHERE
	json_value -> '$.id' = '1'

判断JSON是否有指定数据

select * from dept_leader
where JSON_CONTAINS(json_value,JSON_OBJECT('id','1'))

JSON多表查询

SELECT
	a.id,
	b.json_value -> '$.name' AS 名称,
	a.json_value -> '$.deptName' AS 部门 
FROM
	dept AS a
	JOIN dept_leader AS b ON a.json_value -> '$.deptId' = b.json_value -> '$.id' 
WHERE
	a.id =4

l
UPDATE dept_leader
SET json_value = JSON_SET( json_value, ‘ . n a m e ′ , ′ 胡 ′ ) W H E R E j s o n v a l u e − > ′ .name', '胡' ) WHERE json_value -> ' .name′,′胡′)WHEREjsonv​alue−>′.name’ = ‘王一’




### 删除JSON字段中某key

```sql
UPDATE dept_leader 
	SET json_value = JSON_REMOVE(json_value,'$.name')
WHERE
	json_value -> '$.name' = '胡'

追加JSON字段

UPDATE dept_leader 
	SET json_value =JSON_INSERT(json_value,'$.name','王一')
WHERE
	json_value -> '$.id' = '1'

替换JSON字段key

简单来说就是删除原来字段然后在追加新字段

UPDATE dept_leader 
SET json_value = JSON_REMOVE( json_value, '$.name' ),
json_value = JSON_INSERT( json_value, '$.name1', '王一' ) 
WHERE
	json_value -> '$.id' = '1'

判断JSON是否有指定数据

select * from dept_leader
where JSON_CONTAINS(json_value,JSON_OBJECT('id','1'))

JSON多表查询

SELECT
	a.id,
	b.json_value -> '$.name' AS 名称,
	a.json_value -> '$.deptName' AS 部门 
FROM
	dept AS a
	JOIN dept_leader AS b ON a.json_value -> '$.deptId' = b.json_value -> '$.id' 
WHERE
	a.id =4

通过以上案例我们可以知道JSON函数可以随意搭配使用

点赞 -收藏加 -关注
便于以后复习和收到最新内容
有其他问题在评论区讨论-或者私信我-收到会在第一时间回复
感谢,配合,希望我的努力对你有帮助^_^
胡安民
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL操作之JSON数据类型操作详解
09-09
主要介绍了MySQL操作之JSON数据类型操作详解，内容较为详细，具有收藏价值，需要的朋友可以参考。
MySQL JSON 类型数据操作
weixin_30655569的博客
12-04 601
1、Java 中动态扩展字段，会导致数据库表被锁，在MySQL 5.7.8版本之前，因为MySQL不能直接操作JSON类型数据，可以将一个字段设定成varchar类型，里面存放JSON格式数据，这样在需要扩展字段时，不需要修改表结构； 2、mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的优点。但mysql毕竟是关系型数...
MySQL之json数据操作
最新发布
LiZhen314的博客
02-18 2210
当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。写到这里大家都发现了，我们查询的json都是整条json数据，这样看起来不是很方便，那么如果我们只想看json中的某个字段怎么办？事例：比如我们想针对id=2的数据新增一组：newData:新增的数据,修改deptName为新增的部门1。如果我们再执行以下刚才的那个sql，只是换了value，我们会看到里面的key值不会发生变化。如果我们要更新id=2数据中newData2的值为：更新的数据2。
对MySQL中JSON数据类型的操作和分析
热门推荐
manongpengzai的博客
08-15 2万+
JSON作为以键值对为主的存储形式，符合很多业务场景，尤其对于python中dict字典数据类型，能直接相互转换，更加适应了python的某些使用情况。 此外，JSON作为不限定固定结构的键值对，能方便灵活的使用在数据变动性比较大的场景，如对于网络数据的爬取收集等，当然其中也存在一些问题，下文再分析。这里本文先列举MySQL中对一些JSON数据类型的常用语句和函数（参考MySQL官方文档，建议大家
mysql JSON数据操作
qq_43180307的博客
10-25 685
mysql json操作
MySQL JSON类型字段操作
12-14
1、Java 中动态扩展字段，会导致数据库表被锁，在MySQL 5.7.8版本之前，因为MySQL不能直接操作JSON类型数据，可以将一个字段设定成varchar类型，里面存放JSON格式数据，这样在需要扩展字段时，不需要修改表结构；...
MySQL中json字段的操作方法
01-19
MySQL5.7.8中引入了json字段，这种类型的字段使用的频率比较低，但是在实际操作中，有些业务仍然在用，我们以此为例，介绍下json字段的操作方法： 还是从例子看起： mysql> create table test1(id int,info json); ...
mysql5.6及以下版本如何查询数据库里的json
09-09
MySQL里面保存数据有时候会把一些杂乱且不常用的时候丢进一个json字段里面,那么如何查询数据库里的json呢以及mysql存储json注意那些格式呢？接下来通过本文给大家详细介绍，需要的朋友参考下
mybatis直接操作 mysql 的 json 字段源码 参考 全程java类型
11-07
controller 和 service ,dao等，全程使用 Java 类型 作为参数，自动解析，无需 过多的手动干预。 配套教程 https://blog.csdn.net/w1047667241/article/details/127697481
mysql生成json插件
09-09
mysql生成json插件
MySQL中JSON操作
晴转多云的博客
11-10 236
Mysql5.7版本以后提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。
mysql操作json
qq_35342288的博客
09-29 255
我们先在存储过程中进行json操作 首先我们先来操作json字符串进行对象的取值 下面是存储过程： begin #接收传入的json参数 select param; select json_extract(param,'$.a.a') as `a.a的值`; end 下面是我们传入的参数： （这里传入一个对象，里面有两个属性，属性a是一个对象，属性b是一个...
mysql之JSON数据操作
weixin_42390621的博客
08-28 635
3.需要数组值的上下文中使用的非数组值会自动打包：该值由字符括起来，以将其转换为数组。4.数组和对象通过合并值来合并数组。
Mysql json数据的操作
youcijibi的博客
06-02 534
一、方法 分类 函数 描述 创建json json_array 创建json数组 json_object 创建json对象 json_quote 将json转成json字符串类型 查询json json_contains 判断是否包含某个json值 json_contains_path 判断某个路径下是否包json值 json_extract 提取json值 column->path json_extract的简洁写法，MySQL 5.7.9开始支持 column->>path json
mysql json格式数据操作
qq_41482600的博客
05-05 2076
mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。 1、创建json格式字段 CREATE TABLE `dept` ( `id` int(11) NOT NULL, `dept` varchar(255) DEFAULT .
MySQL ——JSON相关操作
在此立一个IT职业上的flag：https://github.com/Zeb-D/my-review
09-30 1万+
背景：MySQL 5.7.13 + 支持josn 本章先是JSON的查找操作，后半部分为JSON更新查找；接下来开始熟悉5.7.13版本后带来的相关函数操作或者特性，本文章所包含的范围没有涉及到json的索引，有兴趣的小伙伴可以分享下哦。1、JSON_CONTAINS(target, candidate[, path])表示从target的路径path搜索candidate,如果j不存在path直
MySQL中的JSON
哈
12-06 2万+
根据json字段的内容检索查询数据 语法 使用 字段->'$.json属性'进行查询条件 使用json_extract函数查询，json_extract(字段,"$.json属性") 根据json数组查询，用JSON_CONTAINS(字段,JSON_OBJECT('json属性', "内容")) 测试表 对应的SQL SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table
mysql操作json语句
06-09
MySQL提供了一些操作JSON类型数据的语句，以下是一些常用的JSON操作语句： 1. 插入JSON数据到表中 ``` INSERT INTO table_name (json_column) VALUES ('{"key1": "value1", "key2": "value2"}'); ``` 2. 查询JSON...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

胡安民

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值