程序员需了解的SQL之JSON函数查询与修改（七）-白红宇

程序员需了解的SQL之JSON函数查询与修改（七）

阅读量：384 次

发布时间：2019-03-05

本文共 17544 字，大约阅读时间需要 58 分钟。

文章目录

前言

MySQL从5.7.8以后引入了JSON数据类型，对于JSON文档的操作除了简单的读和写之外还有很多处理JSON的函数。

JSON函数可以从增删改查这些功能点来深入学习Mysql提供的JSON函数.JSON支持包括NUMBER、STRING、BOOLEAN、NULL、ARRAY、OBJECT共6种。

1.创建JSON函数

创建JSON数组

mysql> select json_array(1,'json',true,null,now());+-------------------------------------------------------+| json_array(1,'json',true,null,now())                  |+-------------------------------------------------------+| [1, "json", true, null, "2021-02-21 22:19:57.000000"] |+-------------------------------------------------------+1 row in set (0.01 sec)

创建JSON对象

mysql> select json_object('name','张三','age',24);+---------------------------------------+| json_object('name','张三','age',24)   |+---------------------------------------+| {
  "age": 24, "name": "张三"}           |+---------------------------------------+1 row in set (0.00 sec)

2. 查询JSON函数

2.1 JSON_CONAINS(source,target,path)

此函数可以判断target是否包含在source中，其中path参数可选，如果有参数为NULL或者path不存在则返回NULL，存在返回1否则返回0。

下面以简单例子演示此函数使用：

## 创建一个JSON数组对象mysql> select json_array(1,2,'abc',true,null);+---------------------------------+| json_array(1,2,'abc',true,null) |+---------------------------------+| [1, 2, "abc", true, null]       |+---------------------------------+1 row in set (0.00 sec)

然后判断元素abc、true、null、520是否存在上述JSON对象中

判断元素abc是否包含JSON对象中

mysql> select json_contains('[1, 2, "abc", true, null]','"abc"');+----------------------------------------------------+| json_contains('[1, 2, "abc", true, null]','"abc"') |+----------------------------------------------------+|                                                  1 |+----------------------------------------------------+1 row in set (0.00 sec)

判断元素true是否包含JSON对象中

mysql> select json_contains('[1, 2, "abc", true, null]','true');+---------------------------------------------------+| json_contains('[1, 2, "abc", true, null]','true') |+---------------------------------------------------+|                                                 1 |+---------------------------------------------------+1 row in set (0.00 sec)

判断元素null是否包含在JSON对象中

mysql> select json_contains('[1, 2, "abc", true, null]','null');+---------------------------------------------------+| json_contains('[1, 2, "abc", true, null]','null') |+---------------------------------------------------+|                                                 1 |+---------------------------------------------------+1 row in set (0.00 sec)

判断元素520是否包含在此函数中

mysql> select json_contains('[1, 2, "abc", true, null]','520');+--------------------------------------------------+| json_contains('[1, 2, "abc", true, null]','520') |+--------------------------------------------------+|                                                0 |+--------------------------------------------------+1 row in set (0.00 sec)

判断数组[1,2,‘abc’] 是否存在JSON

mysql> select json_contains('[1, 2, "abc", true, null]','[1,2,"abc"]');+----------------------------------------------------------+| json_contains('[1, 2, "abc", true, null]','[1,2,"abc"]') |+----------------------------------------------------------+|                                                        1 |+----------------------------------------------------------+1 row in set (0.00 sec)

上面几个案例中我们使用JSON_CONTAINS 没有使用到path 参数，下面我们将演示带path参数的函数使用。

通常path以$.key,如果key是一个对象obj，则path应该为$.obj.key。

创建JSON对象并存储在Mysql变量中

mysql> select json_object('name','张三','age',23);+---------------------------------------+| json_object('name','张三','age',23)   |+---------------------------------------+| {
  "age": 23, "name": "张三"}           |+---------------------------------------+1 row in set (0.00 sec)## 创建name1变量mysql> set @name1='{"age": 23, "name": "张三"}';Query OK, 0 rows affected (0.00 sec)

判断路径为$.name 是否存在值张三，包含返回1不包含返回0。

