MySQL GROUP_CONCAT()으로 JSON 형식 만드는 방법 – 단계별 예제 가이드

MySQL에서는 GROUP_CONCAT() 함수를 사용해 여러 행의 데이터를 하나의 문자열로 결합할 수 있으며, 여기에 CONCAT() 함수를 함께 활용하면 각 행을 JSON 객체 형태로 감싸서 손쉽게 출력할 수 있습니다.

1. 기본 문법

SELECT yourColumnName1,
   GROUP_CONCAT(CONCAT('{"anyName":"', yourColumnName, '",
   "anyName":"', yourColumnName, '"}')) anyVariableName
   FROM yourTableName
   GROUP BY yourColumnName1;

위 문법을 실제로 이해하기 위해 먼저 샘플 테이블을 생성해 보겠습니다.

2. 테이블 생성

mysql> CREATE TABLE JsonFormatDemo (
   ->    UserId INT,
   ->    UserName VARCHAR(100),
   ->    UserEmail VARCHAR(100)
   -> );
Query OK, 0 rows affected (0.99 sec)

3. 데이터 삽입

INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.

mysql> INSERT INTO JsonFormatDemo VALUES(101,'John','John@gmail.com');
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(101,'Bob','John@gmail.com');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(102,'Carol','Carol@gmail.com');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO JsonFormatDemo VALUES(103,'Sam','Sam@gmail.com');
Query OK, 1 row affected (0.15 sec)

4. 전체 데이터 조회

SELECT 문으로 테이블에 저장된 모든 레코드를 확인합니다.

mysql> SELECT * FROM JsonFormatDemo;

출력 결과

+--------+----------+-----------------+
| UserId | UserName | UserEmail       |
+--------+----------+-----------------+
|    101 | John     | John@gmail.com  |
|    101 | Bob      | John@gmail.com  |
|    102 | Carol    | Carol@gmail.com |
|    103 | Sam      | Sam@gmail.com   |
+--------+----------+-----------------+
4 rows in set (0.00 sec)

5. GROUP_CONCAT()으로 JSON 형식 만들기

이제 GROUP_CONCAT() 함수를 사용해 UserId별로 데이터를 그룹화하고, 각 사용자 정보를 JSON 객체 형식으로 변환하는 쿼리를 실행합니다.

mysql> SELECT UserId,
   -> GROUP_CONCAT(CONCAT('{"Name":"', UserName, '", "Email":"', UserEmail, '"}')) AS JsonFormat
   -> FROM JsonFormatDemo
   -> GROUP BY UserId;

출력 결과

+--------+----------------------------------------------------------------------------+
| UserId | JsonFormat                                                                 |
+--------+----------------------------------------------------------------------------+
|    101 | {"Name":"John", "Email":"John@gmail.com"},{"Name":"Bob", "Email":"John@gmail.com"} |
|    102 | {"Name":"Carol", "Email":"Carol@gmail.com"}                                    |
|    103 | {"Name":"Sam", "Email":"Sam@gmail.com"}                                        |
+--------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

결과를 보면 동일한 UserId를 가진 행들이 하나의 JSON 객체 목록으로 깔끔하게 묶인 것을 확인할 수 있습니다. GROUP_CONCAT()의 기본 구분자는 쉼표(,)이며, SEPARATOR 옵션을 사용해 원하는 구분자로 변경할 수도 있습니다.

참고: MySQL 5.7 이상이라면 JSON_ARRAYAGG() 활용

MySQL 5.7.22 이상 버전을 사용하고 있다면 JSON_ARRAYAGG()와 JSON_OBJECT() 함수를 조합하는 것이 더 좋습니다. 이 방식은 유효한 JSON 타입을 직접 반환하며, 특수문자나 따옴표 이스케이프 처리를 자동으로 수행해 주기 때문에 실무 환경에서 더 안전하고 권장되는 방법입니다.

mysql> SELECT UserId,
   -> JSON_ARRAYAGG(JSON_OBJECT('Name', UserName, 'Email', UserEmail)) AS JsonFormat
   -> FROM JsonFormatDemo
   -> GROUP BY UserId;

또한 GROUP_CONCAT()에는 기본 최대 길이 제한(group_concat_max_len = 1024바이트)이 있으므로, 대량의 데이터를 처리할 때는 다음과 같이 세션 변수 값을 늘려주는 것이 좋습니다.

SET SESSION group_concat_max_len = 1000000;