How to use json_extract function in MySQL

1. json_extract usage scenario description

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.

2. MySQL json_extract function introduction

2.1 Function introduction

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: =, <, <=, >, >=, <>, != and <=> ;.

2.2 Usage

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]

2.3 Notes

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.

3. Data verification

3.1 Extract values ​​from ordinary json

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 :

##nametel_nohobby_1hobby_2hobby_3hobby_4##"zhangsan"3.2 Extract the value of the json array

136-6666-6666	"basketball"	" run"	"sing"	NULL

site_user table

id1234Extract the user’s first tag:

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:

##idnametags##1zhangsanlisiwangwuzhaoliu

"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!