In daily business development, usually a field in the mysql database will need to store a json format string when querying Sometimes the json data is large, and it is inefficient and troublesome to retrieve it all and then parse and query it every time.
Fortunately, Mysql5.7 and later versions provide the json_extract function, which can query the value through key ( If it is a json array type, you can get the value of the corresponding position through the subscript), which is very convenient.
New functions after Mysql5.7 version, Mysql provides a native Json type, the Json value will not be It is stored as a string, but in an internal binary format that allows fast reading of document elements. Json text will be automatically verified when inserting or updating a Json column. Text that fails verification will generate an error message. Json text is created in a standard way, and most comparison operators can be used for comparison operations, such as: =, 91eef5681cad6f0cc96f9adeb2931b45, >=, a8093152e673feb7aba1828c43532094, != and <=> ;.
The data stored is json string (type is vachar).
If you want to query the value of a certain field in json, the method used is: JSON_EXTRACT().
Syntax:
**JSON_EXTRACT(json_doc, path[, path] …)**
Usage tips:
If the json string is not an array, use it directly $.Field name
If the json string is an array [Array], then Directly use $[corresponding index ID]
JSON_EXTRACT performance verification, verify that everything is complete by viewing the execution plan Table scan.
Usage scenarios: If the amount of data is small, it can be used if the json string is large. If the amount of data is large, it is not recommended.
Instructions:
Use ordinary fields $ .KEY
Get the
array field using $.KEY[index]
to get, note that index starts from 0
In actual use, the following json string only needs to be replaced with the corresponding table field, but please note that ETL conversion operations such as empty judgment and replacement of json are required.
select json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.name") as name, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.tel_no") as tel_no, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[0]") as hobby_1, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[1]") as hobby_2, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[2]") as hobby_3, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[3]") as hobby_4;
Result :
tel_no | hobby_1 | hobby_2 | hobby_3 | hobby_4 | |
---|---|---|---|---|---|
136-6666-6666 | "basketball" | " run" | "sing" | NULL |
name | #tags | |
---|---|---|
zhangsan | [“COMMON”] | |
lisi | [“VIP”] | |
wangwu | [“VVIP”,“PLATINUM”] | |
zhaoliu |
select id, name, tags, json_extract(if(LENGTH(tags)>0,tags, '[]'),"$[0]") # 如果tags无数据,赋值为空数组 from site_user;
Result:
tags | ||
---|---|---|
"COMMON" | 2 | |
"VIP" | 3 | |
"VVIP" | 4 | |
NULL |
The above is the detailed content of How to use json_extract function in MySQL. For more information, please follow other related articles on the PHP Chinese website!