mysql이 json 데이터 그룹을 구문 분석할 때 데이터 그룹의 모든 필드를 가져오는 방법

Introduction

개발 과정에서 json 데이터 그룹의 문자열을 접했고, 그룹의 모든 정보를 얻기 위해 json 그룹을 파싱해야 했습니다. 다음 형식:

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

json 그룹을 관찰한 결과 여러 개의 json 문자열이 포함된 목록이라는 것을 알았습니다. 우리가 해야 할 일은 목록의 모든 json 문자열을 분할하고 각 json 문자열을 구문 분석하는 것입니다.

분석 결과 JSON 문자열이 독립적으로 존재하는 경우 JSON_EXTRACT 메소드를 사용하여 추출할 수 있는 것으로 나타났습니다. 목록에는 여러 JSON 문자열이 포함되어 있으므로 목록을 여러 JSON 문자열로 분할해야 합니다.

이 글의 내용을 공부하기 전에, mysql의 두 가지 기능을 미리 이해해야 합니다:

SUBSTRING_INDEX

JSON_EXTRACT

1단계: 한 행을 여러 행으로 분할

한 행을 여러 행으로 분할 여러 행, 즉 목록은 json의 여러 줄로 분할됩니다. 이를 위해 새 테이블 키 ID를 만들고 다음과 같이 0부터 시작하는 숫자만 삽입하려면

1.1이 필요합니다.

다른 튜토리얼에서는 다음을 통해 mysql.help_topic 테이블의 help_topic_id 필드도 괜찮습니다. 그러나 이 라이브러리 테이블을 사용하려면 루트 권한이 필요합니다. 따라서 자신만의 매칭 테이블을 구축하는 것이 가장 적합합니다.

참고: id 값은 목록에 있는 json 문자열 수보다 작을 수 없습니다. 예를 들어 위 목록에 4개의 JSON 문자열이 있는 경우 id 값은 4보다 커야 합니다. help_topic_id의 최대값은 700입니다. 목록의 json 문자열 수가 이 값보다 큰 경우 help_topic_id를 사용하는 것은 부적절합니다.

1.2 분할 식별자 찾기

일명 분할 식별자는 이 기호를 기준으로 한 번에 여러 줄로 분할할 수 있는 기호입니다. 아래 목록에는 분할 식별자가 없으므로 처리가 필요합니다. ;을 분할 식별자로 생각할 수 있습니다. 처리된 내용은 다음과 같습니다.

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

두 개의 목록 기호 [ 및 ] 앞뒤를 제거하고 },{를 };{로 변경하여 ; 다음과 같습니다

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

1.3 Join on으로 여러 행을 분할합니다

이때 ON 조건에서 여러 행의 데이터를 일치시키는 JOIN 연산을 사용하여 maptest 테이블과 새로 생성된 keyid 테이블을 연결할 수 있습니다. SUBSTRING_INDEX를 통해 분할됩니다.

코드는 다음과 같습니다.

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 )
;

이제 json 그룹을 여러 줄로 나누는 작업이 완료되었습니다.

2단계: json 문자열 구문 분석

여러 줄로 분할한 후 JSON_EXTRACT를 통해 구문 분석할 수 있습니다. 효과는 다음과 같습니다.

완성된 코드는 다음과 같습니다.

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 )
;

물론, mysql.help_topic 테이블의 help_topic_id 필드를 활용하는 것도 가능합니다. 코드와 결과는 다음과 같습니다.

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 )
;

참고: JSON_EXTRACT로 구문 분석된 필드가 문자열인 경우 "" 큰따옴표가 있으므로 바꾸십시오.

위 내용은 mysql이 json 데이터 그룹을 구문 분석할 때 데이터 그룹의 모든 필드를 가져오는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!