[MySQL] JSON 데이터 조회

유콩·2021년 11월 15일

openLayers의 좌표를 저장하기 위해 DB에 Polygon의 좌표를 저장하니 type과 coordinates로 나뉜 JSON 형식으로 저장되었다. JSON 형식으로 저장된 데이터를 쉽게 가져오기 위한 방법을 MySQL에서 제공할것같아 찾아보았다.

🐬 JSON 형식의 데이터 생성

JSON_ARRAY(val1, val2, ...)

일단 테스트용 JSON 데이터 형식을 만들기 위해 JSON_ARRAY()를 사용했다. JSON_ARRAY()는 함수 안에 작성한 파라미터들을 리스트 형식으로 묶어서 리턴한다.

SELECT JSON_ARRAY('a', 1, NOW()) AS test;

JSON_OBJECT(key1, val1, key2, val2, ...)

일반 딕셔너리 형식으로 출력하기 위해선 JSON_OBJECT()함수를 사용한다. 입력한 파라미터 순서대로 키, 밸류값을 가진다. 키와 밸류의 개수가 안맞을 경우 에러가 발생한다.

SELECT JSON_OBJECT('key1', 1, 'key2', 'abc') AS test;

JSON_MERGE(val1, val2, ...)

리스트와 딕셔너리가 혼합된 형식을 하나의 리스트로 출력하고 싶은 경우 JSON_MERGE() 함수를 사용한다.

SELECT JSON_MERGE('["a", 1]', '{"key": "value"}') AS test;

workbench에서는 결과값을 오른쪽클릭 시 나오는 'Open Value in Viewer' 에서 JSON 형식을 정리된 데이터를 확인할 수 있다.

문자열로 입력한 값이 딕셔너리 형태로 저장된 것을 확인할 수 있다.

🐬 JSON 형식의 데이터 조회

위에 테스트용으로 만들었던 값들은 출력을 해보면 문자열로 출력된다. MySQL에서는 JSON으로 저장된 데이터를 쉽게 접근하는 방법을 제공한다.

JSON_EXTRACT(json_data, '$.조회할값')

DB에 저장한 값을 조회해와 JSON_EXTRACT의 첫번째 파라미터로 넣고, 해당 JSON 데이터를 $ 변수에 대입하여 원하는 값을 조회한다.

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.id') AS id, 
JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') AS `name`;

위의 예제는 '{"id": 14, "name": "Aztalan"}'의 데이터가 $ 변수에 대입된다. $ 변수 내의 키값인 id와 name으로 밸류값을 가져온다.

Polygon 좌표가 저장된 geometry 값을 재현해보았다. 딕셔너리 형태는 위의 방법대로 키를 이용하여 조회하고, 리스트 형태는 기존 리스트처럼 인덱스로 값을 조회한다.

SET @test = '{"type": "Polygon", "coordinates": [[0, 1], [1, 1], [1, 0]]}';
SELECT JSON_EXTRACT(@test, '$.type') AS `type`;
SELECT JSON_EXTRACT(@test, '$.coordinates') AS `coordinates`;
SELECT JSON_EXTRACT(@test, '$.coordinates[0]') AS coordinates_1;
SELECT JSON_EXTRACT(@test, '$.coordinates[0][0]') AS coordinates_1_x;