mysql查詢json數據web,mysql處理json數據 查詢條件

本文目錄一覽：

• 1、Mysql查詢json數據特定key的值
• 2、mysql5.7查詢json字元串的某個屬性值
• 3、mysql json字元串查詢
• 4、mysql怎麼搜索json格式的數據
• 5、mysql中的json數據類型

Mysql查詢json數據特定key的值

mysql某張表中有一個欄位為json格式，假設欄位名為properties

{

“ocsp.event.append-timestamp.enable”: “true”,

“ocsp.streaming.data.filter.expression”: “id=e4_json”,

“ocsp.event.enable”: “true”,

“ocsp.schema.field.names”: “id”,

“ocsp.event.output.numPartitions”: “0”,

“ocsp.event.json-format.enable”: “true”,

“ocsp.streaming.field.translation.enable”: “false”,

“ocsp.event.append-id.enable”: “false”,

“ocsp.streaming.data.keys”: “id”,

“ocsp.stream.sql”: “SELECT id FROM uuu_5dea34758400_oi5xs0rdaf WHERE id=e4_json”,

“ocsp.kafka.topic”: “hn_20210216b”,

“ocsp.event.period”: “{\”period\”:\”day\”,\”time\”:[{\”begin\”:{\”d\”:\”0\”,\”h\”:\”03:30:23\”},\”end\”:{\”d\”:\”0\”,\”h\”:\”23:20:23\”}}],\”startDate\”:\”2021-02-16\”,\”endDate\”:\”2021-03-28\”}”,

“ocsp.event.periodSwitch”: “true”,

“ocsp.event.output”: “5d74080d1ac0”

}

現在需要查詢ocsp.stream.sql的值 可以使用json_extract函數。注意如果該key是由點分隔符組成的，則需要用雙引號將整個key包起來

select json_extract(properties,’$. “ocsp.schema.field.names” ‘) from COMPONENT where type=’EVENT’ and id=’5dea403ed7c0′;

json_keys函數可以用來獲取json中所有的key欄位

同時 select json_keys (properties) from COMPONENT where type=’EVENT’ and id=’5dea403ed7c0′;

mysql5.7查詢json字元串的某個屬性值

可以使用JSON_EXTRACT函數查詢json欄位或者json字元串類型的text欄位中json對象的某個屬性值，注意層級需要用.號連接，例如：

SELECT id, JSON_EXTRACT(content, ‘$.all_result.quality_result.total_score’) AS total_score FROM tableName

其中，content是表的欄位，裡面存放json格式字元串，

$.all_result.quality_result.total_score表示：content欄位的json字元串中all_result的屬性下quality_result屬性的總分數total_score。

content存儲的內容：

{

“return_code”: “200”,

“return_info”: “處理成功”,

“all_result”: {

“quality_result”: {

“quality_items”: {

“數學”: 5.0,

“語文”: 5.0,

“英語”: 0.0

},

“total_score”: 10.0

}

}

}

mysql json字元串查詢

注意一：欄位類型為varchar、char、text等字元類型

注意二：內容為json

注意三：mysql伺服器版本為5.7及以上

語法：select * from 表名 where 欄位-‘$.屬性’=’查詢內容’;

select * from table1 where col1-‘$.name’=’xiaoming’;

mysql怎麼搜索json格式的數據

在MySQL與PostgreSQL的對比中，PG的JSON格式支持優勢總是不斷被拿來比較。其實早先MariaDB也有對非結構化的數據進行存儲的方案，稱為dynamic column，但是方案是通過BLOB類型的方式來存儲。這樣導致的問題是查詢性能不高，不能有效建立索引，與一些文檔資料庫對比，優勢並不大，故在社區的反應其實比較一般。當然，MariaDB的dynamic column功能還不僅限於非結構化數據的存儲，但不在本文進行展開。

MySQL 5.7.7 labs版本開始InnoDB存儲引擎已經原生支持JSON格式，該格式不是簡單的BLOB類似的替換。原生的JSON格式支持有以下的優勢：

JSON數據有效性檢查：BLOB類型無法在資料庫層做這樣的約束性檢查

查詢性能的提升：查詢不需要遍歷所有字元串才能找到數據

支持索引：通過虛擬列的功能可以對JSON中的部分數據進行索引

首先我們來看如何在MySQL中使用原生的JSON格式：

mysql create table user ( uid int auto_increment,

– data json,primary key(uid))engine=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql insert into user values (NULL,

– ‘{“name”:”David”,”mail”:”jiangchengyao@gmail.com”,”address”:”Shangahai”}’);

Query OK, 1 row affected (0.00 sec)

