[MySQL] GROUP_CONCAT으로 압축한 행 풀기

개요

2024.09.10-[MySQL] GROUP_CONCAT - 그룹 별 행 압축에서 특정 컬럼에 대해 그룹 별로 압축하는 GROUP_CONCAT 함수를 알아보았다. 이번 글에서는 JSON_TABLE, JSON_ARRAY를 활용하여 압축한 행을 다시 풀어내는 방법을 적어본다.

 

 

예제 데이터

다음과 같은 예제 데이터를 사용하여 방법을 적어둔다.

| type    | names    |
| ------- | -------- |
| cooker  | 뒤집개      |
| fruit   | 수박,사과,사과 |

 

DDL

CREATE TABLE objects (
  type varchar(50),
  names varchar(100)
);
INSERT INTO objects VALUES ('cooker ', '뒤집개');
INSERT INTO objects VALUES ('fruit', '수박,사과,사과');

 

 

방법

이 방법은 한 줄로 압출된 컬럼을 JSON ARRAY로 변경하여 JSON_TABLE로 변환한 뒤 원본 테이블과 조인하는 방법이다.

 

예시 쿼리 )

SELECT obj.type, b.name
  FROM (
  	SELECT *
      FROM objects
  ) obj
 -- 압축 해제할 컬럼에 대한 테이블 생성
 JOIN json_table(replace(json_array(obj.names), ',', '","'), '$[*]' 
                 columns (
                   name varchar(50) path '$')
      ) b
;

 

 

한 줄 한 줄 해석해보자.

1) JSON_TABLE과 JOIN 할 테이블을 subquery로 생성한다.

SELECT obj.type, b.name
  FROM (
  	SELECT *
      FROM objects
  ) obj

 

2) 한 줄로 압축된 열의 양 끝에 대괄호를 붙인다.

json_array(obj.names)

 

3. ARRAY로 취급할 수 있도록 값 사이에 큰따옴표를 추가한다.

replace(json_array(names), ',', '","')

 

4. ARRAY 데이터를 테이블로 변환한다.

참고 : 2024.09.24-[MySQL] JSON_TABLE - JSON 데이터를 테이블로 변환

json_table(replace(json_array(obj.names), ',', '","'), '$[*]' 
           columns (
             name varchar(50) path '$')
)

 

5. 두 테이블을 조인한다.

SELECT obj.type, b.name
  FROM (
  	SELECT *
      FROM objects
  ) obj
 JOIN json_table(replace(json_array(obj.names), ',', '","'), '$[*]' 
                 columns (
                   name varchar(50) path '$')
      ) b
;

 

실행 결과

 

 

참고 문서

https://yaerhee.tistory.com/13

https://mitw.tistory.com/45