openLayers의 좌표를 저장하기 위해 DB에 Polygon의 좌표를 저장하니 type과 coordinates로 나뉜 JSON 형식으로 저장되었다. JSON 형식으로 저장된 데이터를 쉽게 가져오기 위한 방법을 MySQL에서 제공할것같아 찾아보았다.
일단 테스트용 JSON 데이터 형식을 만들기 위해 JSON_ARRAY()를 사용했다. JSON_ARRAY()는 함수 안에 작성한 파라미터들을 리스트 형식으로 묶어서 리턴한다.
SELECT JSON_ARRAY('a', 1, NOW()) AS test;
일반 딕셔너리 형식으로 출력하기 위해선 JSON_OBJECT()함수를 사용한다. 입력한 파라미터 순서대로 키, 밸류값을 가진다. 키와 밸류의 개수가 안맞을 경우 에러가 발생한다.
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc') AS test;
리스트와 딕셔너리가 혼합된 형식을 하나의 리스트로 출력하고 싶은 경우 JSON_MERGE() 함수를 사용한다.
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}') AS test;
workbench에서는 결과값을 오른쪽클릭 시 나오는 'Open Value in Viewer' 에서 JSON 형식을 정리된 데이터를 확인할 수 있다.
문자열로 입력한 값이 딕셔너리 형태로 저장된 것을 확인할 수 있다.
위에 테스트용으로 만들었던 값들은 출력을 해보면 문자열로 출력된다. MySQL에서는 JSON으로 저장된 데이터를 쉽게 접근하는 방법을 제공한다.
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;
최종 출력은 문자열이므로 [[0, 1], [1, 1], [1, 0]] 또는 [0, 1] 처럼 출력하면 문자열 파싱의 과정이 추가로 필요하다.