maria db에서 JSON Array 형태로 출력?

 SELECT 
 	JSON_ARRAYAGG(CONVERT(JSON_OBJECT('a',a,'b',b) USING UTF8)) AS 'DATA' 
	FROM t1 ;​

 SELECT 
 	JSON_ARRAYAGG(CONVERT(JSON_OBJECT('a',a,'b',b) USING UTF8)) AS 'DATA' 
	FROM t1 ;

 

 

 

우리가 jascript 라이브러리를 사용하다보면 일반 데이터를 json 형태나 json array 형태의 데이터로 바꿔서 사용해야하는 경우가 많다.

 

예를 들어서 nhn에서 제공하는 tui.grid 라이브러리를 사용하고싶을 경우 아래와 같이 array구조안에 json데이터들이 들어간 json Array 형태로 출력해서 사용한다.

 

 

columns: [
        {
          header: 'Name',
          name: 'name'
        },
        {
          header: 'Artist',
          name: 'artist'
        },
        {
          header: 'Type',
          name: 'type'
        },
        {
          header: 'Release',
          name: 'release'
        },
        {
          header: 'Genre',
          name: 'genre'
        }
      ]

 

 

 

물론 db에서 받은 데이터를  javascript단에서  foreach문이나 for문을 이용해 출력하는 방법도 있겠지만 이글에서는 아예 db에 출력될때부터 json array형태로 출력하는 방법을 적고자 한다(mariaDB기준)

 

 

우선 샘플로 할 테이블은 다음과 같다. 이예제는 mariadb에 있는 예제이다.

 

 

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);

 

 

이 데이터를 출력하면 다음과 같다.

 

 

 

 

우리는 이데이터를 궁극적으로 이러한 형태로 바꿔줄 것이다.

 

[
	{
    	"a": 1, 
        "b": 1
     }
    ,{
    	"a": 2, 
        "b": 1
      }
    ,{
    	"a": 1,
        "b": 1
      }
    ,{
    	"a": 2, 
        "b": 1
     }
    ,{
    	"a": 3, 
      	"b": 2
      }
    ,{
    	"a": 2, 
        "b": 2
      }
    ,{
    	"a": 2, 
        "b": 2
      }
    ,{
    	"a": 2, 
        "b": 2
      }
    ]

 

 

 

그렇다면 작업은 총 2가지이다.

• 각 row를 json형태로 만들어준다
• json형태를 리스트로 넣어준다.

 

 

 

JSON형태를 만들어 주는 함수는 JSON_OBJECT 함수를 이용한다.

 /*
 
 JSON_OBJECT("키값으로 쓸 명칭1",value1,"키값으로 쓸 명칭2",value2)로 구성
 */
SELECT 
 	JSON_OBJECT('a',a,'b',b) AS 'DATA' 
FROM t1 ;

 

그러면 아래 그림과 같이 이쁜 json형태로 된 row들이 나온다.

만약에

a컬럼의 value를 키값, b컬럼의 value를 value값으로 쓸려고 하는 경우에는 JSON_OBJECTAGG(key컬럼,value컬럼)을 이용하면 된다.해당함수는 mariadb 10.5부터 지원한다..

 

 

 

 

이형태로 javascript에서 for문을 돌려 array로 넣을수도 있겠지만.. 아예 array로 만들어 보자..array로 만드는 함수는 JSON_ARRAYAGG 함수를 이용하면 된다.

 

 SELECT 
 	JSON_ARRAYAGG(JSON_OBJECT('a',a,'b',b)) AS 'DATA' 
FROM t1 ;

 

 

드디어 원하는 형태가 나왔다.

 

 

 

이함수는 group by도 적용이 가능하다.

 

 

 

 SELECT 
 	a,JSON_ARRAYAGG(JSON_OBJECT('a',a,'b',b)) AS 'DATA' 
	FROM t1  GROUP BY a;

 

 

 

 

 

 

※만일 JSON_ARRARYAGG가 안되는 경우

