Mysql JSON function 현업 프로젝트에서의 사용 예

by

안녕하세요, 오늘은 베스핀글로벌 이진협 님이 작성해 주신 ‘Mysql JSON function 현업 프로젝트에서의 사용 예’에 대해 알아보겠습니다.

1. mysql에서 data type이 text나 json 으로 되어있을 경우에 사용하면 좋다.

문자열에서 like 검색으로 사용하는 경우 abcde가 있는 걸 검색하는 경우 일반적으로 targetCondition like ‘%abcde%’로 검색하게 되는데 이 경우 test, testa, btest등, test가 포함된 모든 것을 찾는다. json 구조로 된 key를 찾거나 value를 찾거나 정상적인 json형태인지 찾을 수 있다.
SMP 프로젝트에서는 JSON 구조로 된 컬럼이 다수 존재하여 검색 등 데이터 가공 등, 사용할 일이 많이 있다.
JSON_EXTRACT, JSON_UNQUOTE, ->, ->>
{
    "mcc": {},
    "country": {},
    "triggerEvent": {
        "sendingDay": "Fri",
        "appEventFilter": "testdate",
        "eventSelection": "AE",
        "targetTimeUnit": "days",
        "targetTimeValue": 1,
        "sendingFrequency": "weekly",
        "test": "weekly", 
        "targetPeriodType": 0
    }
}
JSON_EXTRACT
  • LIKE 검색으로 weekly를 찾게되면 sendingFrequency, test 두개가 존재하여 원하는 검색을 할수 없다.
  • SELECT * FROM TEST_TABLE WHERE JSON_EXTRACT(targetCondition, '$.triggerEvent.sendingFrequency') LIKE %weekly%'; 이렇게 검색하는 경우 "sendingFrequency": "weekly" 를 찾을 수 있다.
  • 아직 LIKE가 쿼리에있는 이유는 JSON_EXTRACT(targetCondition, '$.triggerEvent.sendingFrequency') 를 실행하게되면 결과가 "weekly" 이렇게 나오기 때문이다. LIKE를 빼고 따옴표를 위해 JSON_UNQUOTE 사용할 수 있다.
JSON_UNQUOTE
  • SELECT * FROM AUTOMARKETING_PLAN WHERE JSON_UNQUOTE(JSON_EXTRACT(targetCondition, '$.triggerEvent.sendingFrequency')) ='weekly';쿼리가 길어서 보기 좋지 않다. -> 를 이용한 쿼리로 간단하게 표현할 수 있다.
->, ->> (JSON_EXTRACT)
  • SELECT * FROM AUTOMARKETING_PLAN WHERE targetCondition->'$.triggerEvent.sendingFrequency' LIKE '%weekly%';
  • 위와 같이 JSON_EXTRACT를 바꿀수 있다. 쿼리결과는 JSON_EXTRACT와 동일하다. ““따옴표가 포함되어 조회 되는 것을 확인 할 수 있다. JSON_UNQUOTE와 JSON_EXTRACT를 같이쓰는 방법인 ->>를 사용하여 따옴표가 제거된 결과를 조회 할 수 있다.
  • SELECT * FROM AUTOMARKETING_PLAN WHERE targetCondition->>'$.triggerEvent.sendingFrequency' = 'weekly';

2. JSON_SEARCH

["LIGHTBLUE","BLUE","DEEPPINK"]
  • SELECT * FROM MARKETING_META WHERE JSON_SEARCH(displayBgColor, 'one', 'BLUE') IS NOT NULL;JSON_SEARCH를 사용하면 찾고자 하는 위치를 찾을 수 있다. 결과는 "$[1]"

3. JSON_EXTRACT와 JSON_SEARCH를 이용한 복잡한 쿼리 예

