[Mysql] JSON ARRAY의 모든 값을 변경시켜 UPDATE하기

 

회사에서 솔루션을 몇년전에 납품했고 디비 업데이트를 해야하는 상황이었다.

 

그동안 기존 버전과의 호환성을 위해 조심조심해서 백엔드와 디비 데이터 형식을 최소한으로 변경해왔지만

불가피하게 프로퍼티가 추가가 되었고, 기존 버전의 솔루션은 추가된 프로퍼티가 있는 모델을 사용하지 않고 있어 org.apache.ibatis.executor.result.ResultMapException 가 나는 상황...

 

어차피 솔루션 업데이트용 디비 테이블을 추가로 만든 상황이라 기존 솔루션이 해석할수있는 형태로 데이터를 전부 바꿔줘야했다.

//기존 형식
[{"a":"string1", "b":"string2"},{"a":"string1", "b":"string2"}]


//최근 형식 (프로퍼티가 c가 추가되었다)
[{"a":"string1", "b":"string2", "c":"string3"},{"a":"string1", "b":"string2", "c":"string3"}]

옛날 버전의 솔루션이 아래처럼 되어있는 컬럼을 읽다가 c를 맵핑하지 못해 Exception이 발생.

 

각 JSON Array 형태를 테이블로 만들어 필요한 프로퍼티만 추가해서 JSON Object를 만들고, 그 Object들을 Aggregation 하여 JSON Array로 다시 생성해 테이블을 업데이트 하는 쿼리이다.

UPDATE
	MY_TABLE
SET
	MY_COLUMN = (
		SELECT
			JSON_ARRAYAGG(JSON_OBJECT("a", a, "b", b))
		FROM
			JSON_TABLE ( MY_COLUMN ,
			'$[*]' COLUMNS( a VARCHAR(200) PATH '$.a',
			b VARCHAR(200) PATH '$.b' )) x);

 

필요에 따라서, 특정컬럼을 삭제하거나 변경할 수 있을 것 같다.