How to get all fields of the data group when mysql parses json data group

Introduction

During the development process, I encountered the string of json data group and needed to parse the json group to get all the information in the group. The following format:

[{"itemId":3101,"itemName":"空滤器及进气管道"},{"itemId":3102,"itemName":"水管、水泵"},{"itemId":3103,"itemName":"柴油管道"},{"itemId":3104,"itemName":"高压泵、机油泵"}]

Observe the json group and find that it is a list containing multiple json strings. What we need to do is to split out all the json strings in the list and parse each json string. .

After analysis, it was found that when the JSON string exists independently, it can be extracted using the JSON_EXTRACT method. Since the list contains multiple JSON strings, we need to split the list into multiple JSON strings.

Before studying the content of this article, you need to understand two functions of mysql in advance:

SUBSTRING_INDEX

JSON_EXTRACT

Step 1: Split one line into multiple lines

Split one line into multiple lines, that is, split the list into multiple lines of json, for this we need

1.1 Create a new table keyid, and only insert numbers starting from 0, as follows:

In other tutorials, it is also possible to use the help_topic_id field of the mysql.help_topic table. However, this library table requires root permissions before it can be used. Therefore, it is most appropriate to build your own matching table.

Note: The value of id cannot be less than the number of json strings in the list. For example, if there are 4 JSON strings in the above list, then the id value must be greater than 4. The maximum value of help_topic_id is 700. If the number of json strings in the list is greater than this value, it is inappropriate to use help_topic_id.

1.2 Find the split identifier

The so-called split identifier is a sign that can be split into multiple lines at one time based on this symbol. In the list below, the split identifier is not found, so it needs to be processed. You can think of ; as a split identifier. The processed content is as follows:

{"itemId":3101,"itemName":"空滤器及进气管道"};{"itemId":3102,"itemName":"水管、水泵"};{"itemId":3103,"itemName":"柴油管道"};{"itemId":3104,"itemName":"高压泵、机油泵"}

Remove the two list signs before and after [ and ], and change },{ into };{ so that ; can be turned into a split identifier. As follows

select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest

1.3 Split multiple rows by join on

At this time, we can connect the maptest table and the newly created keyid table by using the JOIN operation , matches multiple rows of data under the ON condition. Split via SUBSTRING_INDEX.

The code is as follows:

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b 
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

At this point, the work of splitting the json group into multiple lines is completed.

Second step: Parse the json string

After splitting into multiple lines, it can be parsed through JSON_EXTRACT. The effect is as follows:

The completed code is as follows:

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b 
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

Of course, it is also possible to use the help_topic_id field of the mysql.help_topic table. The code and results are as follows:

select 
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ) AS jsonarr_info,
b.help_topic_id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from 
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join mysql.help_topic b 
on b.help_topic_id < ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

Note: If the field parsed by JSON_EXTRACT is a string, it will have "" double quotes. Just replace it. That’s it.

The above is the detailed content of How to get all fields of the data group when mysql parses json data group. For more information, please follow other related articles on the PHP Chinese website!