{
    "button1": {
        "downloadUri": "http://www.naver.com",
        "referrer": {
            "basic": [],
            "specificUrl": [
                {
                    "filterLabel": "gmleetest10",
                    "variableName": "r_gmleetest10",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                },
                {
                    "filterLabel": "fqwef",
                    "variableName": "r_qwefqwefqwefqwef",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                }
            ],
            "downloadUrl": [
                {
                    "filterLabel": "gmleetest11",
                    "variableName": "r_gmleetest11",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                },
                {
                    "filterLabel": "gmtest1",
                    "variableName": "p_gmtest1",
                    "filterType": "appFilter",
                    "status": "LIVE"
                }
            ],
            "sendPush": false
        }
    },
    "button2": {
        "downloadUri": "http://www.daum.net",
        "referrer": {
            "basic": [],
            "specificUrl": [
                {
                    "filterLabel": "wqfwef",
                    "variableName": "r_qwefqwef",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                },
                {
                    "filterLabel": "gmleetest13",
                    "variableName": "r_gmleetest13",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                }
            ],
            "downloadUrl": [
                {
                    "filterLabel": "wefw",
                    "variableName": "r_efwef",
                    "filterType": "appReferrer",
                    "status": "LIVE"
                },
                {
                    "filterLabel": "aaaaaaaa",
                    "variableName": "p_aaaaaaaa",
                    "filterType": "appFilter",
                    "status": "LIVE"
                }
            ],
            "sendPush": false
        }
    },
    "button3": {
        "downloadUri": "http://www.naver.com",
        "referrer": {
            "basic": [],
            "specificUrl": [
                {
                    "filterLabel": "aaaaaaaa",
                    "variableName": "p_aaaaaaaa",
                    "filterType": "appFilter",
                    "status": "LIVE"
                },
                {
                    "filterLabel": "App version",
                    "variableName": "appVersion",
                    "filterType": "basicReferrer",
                    "status": "LIVE"
                }
            ],
            "downloadUrl": [
                {
                    "filterLabel": "MID",
                    "variableName": "mid",
                    "filterType": "basicReferrer",
                    "status": "LIVE"
                }
            ],
            "sendPush": true
        }
    }
}

4. 위 JSON 예시로 referrer.specificUrl배열 안에 있는 variableName의 value가 appVersion인 데이터가 있는지 확인하는 예제를 만들어보겠다.

a. specificUrl의 배열을 뽑아낸다.
SELECT * FROM TEMP_TEST WHERE JSON_EXTRACT(contents, '$.*.referrer.specificUrl[*].variableName') IS NOT null ;

위 쿼리 결과는 

["r_gmleetest10", "r_qwefqwefqwefqwef", "r_qwefqwef", "r_gmleetest13", "p_aaaaaaaa", "appVersion"]

이다.

b. 이 결과를 이용하여 JSON_SEARCH로 JSON_EXTRACT를 감싸고 appVersion를 찾는다.
SELECT * FROM TEMP_TEST WHERE JSON_SEARCH(JSON_EXTRACT(contents, '$.*.referrer.specificUrl[*].variableName'),'one','appVersion') IS NOT NULL ;

5. 위 쿼리에서 JSON_EXTRACT를 복합적으로 사용하는 JSON_FUNCTION에서는 ->나, ->> 를 사용할 수는 없다. (mysql 8 버전 기준)

6. JSON_CONTAINS을 사용할 수도 있다.

JSON_CONTAINS : 0과 1을 반환한다. 
SELECT * FROM TEMP_TEST WHERE JSON_CONTAINS(JSON_EXTRACT(contents, '$.*.referrer.specificUrl[*].variableName'), '"appVersion"', '$');

7. 그 외 JSON_FUNCTION

JSON_REPLACE
  • 프로젝트 내 쿼리에서는 사용 중인 쿼리는 없고, json 필드 안의 value를 마이그레이션하는경우 용이하게 사용하였다. 예) customFilters의 0번째 배열 안에 있는 filterLabel의 값을 ‘Marketing Opt-in’ 변경한다.
