DB에 저장된 JSON 데이터의 키 값을 쿼리로 추출하기

추출 예시

DB에 JSON 형식을 문자열로 그대로 저장했을 경우 이것을 쿼리로 추출하는 방법입니다.

{
    "filename":"test.png",
    "buttons":[
        {"name":"test_name","url":"test_url"},
        {"name":"test_name2","url":"test_url2"}
    ]
}

만약 이렇게 저장된 JSON 형식의 문자열 데이터가 있을 때,
"filename"의 값인 "test.png" 만 가져오는 것이 목표입니다.

예시 DBMS는 MySQL입니다.

추출 쿼리

MySQL 5.7 버전 이상 (MariaDB 10.3 이상)

MySQL 5.7 버전 이상부터 사용 가능하며, 내장 함수 중 하나인 JSON_EXTRACT()를 사용할 수 있습니다.
JSON 데이터가 저장된 컬럼에서 특정 JSON 키의 값을 추출할 수 있습니다.

JSON_EXTRACT(json_column, json_path)

• json_column: JSON 데이터가 저장된 컬럼 이름
• json_path: 추출할 JSON 키의 경로

-- filename 추출
SELECT JSON_EXTRACT(json_info, '$.filename') FROM TEST_TABLE;

-- buttons 추출
SELECT JSON_EXTRACT(json_info, '$.buttons') FROM TEST_TABLE;

-- buttons의 첫 번째 행 추출
SELECT JSON_EXTRACT(json_info, '$.buttons[0]') FROM TEST_TABLE;

-- buttons의 첫 번째 행의 name, url추출
SELECT JSON_EXTRACT(json_info, '$.buttons[0].name'), 
    JSON_EXTRACT(json_info, '$.buttons[0].url') 
FROM TEST_TABLE;

MySQL 5.7 버전 미만

저는 MySQL 버전이 낮아서 JSON_EXTRACT()를 사용할 수 없어서 어쩔 수 없이 노가다 쿼리를 작성했습니다. (다른 좋은 방법이 더 있는지는 모르겠습니다.)

그러나 근본적으로 JSON 데이터를 쿼리를 통해 키 값을 추출하는 것은 좋은 방법은 아닙니다. (저도 임시로 사용하기 위한 거라...)
만약 JSON 형식에 어긋나거나 내가 작성한 쿼리의 기준과 조금이라도 달라지면 원하는 결과를 제대로 얻을 수 없기 때문입니다.

-- filename 추출
SELECT *
FROM (
    SELECT 
      SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json_info, '"filename":"', -1), '"', 1), '"', 1) AS filename
    FROM TEST_TABLE
) a
WHERE filename IS NOT NULL AND LENGTH(filename) > 1

말 그대로 노가다입니다.
SUBSTRING_INDEX를 통해 내가 찾고자 하는 "filename" 을 찾고, 해당 키를 감싸고 있는 큰따옴표(") 2개를 찾습니다. 이렇게 하면 원하는 결과값을 추출할 수 있습니다.

-- buttons 추출
SELECT *
FROM (
    SELECT 
      SUBSTRING(json_info, 
            LOCATE('"buttons":', json_info) + LENGTH('"buttons":'), 
            LOCATE(']}', json_info) - LOCATE('"buttons":', json_info) - LENGTH('"buttons":') + 1) 
    AS buttons 
    FROM TEST_TABLE
) a
WHERE buttons IS NOT NULL AND buttons != '[]'

JSON 데이터 내의 배열을 추출하기 위해서는 더 복잡해집니다.
LOCATE로 문자열이 시작되는 위치를 찾아 그 사이에 있는 문자열을 SUBSTRING으로 추출했습니다.

-- buttons의 첫 번째 행 추출
SELECT *
FROM (
    SELECT 
      CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json_info, '"buttons":[', -1), ']', 1), '}', 1), '}')
    AS buttons_0 
    FROM TEST_TABLE
) a
WHERE buttons_0 IS NOT NULL AND buttons_0 != '}'

buttons의 배열의 시작과 끝인 괄호([,])를 찾고 배열 첫 번째 행의 끝 괄호인 }를 찾아 그 사이의 문자열을 가져옵니다.
가져오게 되면 {"name":"test_name","url":"test_url"이런 식으로 추출하기 때문에 CONCAT으로 괄호를 추가해서 형식을 맞춰줍니다.

-- buttons의 첫 번째 행의 name, url추출
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(buttons_0, '"name":"', -1), '"', 1), '"', 1) AS btnName,
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(buttons_0, '"url":"', -1), '"', 1), '"', 1) AS btnUrl
FROM (
    SELECT *
    FROM (
        SELECT 
          CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json_info, '"buttons":[', -1), ']', 1), '}', 1), '}')
        AS buttons_0 
        FROM TEST_TABLE
    ) a
    WHERE buttons_0 IS NOT NULL AND buttons_0 != '}'
) a

위에서 만들었던 쿼리들을 응용하면 배열의 키값도 가져올 수 있습니다.

참고자료

나의 노가다