mysql 쿼리 select 결과 JSON 형태로 내보내기 - GROUP_CONCAT

들어가며

 

mysql에서 select 쿼리 결과를 JSON형태로 뽑아주는 함수가 있습니다.

 

JSON_ARRAY, JSON_OBJECT, JSON_QUOTE

 

사용법은 공식 문서를 통해 확인할 수 있으며 MYSQL 5.7 버전 이상에서만 지원되는 함수입니다.

https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.17.2 Functions That Create JSON Values

 

그 하위 버전의  MYSQL 서버를 사용중인 레거시 시스템에서는 해당 JSON 함수를 사용하지 못하기 때문에 SELECT 절에서 직접 구현을 해서 사용해야 합니다.

이번에 레거시 시스템 서버 코드를 좀 더 쉽게 풀어낼 수 있도록 서브쿼리에서 뽑혀 나오는 결과물은 JSON 형태로 나오도록 쿼리를 작성해서 서버 코드 작성이 한결 쉬웠었는데 그 과정을 남기고자 간단한 예제를 통해 확인해보려 합니다.

 

 

 

JSON 출력 쿼리 작성

 

대상 테이블은 msyql에 기본적으로 제공되는 HELP_KEYWORD 테이블로 정했습니다.

아래는 테이블 SELECT 조회 시 나오는 데이터들인데 도움말 키워드 리스트가 들어있는 것으로 판단됩니다.

 

 

 

 

이 데이터를 JSON 데이터 형식으로 출력 되도록 CONCAT 과 GROUP_CONCAT 을 활용해 SELECT 쿼리를 구성했습니다.

CONCAT은 문자열 이어붙여주는 함수인데 GROUP_CONCAT는 같은 그룹별로 문자열을 이어붙여주는 함수입니다.

사용법이 간단하니 생소하시다면 공식문서를 참고하시기 바랍니다.

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

MySQL :: MySQL 8.0 Reference Manual :: 12.19.1 Aggregate Function Descriptions

 

 

GROUP BY 를 쓰지 않았기 때문에 결과행으로 1개만 제공이 되며 전체 데이터를 JSON 형태로 출력하도록 했습니다.

SELECT                     
	CONCAT(
	'[',
	GROUP_CONCAT(
		CONCAT('{',
		'"help_keyword_id":"', help_keyword_id, '"',
		',"name":"', NAME, '"}')
	),
	']'
	) as JSON_OBJ
	
FROM 
	mysql.help_keyword
WHERE help_keyword_id < 5

 

다음과 같이 JSON 배열 형태로 떨어지는 데이터를 확인할 수 있으며 서버단에서 해당 데이터를 JSON 객체로 받아와서 사용이 가능하며 API 로 호출된 결과물은 클라이언트로 JSON 데이터 그대로 내려보내 줄 수 있어서 추가적인 가공이 필요없이 매우 편리하게 사용할 수 있습니다.

[
	{
        "help_keyword_id": "0",
        "name": "JOIN"
    }, {
        "help_keyword_id": "1",
        "name": "HOST"
    }, {
        "help_keyword_id": "2",
        "name": "REPEAT"
    }, {
        "help_keyword_id": "3",
        "name": "SERIALIZABLE"
    }, {
        "help_keyword_id": "4",
        "name": "REPLACE"
    }
]

 

 

FUNCTION 정의는 어려운걸로

 

해당 SELECT 절을 FUNCTION 으로 재작성하고 싶었으나 실패했습니다. 동적쿼리에서 파라미터 자체를 컬럼명과 변수값으로 둘다 활용할수는 없기 때문에 모든 컬럼을 정의하는 함수를 작성하기가 어려웠기 때문입니다. 더이상 시간 낭비하고 싶지 않아서 과감히 포기했으며 불편하지만 필요할 때마다 위 쿼리를 활용해 재작성하기로 합니다.