mysql> select json_contains(@name1,'"张三"','$.name');+-------------------------------------------+| json_contains(@name1,'"张三"','$.name')   |+-------------------------------------------+|                                         1 |+-------------------------------------------+1 row in set (0.00 sec)

2.2 JSON_CONAINS_PATH(source,one or all,path…)

此函数查询指定的多个path参数是否存在JSON文档中，其中第二个参数只能取ONE或者ALL，分别表示匹配一个就可以以及匹配所有。

## 匹配JSON对象包含指定路径name或者xxmysql> select json_contains_path(@name1,'one','$.name','$.xx');+--------------------------------------------------+| json_contains_path(@name1,'one','$.name','$.xx') |+--------------------------------------------------+|                                                1 |+--------------------------------------------------+1 row in set (0.00 sec)## 匹配JSON对象包含指定路径name和xxmysql> select json_contains_path(@name1,'all','$.name','$.age');+---------------------------------------------------+| json_contains_path(@name1,'all','$.name','$.age') |+---------------------------------------------------+|                                                 1 |+---------------------------------------------------+1 row in set (0.00 sec)

2.3 JSON_EXTRACT(source,path…)

在JSON数组中可以path可以$[index] 表示数组中第index个元素，下面提取第index个元素如下：

## 创建JSON数组mysql> select json_array('1','2',json_array('3','4'));+-----------------------------------------+| json_array('1','2',json_array('3','4')) |+-----------------------------------------+| ["1", "2", ["3", "4"]]                  |+-----------------------------------------+1 row in set (0.00 sec)

抽取第一个和第三个值

mysql> select json_extract('["1", "2", ["3", "4"]]','$[0]','$[2]');+------------------------------------------------------+| json_extract('["1", "2", ["3", "4"]]','$[0]','$[2]') |+------------------------------------------------------+| ["1", ["3", "4"]]                                    |+------------------------------------------------------+1 row in set (0.00 sec)

MySQL5.7.9之后有一个更简单的标号-> 用来简化JSON_EXRACT,其语法如下：

列名 -> path

## 创建包含JSON类型的表如下mysql> create table json_demo(content json);Query OK, 0 rows affected (0.01 sec)## 插入数据mysql> insert into json_demo values(@name1);Query OK, 1 row affected (0.01 sec)## 查询数据mysql> select * from json_demo;+-------------------------------+| content                       |+-------------------------------+| {
  "age": 23, "name": "张三"}   |+-------------------------------+1 row in set (0.00 sec)

请看如下SQL语句：

mysql> select content,json_extract(content,'$.name') from json_demo where json_extract(content,'$.name')='张三';+-------------------------------+--------------------------------+| content                       | json_extract(content,'$.name') |+-------------------------------+--------------------------------+| {
  "age": 23, "name": "张三"}   | "张三"                         |+-------------------------------+--------------------------------+1 row in set (0.00 sec)

等价于如下SQL：

mysql> select content,content -> '$.name' from json_demo where content -> '$.name'='张三';+-------------------------------+---------------------+| content                       | content -> '$.name' |+-------------------------------+---------------------+| {
  "age": 23, "name": "张三"}   | "张三"              |+-------------------------------+---------------------+1 row in set (0.00 sec)

JSON中查询为字符串类型都会带双引号，如果去掉双引号可以使用JSON_UNQUOTE 函数如下所示：

mysql> select content,json_unquote(content -> '$.name') from json_demo where content -> '$.name'='张三';+-------------------------------+-----------------------------------+| content                       | json_unquote(content -> '$.name') |+-------------------------------+-----------------------------------+| {
  "age": 23, "name": "张三"}   | 张三                              |+-------------------------------+-----------------------------------+1 row in set (0.01 sec)

2.4JSON_KEYS(source,path)

此函数返回在指定路径下所有的键，下面简单演示其使用：

mysql> select json_keys(@name1);+-------------------+| json_keys(@name1) |+-------------------+| ["age", "name"]   |+-------------------+1 row in set (0.00 sec)mysql>

创建嵌套JSON对象如下所示：

