json 데이터 조회 경험 (How to search JSON data in MySQL?)

02.08.2022 — MYSQL, 2022 — 2 min read

배경

매장에서 기부를 했던 기부자가 자신의 기부 내역을 찾을 수 없다는 문의가 왔다 기부자는 2022년 **매장에서 기부를 총 2번 했다 1번은 물품등록부터 기부영수증 신청까지 완료했지만 다른 기부내역은 없었다 기부날짜,방문매장코드,등록자를 바탕으로 검색을 해봤다 해당 날짜에 방문 매장에 데이터를 봤지만 없었다 최근에 매장에서 자신의 매장이 아닌 다른 매장이 보인다는 오류가 있었다 등록자는 같지만 매장이 다른 기부건을 찾아보고 싶었다

JSON 데이터를 어떻게 조회하지?

현재 db 에는 등록자 컬럼(created)을 json 으로 관리한다

id	date	created
1	20220706	`{"id": 9, "name": "A", "role": "매니저", "datetime": "2022-07-06 17:40:05"}`
2	20220727	`{"id": 20, "name": "B", "role": "매니저", "datetime": "2022-07-27 13:27:29"}`
3	20220727	`{"id": 5, "name": "C", "role": "매니저", "datetime": "2022-07-27 11:45:32"}`

json 의 특정 key 값을 출력하기

SELECT id, date ,JSON_EXTRACT(created, '$.id') as created
FROM donation
WHERE JSON_EXTRACT(created, '$.id') = 9

id	date	created
1	20220706	9

json 전체를 출력하기

SELECT id, date ,created
FROM donation
WHERE JSON_EXTRACT(created, '$.id') = 9

id	date	created
1	20220706	9

20230109

mysql 에서 json data 안의 key 쉽게 찾기 위해 json path 표현식을 사용하는 것을 알게되었다.

Json Path

SELECT * FROM donation WHERE created->'$.id' = 1; created->'$.id' 와 json_extract(created, '$.id') 는 동일하다

참고 자료

https://www.joinc.co.kr/w/man/12/mysql/json https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql