[SQL] Maria DB JSON 데이터 추출하기

DB 컬럼 중 JSON 데이터를 담는 컬럼이 존재할시 , 해당 컬럼의 데이터를 추출할때의 쿼리문 작성에 대한 기록을 하기 위해 글 작성을 진행한다.

하기 아래의 데이터가 존재할시 , 다음과 같은 쿼리문을 통해 키값에 접근하여 value 값을 추출할 수 있다.

json_extract (키값을 통한 값 추출)

[쿼리문 양식]

SELECT json_extract([컬럼명 입력],'$.[Json 데이터 Key 값 입력]')
FROM [테이블 명 입력];

[쿼리문 예제]

SELECT json_extract(data,'$.doc.doc_id')
FROM json_table;

만약 위 결과에서 문자열 따옴표를 지우고 싶다면 다음과 같은 쿼리문 작성이 필요하다.

json_unquote (따옴표 제외)

[쿼리문 양식]

SELECT json_unquote(json_extract([컬럼명 입력],'[Json 데이터 Key 값 입력]'))
FROM [테이블 명 입력];

[쿼리문 예제]

SELECT json_unquote(json_extract(data,'$.doc.doc_id'))
FROM json_table;

위 쿼리문 실행시 , 하기 와 같은 결과를 얻을 수 있다.

번외편

만약 json 데이터 Key 값 안에 또 다른 Key 값에 해당 하는 값이 존재하는 Json 데이터에서 값을 추출하고 가공하는 상황이라고 한다면 , 자바 로직단에서의 처리가 아닌 , 디비 쿼리문에서의 처리는 어떻게 하는가에 대한 궁금증이 생겼다.

예시 Json Data :

[
            {
                "doc_id": "162",
                "appline_div": "20",
                "user_id": "1846"
            },
            {
                "doc_id": "162",
                "appline_div": "10",
                "user_id": "1846"
            }
]

위의 예시 데이터는 , 하나의 row에 특정 키값을 접근하여 데이터 조회시 확인 할수 있는 데이터로 각 row 마다 키값에 해당하는 value의 길이값이 다른 상황이다.

이때 아래와 같은 쿼리를 통해서 조회 할수 있다.

SELECT json_extract([컬럼명 입력], CONCAT('$.컬럼명[', n.n, ']'))
FROM json_table ,
     (SELECT 0 AS n FROM DUAL UNION ALL
      SELECT 1 AS n FROM DUAL UNION ALL
      SELECT 2 AS n FROM DUAL UNION ALL
      SELECT 3 AS n FROM DUAL UNION ALL
      SELECT 4 AS n FROM DUAL UNION ALL
      SELECT 5 AS n FROM DUAL UNION ALL
      SELECT 6 AS n FROM DUAL UNION ALL
      SELECT 7 AS n FROM DUAL UNION ALL
      SELECT 8 AS n FROM DUAL UNION ALL
      SELECT 9 AS n FROM DUAL) AS n
WHERE 1 = 1
AND n.n < json_length(json_extract([컬럼명 입력],'$.[키값]'))

위 쿼리의 원리는 , sql json_extract 함수를 통해 배열의 인덱스 값을 지정하여 , 특정 인덱스에 포함되어 있는 값을 꺼내는 원리인데 , 이때 각 row의 인덱스 (0 ~ 9) 반복문을 돌면서 처리하기가 쿼리 단에서는 쉽지 않기 때문에 , 인덱스값을 임의로 구성 및 대입 해서 처리를 할 수 있다.

더 좋은 방법은 , 디비상의 하나의 테이블을 구성하여 일련의 시퀀스값을 통해 위와 같은 쿼리에 대입하는 방법이 있을 것 같다.