mysql> select json_object('name','张三','age','23','account',json_object('accountId','123456','accountBank','招商银行'));+------------------------------------------------------------------------------------------------------------------+| json_object('name','张三','age','23','account',json_object('accountId','123456','accountBank','招商银行'))       |+------------------------------------------------------------------------------------------------------------------+| {
  "age": "23", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}                     |+------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查询嵌套函数key

mysql> select json_keys(@name2);+----------------------------+| json_keys(@name2)          |+----------------------------+| ["age", "name", "account"] |+----------------------------+1 row in set (0.00 sec)

查询指定路径下嵌套的key

mysql> select json_keys(@name2,'$.account');+-------------------------------+| json_keys(@name2,'$.account') |+-------------------------------+| ["accountId", "accountBank"]  |+-------------------------------+1 row in set (0.00 sec)

2.5 JSON_SEARCH(source,one or all ,search_str)

此函数表示在source中匹配一个或者所有复合search_str的元素key，其中search_str 可使用类似like中模糊匹配如下：

## 查询JSON至少一个内容是23的keymysql> select json_search(@name2,'one','23');+--------------------------------+| json_search(@name2,'one','23') |+--------------------------------+| "$.age"                        |+--------------------------------+1 row in set (0.00 sec)## 查询JSON至少一个内容是以张开头的keymysql> select json_search(@name2,'one','张%');+----------------------------------+| json_search(@name2,'one','张%')  |+----------------------------------+| "$.name"                         |+----------------------------------+1 row in set (0.00 sec)## 查询内容包含2的所有key对象mysql> select json_search(@name2,'all','%2%');+----------------------------------+| json_search(@name2,'all','%2%')  |+----------------------------------+| ["$.age", "$.account.accountId"] |+----------------------------------+1 row in set (0.00 sec)## 创建新的JSON对象并赋值mysql> select json_array('1','2',true,null,'abc');+-------------------------------------+| json_array('1','2',true,null,'abc') |+-------------------------------------+| ["1", "2", true, null, "abc"]       |+-------------------------------------+1 row in set (0.00 sec)mysql> set @v1='["1", "2", true, null, "abc"]';Query OK, 0 rows affected (0.00 sec)## 查询JSON数组mysql> select json_search(@v1,'one','1');+----------------------------+| json_search(@v1,'one','1') |+----------------------------+| "$[0]"                     |+----------------------------+1 row in set (0.00 sec)

3. 修改JSON函数

3.1 JSON_ARRAY_APPEND(source,path,val)

此函数在指定path的JSON 数组尾部加val。如果指定path是一个JSON对象则将其封装城一个新的JSON Array。

mysql> select json_array_append(@v1,'$[0]','haha');+-----------------------------------------+| json_array_append(@v1,'$[0]','haha')    |+-----------------------------------------+| [["1", "haha"], "2", true, null, "abc"] |+-----------------------------------------+1 row in set (0.00 sec)mysql> select json_array_append('[["1", "haha"], "2", true, null, "abc"]','$[0]','xixi');+----------------------------------------------------------------------------+| json_array_append('[["1", "haha"], "2", true, null, "abc"]','$[0]','xixi') |+----------------------------------------------------------------------------+| [["1", "haha", "xixi"], "2", true, null, "abc"]                            |+----------------------------------------------------------------------------+1 row in set (0.00 sec)## 设置值mysql> set @v2='[["1", "haha", "xixi"], "2", true, null, "abc"]';Query OK, 0 rows affected (0.00 sec)## 在第一个元素数组中第二个位置上追加 aimysql> select json_array_append(@v2,'$[0][1]','ai');+---------------------------------------------------------+| json_array_append(@v2,'$[0][1]','ai')                   |+---------------------------------------------------------+| [["1", ["haha", "ai"], "xixi"], "2", true, null, "abc"] |+---------------------------------------------------------+1 row in set (0.00 sec)

3.2 JSON_ARRAY_INSERT(source,path,val)

此函数可以在指定path下插入元素val，原先位置的元素整体右移。如果插入的目标元素非JSON数组则不会插入val元素，如果插入的元素超出JSON对象的长度则在尾部进行追加。