DELIMITER //

DROP FUNCTION IF EXISTS JSON_ARRAYAGG//

CREATE AGGREGATE FUNCTION IF NOT EXISTS JSON_ARRAYAGG(next_value TEXT) RETURNS TEXT
BEGIN  

 DECLARE json TEXT DEFAULT '[""]';
 DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN json_remove(json, '$[0]');
      LOOP  
          FETCH GROUP NEXT ROW;
          SET json = json_array_append(json, '$', next_value);
      END LOOP;  

END //
DELIMITER ;

위의 sql문을 이용해 JSON_ARRAYAGG를 만들어준다.

 

 

※한글로 json array를 만드는데 글자가 깨지는 경우 convert 함수를 이용하면 된다

 

 SELECT 
 	JSON_ARRAYAGG(CONVERT(JSON_OBJECT('a',a,'b',b) USING UTF8)) AS 'DATA' 
	FROM t1 ;

 

※2022.12.29 추가 convert함수를 이용해도 글자가 깨지는 경우

 

 

 /* 
 깨지는 이유
 mariadb는 10.5부터 json_arrayagg가 내장함수 이다.
 그런데 이유는 모르겠지만 
 시스템 캐릭터 셋을 이리저리 바꾼경우에
 convert를 이용해도 글자가 제대로 출력이 안되는 경우가 있다.
 이럴때는
 json_arrayagg함수를 해당 DB에 만들어주고
 json_array앞에 db명을 언급하여
 해당 DB의 함수를 사용하겠금 강제하면 된다.
 
 */
 
 
 /*
     혹시 모를
     json_arrayagg 생성문

     DELIMITER $$

    DROP FUNCTION IF EXISTS `JSON_ARRAYAGG` $$

        CREATE AGGREGATE FUNCTION IF NOT EXISTS `JSON_ARRAYAGG`(`next_value` TEXT CHARSET UTF8) RETURNS TEXT CHARSET utf8mb3
        BEGIN  

        DECLARE `json` TEXT CHARSET utf8mb4 DEFAULT '[""]' ;
        DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN JSON_REMOVE(`json`, '$[0]');
       LOOP  FETCH GROUP NEXT ROW;
              SET `json` = CONVERT(JSON_ARRAY_APPEND(`json`, '$', `next_value`) USING utf8mb3);
       END LOOP;  

    END $$
    DELIMITER ;
 
 */
 
 
 SELECT 
 /* DBNAME의 본인의 database 명을 쓸것!*/
 	DBNAME.JSON_ARRAYAGG(CONVERT(JSON_OBJECT('a',a,'b',b) USING UTF8)) AS 'DATA' 
	FROM t1 ;

 

※2023.02.29 escape 문자가 나오는 경우 

내가 이글을 몇번이고 수정하게 될줄 몰랐슴다

 

maria db가 조금 낮은 경우 JSON_ARRAYAGG랑 JSON_OBJECT를 섞어 쓰는 경우 쌍따옴표에 escape 문자가 나오는 경우가 있슴다.

내가 작성한 select문중 한줄

JSON_OBJECT('name','인사평가E','data',JSON_ARRAYAGG(GROUP_DATA.HR_E_RATE)) AS 'HR_E_RATE'
,JSON_ARRAYAGG(JSON_OBJECT('name','미작성','data',JSON_ARRAY(GROUP_DATA.MNG_NO_RATE,GROUP_DATA.HR_NO_RATE))) AS 'NO_RATE'

 

본래 나와야하는 형태

#첫번째 데이터
{"name": "인사평가E", "data": [0.0000,0.0000]}
#두번째 데이터
[{"name": "미작성", "data": [27.4194, 96.7742]},{"name": "미작성", "data": [34.3750, 96.8750]}]

 

DB 버전이 낮아 escape문자가 나오는경우(mariadb 10.6.5 이하) 

escape문자가 붙으면서 전체가 string되버린다.

