개발 및 데이터분석을 하다 보면 가장 많이 마주치는 데이터 유형 중 하나가 키와 값으로 이루어진 JSON 형태일 것이다. 그간 나 역시 정형 데이터 또는 비정형 데이터를 전달받아 필요한 형태의 JSON 값으로 생성하거나, JSON 형태를 전달받아 작업하기 용이한 형태로 파싱하는 작업은 여러 번 진행했었다. 하지만 하나의 테이블에서 분리되어있는 컬럼 값을 JSON 형태로 결합하는 SQL 문을 작성해 본 경험은 없었다. Oracle, MySQL, MariaDB와 같은 RDB를 사용하고 있다면 하나의 객체와 관련된 정보를 여러 테이블로 쪼개어 저장할 텐데, 이 쪼개어 저장된 정보가 1:N의 관계를 가지고 하나의 테이블로 다시 통합해 외부로 전달해야 하는 상황이라면 단순 통합만으로는 많은 양의 데이터 중복이 발생하게 된다. 이를 해결하기 위해 MySQL/MariaDB에서 기존 컬럼 값을 결합하여 JSON 형식 컬럼을 생성하는 2가지 방법을 간단한 예시와 함께 정리하려 한다.

[ 요약 ]

❗버전확인 필수❗
- MySQL : v 8.0 ~
- mariaDB : v 10.5.0 ~

• 방법1 : 기존 테이블의 컬럼 값을 JSON의 value 값으로만 활용하고 싶은 경우

SELECT 그루핑 할 컬럼명,
	JSON_ARRAYAGG(JSON_OBJECT('표시할 key명', 기존 테이블 컬럼명1, '표시할 value명', 기존 테이블 컬럼명2))
FROM 테이블명
GROUP BY 그루핑 할 컬럼명

• 방법2 : 기존 테이블의 컬럼 중 대응되는 컬럼 2개를 하나는 JSON의 key로 다른 하나는 value로 활용하고 싶은 경우

SELECT 그루핑 할 컬럼명,
JSON_OBJECTAGG(기존 테이블 컬럼명1, 기존 테이블 컬럼명2)
FROM 테이블명
GROUP BY 그루핑 할 컬럼명

[ 개념설명 ]

JSON_OBJECT

• Syntax

  JSON_OBJECT([key, value[, key, value] ...])

• Description
  - 키:값 쌍의 목룍을 평가하고 해당 쌍을 JSON 객체로 반환
  - 키/값 목록은 비어 있을 수 있음
  - 값으로는 NULL이 가능하지만, 키로 NULL 값이 오면 오류 발생
  - 인수 개수가 홀수일 경우도 오류 발생

• Example

  SELECT JSON_OBJECT("id", 1, "name", "Monty");
  
  +---------------------------------------+
  | JSON_OBJECT("id", 1, "name", "Monty") |
  +---------------------------------------+
  | {"id": 1, "name": "Monty"}            |
  +---------------------------------------+

JSON_ARRAYAGG

• Syntax

  JSON_ARRAYAGG(column_or_expression)

• Description
  - 주어진 JSON 또는 SQL 값의 요소를 포함하는 JSON 배열로 반환
  - 단일 값으로 평가되는 열 또는 표현식에 동작
  - 오류가 발생하거나 결과에 행이 없으면 NULL 값 반환
  - 현재는 윈도우 함수로 사용할 수 없음

• Example

  CREATE TABLE t1 (a INT, b INT);
   INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);
   SELECT * FROM t1;
  
  +--------+
  | a | b |
  +--------+
  | 1 | 1 |
  +--------+
  | 2 | 1 |
  +--------+
  | 1 | 1 |
  +--------+
  | 2 | 1 |
  +--------+
  | 3 | 2 |
  +--------+
  | 2 | 2 |
  +--------+
  | 2 | 2 |
  +--------+
  | 2 | 2 |
  +--------+

  • 각 컬럼의 모든 요소들이 json 형태로 반환

  SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
  
  +-------------------+-------------------+
  | JSON_ARRAYAGG(a)  | JSON_ARRAYAGG(b)  |
  +-------------------+-------------------+
  | [1,2,1,2,3,2,2,2] | [1,1,1,1,2,2,2,2] |
  +-------------------+-------------------+

  • b컬럼을 기준으로 그루핑 된 상태에서 각 컬럼의 요소들이 json 형태로 반환

  SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
  
  +------------------+------------------+
  | JSON_ARRAYAGG(a) | JSON_ARRAYAGG(b) |
  +------------------+------------------+
  | [1,2,1,2]        | [1,1,1,1]        |
  | [3,2,2,2]        | [2,2,2,2]        |
  +------------------+------------------+

JSON_OBJECTAGG

• Syntax

  JSON_OBJECTAGG(key, value)

• Description
  - 키:값 쌍을 JSON 객체로 반환
  - 단일 값으로 평가되는 2개의 표현식을 받거나 두개의 컬럼명을 인수로 받음
  - 첫 번째 인자는 키로, 두 번째 인자는 값으로 반환 됨
  - 오류가 발생하거나 결과에 행이 없으면 NULL 값 반환
  - 현재는 윈도우 함수로 사용할 수 없음