JSON_REPLACE(LastServicedCustomFilters, '$.customFilters[0].filterLabel', 'Marketing Opt-in')
  • 마이그레이션 사용 예 : triggerEvent의 eventSelection이 ‘OI’ 인 것을 찾아서 bagreementDate를 Array type으로 변경하고 첫 번째 배열에는 현재 값, 두 번째 배열에는 +1일이 되게 수정하라.
{

    "model": [],
    "deregi": "ALL",
    "device": [],

    "agreement": 1,
    "excludeModel": [],
    "optinSpecify": {
        "specify": false
    },
    "targetMethod": "AUTOM",
    "triggerEvent": {
        "sendingDay": "",
        "scheduleDelay": 0,
        "eventSelection": "OI",
        "targetTimeUnit": "days",
        "targetTimeValue": 1,
        "sendingFrequency": "daily",
        "targetPeriodType": 0
    },
    "excludeDevice": [],
    "allMncSelected": false,
    "bagreementDate": "2022-05-15 15:00:00",
    "excludeOsLevel": [],
    "allCountrySelected": true,
    "includeNewOsVersion": 0,
    "includeNewSdkVersion": 0,
    "excludeAllMncSelected": false,
    "excludeLowerSDKversion": false
}

a. 실행 쿼리

UPDATE TEMP_TEST
SET contents = JSON_REPLACE(targetCondition, '$.bagreementDate', JSON_ARRAY(JSON_EXTRACT(targetCondition, '$.bagreementDate'), DATE_ADD(DATE_FORMAT(JSON_UNQUOTE(JSON_EXTRACT(targetCondition, '$.bagreementDate')),'%Y-%m-%d %T'), INTERVAL 1 DAY )))
WHERE JSON_EXTRACT(targetCondition, '$.triggerEvent.eventSelection') in ('OI')

b. 결과 확인

    "triggerEvent": {
        "sendingDay": "",
        "scheduleDelay": 0,
        "eventSelection": "OI",
        "targetTimeUnit": "days",
        "targetTimeValue": 1,
        "sendingFrequency": "daily",
        "targetPeriodType": 0
    },
    "excludeDevice": [],
    "allMncSelected": false,
    "bagreementDate": [
        "2022-05-15 15:00:00",
        "2022-05-16 15:00:00"
    ],
    "excludeOsLevel": [],
    "allCountrySelected": true,
    "includeNewOsVersion": 0,
JSON_CONTAINS_PATH
  • json key가 가변인 경우 해당 키가 있는지 검색해 볼 수 있다.

예) ${customFilter}에 값을 가변적으로 지정한다.

JSON_CONTAINS_PATH(targetCondition, 'all', '$.custom.${customFilter}')=1
JSON_VALID
  • db에 insert될때 기본적으로 유효한 JSON이 들어가야 하겠지만 그렇지 않은 경우 유효한지 검증하는 함수다. SMP에서는 datatype을 text 컬럼에서 json 컬럼으로 변경한 컬럼이 있었다 이 경우 invalid한경우에 오류를 토해낸다. 그래서 JSON_VALID 를 이용해서 row를 찾고 valid한 json으로 변경 후 datatype을 json으로 변경하였다.
  • json 컬럼인 경우 datatype을 text로 하는 것 보다 json type으로 하는 게 insert가 안되기 때문에 파싱하다가 운영 중에 Exception발생하는 것을 미연에 방지 할 수 있다.
  • 사용 예) TEST_TEMP의 contents와 targetCondition 이 json구조가 invalid하면 STAT_CUSTOM에서 삭제하라
DELETE FROM STAT_CUSTOM WHERE MID IN (SELECT mid FROM TEST_TEMP WHERE (JSON_VALID(contents) = 0 OR JSON_VALID(targetCondition) = 0));

8. 프로젝트를 진행하면서 가장 많이 쓰는 것은

JSON_EXTRACT, JSON_SEARCH, JSON_CONTAINS_PATH, JSON_CONTAINS 이다. JSON_EXTRACT 만 잘 사용해도 좋을 것 같다. JSON_EXTRACT는 결과 값을 재사용 하는 경우에 사용하고, JSON_CONTAINS는 원하는 값이 있는지 찾을 경우, JSON_CONTAINS_PATH는 특정 key를 찾을 경우에 사용하면 좋다.

감사합니다~ 🙂

Leave a Comment