json_table, json_array를 활용하여한 delimiter로 여러 내용 출력하여 받기

문제 내용: 구분자(delimiter)로 긴 String 을 n 개의 행으로 출력할 수 없음(substring_index() 는 지정한 index 에서만 split 가능)

해결 방법: json_table, json_array 를 활용하여 문자열을 일괄 행으로 출력 (MySQL 8.0.4 버전부터 가능한 것에 유의)

---

* 실제 테이블 대신 from dual 및 SQLize 로의 쿼리 실행으로 과정 및 결과물을 출력합니다. *

select 'test1:false:String,test2:false:String,test3:false:String,test4:false:String,test5:false:String,'
from dual;

현재 유지보수 하고 있는 DB에서는, 다음과 같이 한 컬럼에 무지막지(...) 하게 많은 내용이 들어가곤 한다.

컬럼 내용 자체를 다 고쳐야 하는 상황에서, 기존의 컬럼 내용과 일치하는 부분을 찾아내기 위해서는

test1:false:String

test2:false:String 

... 과 같이 행이 구분된 데이터로 출력이 필요했다.

 

위에서 보시는 바와 같이 콤마로 줄바꿈 없이 쭉~ 연결되는 데이터여서

우선 각 행으로 구분지어 출력하는 쿼리를 찾아보기로 했다.

 

---

Substring_index(), 다 보여주면 참 좋았을걸... ㅠㅠ

기존 방식으로는

1. substring_index()를 통한 인덱스 넘버링, 필요한 데이터 추출

select substring_index('test1:false:String,test2:false:String,test3:false:String,test4:false:String,test5:false:String,', 
			',', 1)
from dual;

및

2. 프로시저로 선언하여 데이터 쫙 뽑기 (옛날 버전)

https://stackoverflow.com/questions/6152137/mysql-string-split

였던 것으로 확인된다.

 

그러나.. 유감스럽게도

1. 의 결과물은 

왜 한 줄 밖에 출력을 못하니(ㅠㅠ)

아주 정직하게 1의 index 에 해당하는 라인만을 split 하여 출력하고 있었다.

 

2번의 방법과, select substring_index()2, substring_index()2, ... union 을 하는 방법도 있었지만

DB가 외부에 있다 보니 프로시저를 사용하기도 뭐하고... ㅜㅜ

뭔가 더 간단하고 좋은 방법이 있지 않을까 해서 조금 더 서칭했는데

아래와 같은 깔끔한 해결책을 발견했다!

---

json_table은 MySQL 8.0.4 버전에서 부터 지원되고 있는 기능으로,

작성자는 json_array와 같이 쿼리를 작성하여 데이터를 출력해 보려고 한다.

(json_array는 기존에도 지원하는 기능이며, JSON 배열을 반환함)

 

(JSON_TABLE, JSON_ARRAY 소개 부분은 아래 부록 란에 첨부했습니다)

 

test Table 생성 후, select 및 json_table (+ json_array) 처리하는 쿼리

create table test (
    test_column varchar(500)
);

insert into test
    (test_column)
    values
('test1:false:String,test2:false:String,test3:false:String,test4:false:String,test5:false:String,test1:false:String,test2:false:String,test3:false:String,test4:false:String,test5:false:String,')
;

select test_column from test;

select j.name
from test t
join json_table(
  replace(json_array(test_column), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) j
;

json_table 관련 쿼리를 추가 작성할 때는

from 절에 테이블 이름을 작성하고,

join 절에 위치한 json_table 내부에 json_array를 감싼 컬럼 이름을 정의한다.

(하단에는 컬럼의 속성을 정의)

 

(아래의 SQLize 페이지에서 위의 쿼리를 그대로 실행해보실 수 있습니다!)

https://sqlize.online/sql/mysql80/9788a490fa2ff6ac61a88b1528159c91/

*************

 

마지막 문단의 json_table이 포함된 쿼리를 실행하면...

아래와 같이 "," 구분자를 기준으로 정리된 라인들이 일괄 출력된다!

큰 묶음의 String이 일괄 행으로 변환되었다

몇 십개 묶음의 데이터를 한 컬럼에 콤마로 잔뜩 합쳐 넣어서 힘들었는데

앞으로 json_array는 유용하게 잘 활용할 것 같다. :)

---

출처

https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows

SQL split values to multiple rows

 

부록

 

JSON_TABLE
https://dev.mysql.com/blog-archive/json_table-the-best-of-both-worlds/

MySQL dev 페이지에서 안내하는 JSON_TABLE. (MySQL에서 나온 첫 테이블 함수라고 한다!)

JSON_TABLE 의 유형, 데이터를 출력할 컬럼의 갯수 등을 설정한 모양새로 출력할 수 있다.

(JSON data, JSON Array data 등의 유형에 대응 가능)

MySQL :: JSON_TABLE - The Best of Both Worlds

 

MySQL에서의 JSON 활용 (JSON_ARRAY 포함)

https://www.joinc.co.kr/w/man/12/mysql/json

Mysql에서 JSON 활용