• Example

  select * from t1;
  +------+-------+
  | a    | b     |
  +------+-------+
  |    1 | Hello |
  |    1 | World |
  |    2 | This  |
  +------+-------+

  SELECT JSON_OBJECTAGG(a, b) FROM t1;
  +----------------------------------------+
  | JSON_OBJECTAGG(a, b)                   |
  +----------------------------------------+
  | {"1":"Hello", "1":"World", "2":"This"} |
  +----------------------------------------+

[ 실습 ]

사전준비 : 테스트 테이블 생성 및 더미데이터 삽입

-- usr_profile 테이블 생성
CREATE TABLE `usr_profile` (
  `USR_ID` varchar(20) NOT NULL COMMENT '유저아이디',
  `TITLE` varchar(2000) DEFAULT NULL COMMENT '프로필 제목',
  `CONTENT` text DEFAULT NULL COMMENT '프로필 내용',
  PRIMARY KEY (`USR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='프로필';

-- usr_skill 테이블 생성
CREATE TABLE `usr_skill` (
  `USR_ID` varchar(20) CHARACTER SET euckr NOT NULL COMMENT '유저아이디',
  `SKILL_NO` int(11) NOT NULL COMMENT '보유기술순번',
  `SKILL_NM` varchar(200) CHARACTER SET euckr DEFAULT NULL COMMENT '보유기술명',
  `SKILL_LEVEL` varchar(20) CHARACTER SET euckr DEFAULT NULL COMMENT '숙련도',
  PRIMARY KEY (`USR_ID`,`SKILL_NO`)
) COMMENT='보유기술';

INSERT INTO `usr_profile` VALUES ('id1', '3년차 파이썬 개발자 김떙떙입니다!', 'django, flask, fastAPI 등 다양한 파이썬 프레임워크를 활용해 백엔드 구축 및 스크래핑 프로그램 개발을 해왔습니다.'),
								('id2', '열정과 패기로 똘똘뭉친 신입개발자 이빵빵입니다.', '안녕하세요. 이제 막 개발에 입문한 신입개발자이지만 지난 6개월의 국비과정을 통해 2건의 프로젝트를 수행해본 경험이 있습니다. 최선을 다하겠습니다.'),
                                ('id3', '십여년간의 축적된 개발 경험을 보유한 풀스택개발자입니다.', '자바 및 스프링부트 기반으로 공공/금융 프로젝트를 주로 수행해왔습니다. 다년간의 경험으로 확실한 결과물을 약속드립니다.');

INSERT INTO `usr_skill` VALUES ('id1', '1', 'python', '상'), 
								('id1', '2', 'fastAPI', '중'),
                                ('id3', '1', 'java', '특상'),
                                ('id3', '2', '스프링부트', '특상'),
                                ('id3', '3', '자바스크립트', '상'),
                                ('id3', '4', 'C++', '중');

방법1 : JSON_OBJECT + JSON_ARRAYAGG

-- [{'스킬명':SKILL_NM, '스킬레벨':'SKILL_LEVEL'}···] 형태로 SKILL 칼럼 생성방법
select USR_ID
	, JSON_ARRAYAGG(JSON_OBJECT('스킬명', SKILL_NM, '스킬레벨', SKILL_LEVEL)) as SKILL
from usr_skill
group by USR_ID
;

-- usr_profile 테이블과 join
select a.USR_ID, 
	a.TITLE, 
    a.CONTENT, 
    b.SKILL
from usr_profile as a 
left join(select USR_ID, JSON_ARRAYAGG(JSON_OBJECT('스킬명', SKILL_NM, 
													'스킬레벨', SKILL_LEVEL)) as SKILL 
	from usr_skill 
    group by USR_ID) as b 
on a.USR_ID = b.USR_ID;

방법2 : JSON_OBJECTAGG

-- [{SKILL_NM : SKILL_LEVEL}···] 형태로 SKILL 칼럼 생성방법
select USR_ID
	, JSON_ARRAYAGG(JSON_OBJECT('스킬명', SKILL_NM, '스킬레벨', SKILL_LEVEL)) as SKILL
from usr_skill
group by USR_ID
;

-- usr_profile 테이블과 join
select a.USR_ID, 
	a.TITLE, 
    a.CONTENT, 
    b.SKILL
from usr_profile as a 
left join(select USR_ID, JSON_OBJECTAGG(SKILL_NM, SKILL_LEVEL) as SKILL 
	from usr_skill 
    group by USR_ID) as b 
on a.USR_ID = b.USR_ID;

💡 CONVERT 함수
만일 JSON 형태로 반환하는 과정에 인코딩 에러가 발생한다면 내장 함수 중 CONVERT 함수에 대한 documentation 확인!

[ Reference ]

• MySQL 공식문서_JSON 함수
• mariaDB 공식문서_JSON 함수
• 최초에 힌트 얻은 stack overflow 페이지