[mysql] 타입 변환, convert string(varchar) to json

컬럼의 타입이 varchar 이지만 실제 데이터는 json일 경우, 

json 내의 데이터 기준으로 조회 또는 업데이트 하는 쿼리를 기록해보려고 한다. 

 

예를 들어 아래와 같은 테이블이 있다고 가정을 해보자 

id	name	price	options
1	pencil	10	{"available":true,"color":[{"name":"red", "qty":100},{"name":"green","qty":10}]}
2	pen	12	{"available":fale,"color":[{"name":"black", "qty":100},{"name":"red","qty":10}]}
3	notepad	36	{"available":true,"color":[{"name":"red", "qty":100},{"name":"pink","qty":10},{"name":"green","qty":0}]}
4	sticker	40	{"available":true,"color":[{"name":"red", "qty":100},{"name":"green","qty":10}]}

 [products]

 

 

위의 products 테이블에서 options 내 데이터 기준으로 조회 또는 업데이트를 해보려고 한다. 

 

구매 가능한 상품을 조회하고 싶을 때, 아래와 같이 쿼리를 사용할 수 있다. 

-- json_extract 사용
select * from product 
where JSON_EXTRACT(options, '$.available') = true

-- path연산자(->) 사용
select * from product where options->"$.available" = true;

JSON_EXTRACT 또는 path 연산자인 '->'을 통해 JSON내 데이터를 조회할 수 있다.  

 

 

빨간 연필의 qty를 1로 수정하기 위해선 아래와 같이 쿼리를 날려야 한다. 

update product
set `options` = CAST(JSON_SET( `options`, '$.color[0].quantity', 1) as NCHAR)
where id = 1;

JSON_SET을 이용하여 property 추가도 할 수 있다.