회사에서 사용하는 디비 일부에는 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
'개발 > 개발괴발' 카테고리의 다른 글
맥에서 파일 디렉토리 구조 json 형식으로 추출 후 엑셀파일로 만들기 (0) | 2024.05.08 |
---|---|
[HTTP] 그래서 PUT 이랑 PATCH 는 뭐가 다른건가요 - (2) (0) | 2022.10.05 |
[HTTP] 그래서 PUT 이랑 PATCH 는 뭐가 다른건가요 - (1) (0) | 2022.10.04 |
[JPA] 엔티티 값을 수정했는데 save를 안해도 되나요 (0) | 2022.09.27 |
모바일에서 로컬호스트 보기 (0) | 2021.10.31 |