程式員需了解的SQL之JSON函數查詢與修改（七）

文章目錄

- 前言
- 1.建立JSON函數
- 2. 查詢JSON函數
- - 2.1 JSON_CONAINS(source,target,path)
  - 2.2 JSON_CONAINS_PATH(source,one or all,path..)
  - 2.3 JSON_EXTRACT(source,path....)
  - 2.4JSON_KEYS(source,path)
  - 2.5 JSON_SEARCH(source,one or all ,search_str)
- 3. 修改JSON函數
- - 3.1 JSON_ARRAY_APPEND(source,path,val)
  - 3.2 JSON_ARRAY_INSERT(source,path,val)
  - 3.3 JSON_REPLACE(source,path,val)
  - 3.4 JSON_SET(source,path,val)
  - 3.5 JSON_MERGE_PRESERVE(source1,source2)
  - 3.5 JSON_REMOVE(source,path.....)

前言

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或者xx
mysql> 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和xx
mysql> 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的key
mysql> select json_search(@name2,'one','23');
+--------------------------------+
| json_search(@name2,'one','23') |
+--------------------------------+
| "$.age"                        |
+--------------------------------+
1 row in set (0.00 sec)
## 查詢JSON至少一個内容是以張開頭的key
mysql> 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)
## 在第一個元素數組中第二個位置上追加 ai
mysql> 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]上的元素。

程式員需了解的SQL之JSON函數查詢與修改（七）

文章目錄

前言

1.建立JSON函數

2. 查詢JSON函數

2.1 JSON_CONAINS(source,target,path)

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

2.3 JSON_EXTRACT(source,path…)

2.4JSON_KEYS(source,path)

2.5 JSON_SEARCH(source,one or all ,search_str)

3. 修改JSON函數

3.1 JSON_ARRAY_APPEND(source,path,val)

3.2 JSON_ARRAY_INSERT(source,path,val)

3.3 JSON_REPLACE(source,path,val)

3.4 JSON_SET(source,path,val)

3.5 JSON_MERGE_PRESERVE(source1,source2)

3.5 JSON_REMOVE(source,path…)

繼續閱讀

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method