mysql> select json_array_insert(@name2,'$[0]','1');+----------------------------------------------------------------------------------------------------+| json_array_insert(@name2,'$[0]','1')                                                               |+----------------------------------------------------------------------------------------------------+| {
  "age": "23", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}       |+----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

上面的JSON对象插入没有效果，这是因为JSON对象是一个JSON_OBJECT类型的元素会自动忽略插入的元素‘1’。

接下来我们使用JSON_ARRAY插入对象看一看效果。

mysql> select json_array_insert('["1", "2", ["3", "4"]]','$[0]','x');+--------------------------------------------------------+| json_array_insert('["1", "2", ["3", "4"]]','$[0]','x') |+--------------------------------------------------------+| ["x", "1", "2", ["3", "4"]]                            |+--------------------------------------------------------+1 row in set (0.00 sec)

3.3 JSON_REPLACE(source,path,val)

此函数可以替换指定路径上的数据，如果path路径不存在则忽略。

mysql> select json_replace(@v1,'$[0]','y');+------------------------------+| json_replace(@v1,'$[0]','y') |+------------------------------+| ["y", "1", "2", ["3", "4"]]  |+------------------------------+1 row in set (0.00 sec)mysql> select json_replace(@name2,'$.age','24');+----------------------------------------------------------------------------------------------------+| json_replace(@name2,'$.age','24')                                                                  |+----------------------------------------------------------------------------------------------------+| {
  "age": "24", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}       |+----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3.4 JSON_SET(source,path,val)

此函数可以设置指定路径的数据，与JSON_REPLACE功能类似，唯一区别就是当指定路径不存在，会在尾部进行添加

mysql> select json_replace(@name2,'$.age11','24');+----------------------------------------------------------------------------------------------------+| json_replace(@name2,'$.age11','24')                                                                |+----------------------------------------------------------------------------------------------------+| {
  "age": "23", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}       |+----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

我们发现此函数并不存在，我们在使用JSON_SET函数在看次效果如下：

mysql> select json_set(@name2,'$.age11','24');+-------------------------------------------------------------------------------------------------------------------+| json_set(@name2,'$.age11','24')                                                                                   |+-------------------------------------------------------------------------------------------------------------------+| {
  "age": "23", "name": "张三", "age11": "24", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}       |+-------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3.5 JSON_MERGE_PRESERVE(source1,source2)

此函数是可以合并两个JSON函数，合成规则如下：

如果两者都是JSON数组，则合成一个JSON数组对象。

如果都是JSON对象则合成一个JSON对象。

如果是不同类型，则将非JSON数组对象转换成JSON数组在按照第一个规则进行合并

合并数组

mysql> select json_merge_preserve(@v1,json_array('a','b','c'));+--------------------------------------------------+| json_merge_preserve(@v1,json_array('a','b','c')) |+--------------------------------------------------+| ["x", "1", "2", ["3", "4"], "a", "b", "c"]       |+--------------------------------------------------+1 row in set (0.00 sec)

合并对象

mysql> select json_merge_preserve(@name2,json_object('company','神码'));+-------------------------------------------------------------------------------------------------------------------------+| json_merge_preserve(@name2,json_object('company','神码'))                                                               |+-------------------------------------------------------------------------------------------------------------------------+| {
  "age": "23", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}, "company": "神码"}         |+-------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

合并多类型

mysql> select json_merge_preserve(@v1,@name2);+---------------------------------------------------------------------------------------------------------------------------------+| json_merge_preserve(@v1,@name2)                                                                                                 |+---------------------------------------------------------------------------------------------------------------------------------+| ["x", "1", "2", ["3", "4"], {
  "age": "23", "name": "张三", "account": {
  "accountId": "123456", "accountBank": "招商银行"}}]       |+---------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3.5 JSON_REMOVE(source,path…)

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

mysql> select json_remove(@v1,'$[0]','$[1]');+--------------------------------+| json_remove(@v1,'$[0]','$[1]') |+--------------------------------+| ["1", ["3", "4"]]              |+--------------------------------+1 row in set (0.00 sec)

这里需要注意的是删除是串行操作的，即先删除$[0]后的JSON文档上基础继续删除$[1]上的元素。

转载地址：http://lenwz.baihongyu.com/

你可能感兴趣的文章