mysql insert into user values (NULL,'{“name”:”Amy”,”mail”:”amy@gmail.com”}’);

Query OK, 1 row affected (0.00 sec)

可以看到我們新建了表user，並且將列data定義為了JSON類型。這意味著我們可以對插入的數據做JSON格式檢查，確保其符合JSON格式的約束，如插入一條不合法的JSON數據會報如下錯誤：

mysql insert into user values (NULL,”test”);

ERROR 3130 (22032): Invalid JSON text: “Invalid value” at position 2 in value (or column) ‘test’.

此外，正如前面所說的，MySQL 5.7提供了一系列函數來高效地處理JSON字元，而不是需要遍歷所有字元來查找，這不得不說是對MariaDB dynamic column的巨大改進：

mysql select jsn_extract(data, ‘$.name’),jsn_extract(data,’$.address’) from user;

+—————————–+——————————-+

| jsn_extract(data, ‘$.name’) | jsn_extract(data,’$.address’) |

+—————————–+——————————-+

| “David” | “Shangahai” |

| “Amy” | NULL |

+—————————–+——————————-+

2 rows in set (0.00 sec)

當然，最令人的激動的功能應該是MySQL 5.7的虛擬列功能，通過傳統的B+樹索引即可實現對JSON格式部分屬性的快速查詢。使用方法是首先創建該虛擬列，然後在該虛擬列上創建索引：

mysql ALTER TABLE user ADD user_name varchar(128)

– GENERATED ALWAYS AS (jsn_extract(data,’$.name’)) VIRTUAL;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql select user_name from user;

+———–+

| user_name |

+———–+

| “Amy” |

| “David” |

+———–+

2 rows in set (0.00 sec)

mysql alter table user add index idx_username (user_name);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

然後可以通過添加的索引對用戶名進行快速的查詢，這和普通類型的列查詢一樣。而通過explain可以驗證優化器已經選擇了在虛擬列上創建的新索引：

mysql explain select * from user where user_name='”Amy”‘\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

partitions: NULL

type: ref

possible_keys: idx_username

key: idx_username

key_len: 131

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

可以發現MySQL 5.7對於JSON格式堪稱完美，相信PostgreSQL陣營需要尋找新的策略來「攻擊」MySQL了吧。如無意外，還是會停留在優化器這塊，畢竟這塊是目前MySQL必須要克服的最大問題，好在MySQL團隊已經在重構優化器代碼，相信更好的優化器將會在下一個版本中全面爆發。而一大堆文檔資料庫們已經哭暈在廁所了吧。

mysql中的json數據類型

JSON數組包含一個由逗號分隔的值列表，並包含在 字元[和]字元中：

JSON對象包含一組由逗號分隔的鍵值對，並包含在字元{和 }字元中，JSON對象中的鍵必須是字元串：

在JSON數組元素和JSON對象鍵值中允許嵌套：

在MySQL中，JSON值被寫為字元串。MySQL解析在需要JSON值的上下文中使用的任何字元串，如果它作為JSON無效則會產生錯誤。

1.JSON_ARRAY 生成json數組

JSON_ARRAY(val1,val2,val3…)生成一個包含指定元素的json數組。

2.JSON_OBJECT 生成json對象

JSON_OBJECT(key1,val1,key2,val2…) 生成一個包含指定K-V對的json object。如果有key為NULL或參數個數為奇數，則拋錯。

3.JSON_QUOTE 加”號

JSON_QUOTE(json_val) — 將json_val用”號括起來。

2.插入記錄

創建一個表

插入含有json數組的記錄

插入含有json對象的記錄

路徑表達式對於提取JSON文檔的一部分或修改JSON文檔的函數很有用，以指定該文檔中的操作位置。例如，以下查詢從JSON文檔中提取具有 name 鍵的成員的值 ：

路徑語法使用前導 $ 字元來表示正在考慮的JSON文檔，可選地後跟選擇器，它們連續指示文檔的更多特定部分：

如果路徑表達式中的未加引號的鍵名稱不合法，則必須引用命名鍵的路徑組件。讓我們 $ 參考這個值，且密鑰都包含空格，必須引用：

可以使用帶有 to 關鍵字的範圍來指定JSON數組的子集。

last關鍵字被支撐為最後一個元素的陣列中的索引的同義詞。表單的表達式可用於相對定址，也可用於範圍定義，如下所示： last – * N *

4.JSON_REMOVE()獲取JSON文檔和一個或多個指定要從文檔中刪除的值的路徑。返回值是原始文檔減去文檔中存在的路徑選擇的值

JSON值可以使用進行比較 =、、=、、=、、!=、=

JSON值尚不支持以下比較運算符和函數：BETWEEN、IN()、GREATEST()、LEAST()

以上列出的比較運算符和函數是通過將JSON值轉換為本機MySQL數值或字元串數據類型，使它們具有一致的非JSON標量類型。

JSON值的比較發生在兩個級別。第一級比較基於比較值的JSON類型。如果類型不同，則比較結果僅由哪種類型具有更高優先順序來確定。如果這兩個值具有相同的JSON類型，則使用特定於類型的規則進行第二級比較。

NULL-INTEGER, DOUBLE-STRING-OBJECT-ARRAY-BOOLEAN-DATE-TIME-DATETIME-OPAQUE-BIT-BLOB按此順序優先順序依次變大。

對於具有相同優先順序的JSON值，比較規則是特定於類型的：

1） BLOB、BIT、OPAQUE

比較兩個值 的第一個位元組，其中N是較短值中的位元組數。如果N兩個值的第一個位元組相同，則在較長值之前排序較短的值。

2） DATETIME

表示較早時間點的值在表示稍後時間點的值之前排序。如果兩個值最初 分別來自MySQL DATETIME 和 TIMESTAMP 類型，則它們相等，如果它們代表相同的時間點。

3）TIME

兩個時間值中較小的一個在較大的值之前排序。

4） DATE`

較早的日期是在最近的日期之前訂購的。

5） ARRAY

如果兩個JSON數組具有相同的長度並且數組中相應位置的值相等，則它們是相等的。如果數組不相等，則它們的順序由第一個位置中存在差異的元素確定。首先排序在該位置具有較小值的數組。如果較短數組的所有值都等於較長數組中的相應值，則首先排序較短的數組。

8） STRING

字元串在被比較的兩個字元串以詞法字典序排序 ，其中 N 是較短字元串的長度。如果 N 兩個字元串的第一個 位元組相同，則認為較短的字元串小於較長的字元串。