#첫번째 데이터
{"name": "인사평가E", "data": [0.0000]}
#두번째 데이터
["{\"name\": \"미작성\", \"data\": [24.1935, 96.7742]}", "{\"name\": \"미작성\", \"data\": [28.1250, 96.8750]}"]

 

마리아 db버그라고 하는데.... 10.6.5 이하버전은 아래같이 나와버린다..

방법이 여러가지 인데

우선

첫줄 같은 경우 말고 JSON_OBJECT곁에  JSON_COMPACT를 둘러서 쓰는 것이다.

SELECT
JSON_OBJECT('name','인사평가E','data', JSON_COMPACT(JSON_ARRAYAGG(GROUP_DATA.HR_E_RATE))) AS 'HR_E_RATE'

 

하지만 안에 있는 array숫자가 string으로 인식된다

{"name": "인사평가E", "data": ["0.0000", "0.0000"]}

 

하지만 두번째줄에 JSON_COMPACT를 쓰는 경우...원하는 데이터 형태가 나오지 않는다.

JSON_COMPACT(JSON_ARRAYAGG(JSON_OBJECT('name','미작성','data',JSON_ARRAY(GROUP_DATA.MNG_NO_RATE,GROUP_DATA.HR_NO_RATE)))) AS 'NO_RATE'

 

그래서 그냥 수동으로 replace하는 DB함수를 만들었다..

DELIMITER $$
CREATE FUNCTION `FN_HANDLING_JSON` (`val` TEXT) RETURNS TEXT
BEGIN
	DECLARE `r_result_val` TEXT;
	
	SELECT REPLACE(REPLACE(REPLACE(JSON_COMPACT( `val`),'\\', ''),'"{"','{"'),']}"',']}') INTO `r_result_val`;
	RETURN r_result_val;
	
 END $$

DELIMITER;

 

 

적용

SELECT JSON_OBJECT('name','인사평가E','data', JSON_COMPACT(JSON_ARRAYAGG(GROUP_DATA.HR_E_RATE))) AS 'HR_E_RATE'
            	,FN_HANDLING_JSON(JSON_ARRAYAGG(JSON_OBJECT('name','미작성','data',JSON_ARRAY(GROUP_DATA.MNG_NO_RATE,GROUP_DATA.HR_NO_RATE)))) AS 'NO_RATE'

 

결과 데이터

{"name": "인사평가E", "data": ["0.0000", "0.0000"]}
[{"name": "미작성", "data": [24.1935, 96.7742]},{"name": "미작성", "data": [28.1250, 96.8750]}]

 

 

 

 

출처:

https://nhn.github.io/tui.grid/latest/tutorial-example10-data-source

https://nhn.github.io/tui.grid/latest/tutorial-example10-data-source/

https://mariadb.com/kb/en/json_arrayagg/

JSON_ARRAYAGG

https://mariadb.com/kb/en/json_object/

JSON_OBJECT

https://stackoverflow.com/questions/50750379/aggregate-function-in-mariadb

Aggregate function in mariaDB

https://stackoverflow.com/questions/69872120/json-object-inside-json-arrayagg-sometimes-return-json-array-sometimes-string

JSON_OBJECT inside JSON_ARRAYAGG sometimes return json array, sometimes string

 

 

사실 이내용은 아래 페이지에 다있다...

 

https://velog.io/@dev_grow_up/MySQLMariaDB-%EA%B8%B0%EC%A1%B4-%EC%BB%AC%EB%9F%BC-%EA%B0%92-%EA%B2%B0%ED%95%A9%ED%95%98%EC%97%AC-JSON-%ED%98%95%EC%8B%9D-%EC%BB%AC%EB%9F%BC-%EC%83%9D%EC%84%B1-%EB%B0%A9%EB%B2%95

MySQL/MariaDB 기존 컬럼 값 결합하여 JSON 형식 컬럼 생성 방법

 

다음글을 이것을 자바단에서 불러내어 화면단으로 데이터를 출력하는 작업을 하고자한다.