[mysql] mysql의 json 칼럼 업데이트

회사에서 사용하는 디비 일부에는 json 칼럼이 있다. 

json 칼럼 값들은 kotlin 으로 jpa, querydsl, jooq로 조작하면 뚝딱이지만

종종 리얼 디비 json 칼럼 값 수정 요청이 오곤 한다. 대부분의 경우 1개 row 만 변경하면 되는거여서 어찌저찌 해오거나

케이스가 몇개 없는 json array 칼럼의 경우는 케이스별로 그냥 다 만들어서 쿼리를 날리면서 지내왔다.

 

(~어쩌고 생략)

 

json array 값 업데이트 하기

칼럼 타입 : json

값 예시 : [ "RED", "ORANGE", "GREEN", "YELLOW"] .. etc 이렇게 들어가 있다.

전제 : 중복값은 없다

 

하고싶은것.

칼럼에 RED 값이 없으면 추가. 디비에 들어가 있는 순서는 중요 하지 않다.

UPDATE {테이블}
SET {칼럼} = JSON_ARRAY_APPEND({칼럼}, '$', 'RED')
WHERE JSON_SEARCH({칼럼}, 'all', 'RED') is null;

 

하고싶은것

칼럼에 GREEN 값이 있으면 삭제

UPDATE {테이블}
SET {칼럼} = JSON_REMOVE(
        {칼럼}, replace(JSON_SEARCH({칼럼}, 'all', 'GREEN'), '"', '')
    )
WHERE JSON_SEARCH({칼럼}, 'all', 'GREEN') > 1;

 

json 칼럼 값 안에 있는 ARRAY 값 업데이트 하기

칼럼 타입 : json

값 예시 :  { "a" : "abcd", "b" : ["RED", "ORANGE", "GREEN"], "c": 20 }

 

하고싶은것

b에 RED가 없으면 추가.

UPDATE {테이블} 
SET {칼럼} = JSON_ARRAY_APPEND({칼럼}, '$.b', 'RED')
WHERE {칼럼} is not null
  and JSON_SEARCH({칼럼}, 'all', 'RED', NULL, '$.b') is null;

 

하고싶은것

b에 GREEN이 있으면 삭제.

UPDATE {테이블}
SET {칼럼} = JSON_REMOVE({칼럼}, replace(JSON_SEARCH({칼럼}, 'all', 'GREEN', NULL, '$.b'), '"', ''))
WHERE {칼럼} is not null
  and JSON_SEARCH({칼럼}, 'all', 'GREEN', NULL, '$.b') > 1;

 

잘못 작성해서 값 날려먹은 쿼리 (다행히도 테스트 디비 + 쿼리날리기전에 엑셀로 해당 데이터 백업해둬서 살렸다 ^^...)

-

 

한번도 안해봐서 그런지 하루종일 쿼리 짜는데 시간 다썼다.

검색도 어떻게 해야할지 몰라서 한참을 해맸다.

 

그래도 하고 나니 이제 맘이 든든하다.

그리고 해당 테이블에 값이 몇개 없어서 이렇게 쿼리 날린거지

JSON_SEARCH가 조건절에 있으면 풀스캔 타서 데이터가 많다면 함부로 사용하기에는 부족한 쿼리 같긴하다.

 

참고

mysql 공식문서 로딩 속도 너무 느려서 기절할뻔 했다.

mysql JSON Function Reference : https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html