Detailed explanation of querying JSON format fields in MySQL

During the work development process, a requirement was encountered: the customer's name, mobile phone number, ID card, and document type needed to be dynamically stored. This means that the front end may transmit the information of one, two, or three people, which is a dynamic and unfixed number of four fields (name, mobile phone number, ID card, document type).

The front-end page is as follows:

I am using List

[{
	"cardId": "110101199003072316",
	"cstName": "Zhang Shuang'er 1",
	"cstMobile": "13263654144",
	"idCardType": "1"
}, {
	"cardId": "11010119900307571X",
	"cstName": "Zhang Shuang'er 2",
	"cstMobile": "13263654144",
	"idCardType": "1"
}]

The product requirement is to fuzzy query these joint customer information. At the beginning, I wrote it wrongly:

select * from signCustomerGroup like '%儿%'

But later I found a problem. For example, if I fuzzily input the letter c, the English field name 'cardId' on the left would be matched.
Later I learned that MySQL 5.7 and later versions added the json type, and some json type functions can be used to directly query a field in json format.

The correct syntax is as follows:

Table fields:

The main SQL function used is json_extract(), which is used to find all specified data from JSON format

1. json array query

Fuzzy query a field in the json array format:
Directions:
SELECT * FROM table name WHERE json_extract(field name,"$[*].json中key") like '%value to be searched%';
Examples:
SELECT * FROM table WHERE json_extract(sign_customer_info_ext,"$[*].cstName") like '%h%';

Precise query (Note: Precise query must specify the subscript of the array to which the queried field belongs, for example, if the first one is [0], the second one is [1])
SELECT id,sign_customer_info_ext FROM table WHERE json_extract(sign_customer_info_ext,"$[0].cstName") = 'ghhj中文1355';

2. Single json query

Single json parameter in frontend and mysql database:

{
	"cstName": "Ma Yun",
	"cstMobile": "17879767646",
	"idCardType": "1",
	"cardId": "E4813980"
}

Fuzzy query single json query:
Directions:
SELECT id,sign_customer_info_ext FROM table name WHERE json_extract(field name,"$.json中key") like '%马云%';
Examples:
SELECT id,sign_customer_info_ext FROM table WHERE json_extract(sign_customer_info_ext,"$.cstName") like '%Ma Yun%';

Summarize

This is the end of this article about querying json format in MySQL. For more relevant MySQL query json format field content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!