1. JSON 데이터 타입 사용하기
MySQL Ver. 5.7.8부터 사용가능한 Json을 지원하는 데이터 타입은 JSON 이다.JSON 데이터 타입은 Json이 제공하는 모든 데이터 타입에 대한 저장이 가능하다. 유효성도 체크 가능하다.
다음과 같이 사용이 가능하다.
mysql> create table test ( data json);
mysql> insert into test values ( '{"id":1, "name":"jane"}');
mysql> select * from test;
+------------------------------------+
| data |
+------------------------------------+
| {"id":1, "name":"jane"} |
+------------------------------------+
만약, 정확하지 않은 Json 데이터를 입력하려고 하면 다음과 같은 에러가 발생한다.
mysql> insert test values ('test random');
ERROR 3130 (22032) : Invalid JSON text: "Invalid value" at position 2 in value ( or column) 'test random'.
좀 더 자세한 사용 방법은 다음과 같다.
-- JSON 타입의 컬럼은 max_allowed_packet 시스템 변수가 설정한 데이터 사이즈까지 저장이 가능하다. 만약, 현재 설정값 보다 더 큰 사이즈를 저장하고자 한다면 max_allowed_packet 시스템 변수값을 증가시켜야 한다.
-- JSON 타입의 컬럼은 기본 값을 설정할 수 없다.
-- JSON 타입의 컬럼은 해당 컬럼을 직접 사용해서는 인덱스를 생성할 수 없다. 생성하기 위해서는 Generated Column을 이용해야 한다.
-- JSON 타입 컬럼에 데이터를 입력/수정할때에는 문자열 데이터를 사용할 때와 동일하게 사용하면 된다. 즉, ''으로 묶어서 처리하면 된다.
-- 입력/수정 할때 유효성 체크를 하기 때문에 문법에 맞지 않으면 쿼리 실행 시 에러가 발생한다.
2. 함수 사용
2.1 JSON 데이터를 만드는데 사용하는 함수
JSON 데이터를 만들어 내는데에 사용하는 함수들에는 다음과 같은 함수들이 있다.
2.1.1 JSON_TYPE
JSON 데이터의 타입을 알려주는 함수이다. 이 함수의 결과값은 MySQL의 String 형태로 보여준다.
mysql> select json_type(capabilities), json_type(json_extract(capabilities, '$[0]')) from thermostat_model;
+------------------------+----------------------------------------------+
|json_type(capabilities) | json_type(json_extract(capabilities, '$[0]'))|
+------------------------+----------------------------------------------+
| ARRAY | STRING |
| ARRAY | OBJECT |
| ARRAY | STRING |
| ARRAY | STRING |
+------------------------+----------------------------------------------+
참고로 MySQL에서 제공하는 JSON 타입은 ECMA standard for JSON을 지원하는데, 현재 여기에서는 NULL, DECIMAL, UNSIGNED INTEGER, DOUBLE, STRING, OBJECT, ARRAY, BOOLEAN, DATE, TIME, DATETIME, TIMESTAMP, OPAQUE(raw bits)를 지원한다.
2.1.2 JSON_ARRAY
이 함수는 문자열을 JSON ARRAY 타입으로 변환해서 입력할 수 있게 해준다.
mysql> select json_array('programmable','fan','ac','furnace') json_array;
+-------------------------------------+
| json_array |
+-------------------------------------+
|["programmable","fan","ac","furnace"]|
+-------------------------------------+
위의 예제처럼 나온 출력 결과 값을 JSON 데이터 타입에 입력할 때 사용하 수 있다.
mysql> insert into test_model values ('xyzzy','["programmable","fan","ac","furnace"]');
2.1.3 JSON_OBJECT
이 함수를 사용하면 내가 입력하는 입력 내용을 key/value 형태의 JSON 데이터로 만들 수 있다.
mysql> select json_object ('device_id', 3001, 'unixtime',2222) object_json;
+------------------------------------+
| object_json |
+------------------------------------+
| {"unixtime":2222,"device_id":3001} |
+------------------------------------+
2.1.4 JSON_VALID
이 함수는 JSON 데이터에 대한 유효성을 체크해 주는 함수이다. 0 값이 리턴되면 유효하지 않다는 것을 의미한다.
mysql> select jsn_valid('{"capability":fan, "rpm": 3000, "voltage": 220}');
+---------------------------------------------------------------+
| jsn_valid('{"capability":fan, "rpm": 3000, "voltage": 220}') |
+---------------------------------------------------------------+
| 0|
+---------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select jsn_valid('{"capability": "fan", "rpm": 3000,"voltage": 220}');
+-----------------------------------------------------------------+
| jsn_valid('{"capability":"fan", "rpm": 3000, "voltage": 220}') |
+-----------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
2.2 JSON 데이터를 추출하는데 사용하는 함수
JSON 데이터를 추출하는데 사용하는 함수에는 다음과 같은 것들이 있다.
2.2.1 JSON_EXTRACT
JSON_EXTRACT 는 JSON 데이터를 추출하는데 사용하는 함수이다.
mysql> select json_extract(data, '$.name') from test;
+----------------------------+
|json_extract(data, '$.name')|
+----------------------------+
|"jane" |
|"joe" |
+----------------------------+
$는 Document의 root를 의미한다. 위의 예제처럼 깊이를 표현하여 Json 데이터를 내가 원하는 항목에 대한 것만 추출할 수 있다.
2.2.2 JSON_SEARCH
이 함수는 Document 안에 Scalar 데이터의 위치를 확인해 주는 함수이다. 현재, Json 데이터를 찾을 때 사용하고, 결과 값으로는 해당 데이터의 경로를 보여준다. 옵션 값으로는 one 또는 all을 사용할 수 있다. 찾으려고 하는 key 에 %나 _도 사용 가능하다.
mysql> select jsn_search(capabilities,'one', 'furnace') from thermostat_model;
+--------------------------------------------+
| jsn_search(capabilities, 'one','furnace') |
+--------------------------------------------+
| "$[1]" |
| "$[1]" |
| "$[3]" |
| "$[3]" |
+--------------------------------------------+
4 rows in set (0.00 sec)
2.3 JSON 데이터를 입력하는데 사용하는 함수
JSON 데이터를 입력하는데 사용하는 함수에는 다음과 같은 것들이 있다.
2.3.1 JSON_INSERT
Json 데이터를 추가하여 더 입력하고 싶을 때 사용하는 함수이다.
mysql> select * from test_reading;
+--------------------------------------------------+
| reading |
+--------------------------------------------------+
| {"unixtime": 2222, "device_id":3301} |
| {"unixtime": 3333, "device_id":3000} |
| {"on": true, "unixtime": 3334, "device_id":3001} |
+--------------------------------------------------+
mysql> update test_reading set reading = json_insert(reading, '$.on', cast('null' as json));
Query OK, 2 rows affected (0.05 sec)
Rows matched: 3 Changed: 2 Warning: 0
mysql> select * from test_reading;
+--------------------------------------------------+
| reading |
+--------------------------------------------------+
| {"on": null, "unixtime": 2222, "device_id":3301} |
| {"on": null, "unixtime": 3333, "device_id":3000} |
| {"on": true, "unixtime": 3334, "device_id":3001} |
+--------------------------------------------------+
위와 같이 입력하면, on 항목이 없던 json 데이터에 on 항목이 추가된다. 그 값은 json null로 들어간다. 여기서 null을 입력할 때 cast 함수를 사용했는데, 만약 사용하지 않았으면 에러가 났었을 것이다. 쿼리에서 처리되는 null과 Json 데이터의 null은 다르기 때문이다.
2.3.2 JSON_ARRAY_INSERT
Json 배열 타입의 데이터에 데이터를 입력하고 싶은 경우 사용한다.
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
+------------------------------------+
위와 같이 작성하면 @j Json 데이터에 Json 경로로 Root 의 첫번째 깊이의 두번째로 'x' 라는 데이터를 배열로 입력하라는 것을 의미한다.
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]] |
+-----------------------------------------+
위와 같이 작성하면 @j Json 데이터에 Json 경로로 Root 다음의 두번째 항목에 두번째 깊이의 첫번째로 'x'라는 데이터를 배열로 입력하라는 것을 의미한다.
2.3.2 JSON_REPLACE
이 함수는 현재 존재하는 Json 데이터의 값을 교체하고자 할때 사용한다.
mysql> select * from test_reading;
+-------------------------------------+
| reading |
+-------------------------------------+
| {"unixtime":3000, "device_id":3001} |
| {"unixtime":2999, "device_id":3000} |
| {"unixtime":3000, "device_id":3001} |
+-------------------------------------+
mysql> update test_reading set reading = json_replace ( reading, '$.unixtime', cast(json_extract(reading, '$.device_id') as decimal(5))-2);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from test_reading;
+-------------------------------------+
| reading |
+-------------------------------------+
| {"unixtime":2999, "device_id":3001} |
| {"unixtime":2998, "device_id":3000} |
| {"unixtime":2999, "device_id":3001} |
+-------------------------------------+
2.4 JSON 데이터를 삭제하는데 사용하는 함수
JSON 데이터를 삭제하는데 사용하는 함수에는 다음과 같은 것이 있다.
2.4.1 JSON_REMOVE
Json 데이터를 삭제하고자 할때 사용하는 함수이다. 다음과 같이 사용한다.
mysql> select * from test_reading;
+-------------------------------------------------+
| reading |
+-------------------------------------------------+
| {"on": null, "unixtime":2222, "device_id":3001} |
| {"on": null, "unixtime":3333, "device_id":3000} |
| {"on": true, "unixtime":3334, "device_id":3001} |
+-------------------------------------------------+
mysql> update test_reading set reading = json_remove(reading, '$.on');
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from test_reading;
+-------------------------------------+
| reading |
+-------------------------------------+
| {"unixtime":2222, "device_id":3001} |
| {"unixtime":3333, "device_id":3000} |
| {"unixtime":3334, "device_id":3001} |
+-------------------------------------+
2.5 JSON 데이터를 수정하는데 사용하는 함수
JSON 데이터를 수정하는데 사용하는 함수에는 다음과 같은 것들이 있다.
2.5.1 JSON_MERGE
mysql> select * from test_model;
+----------+-------------------------------------------+
| model_id | capabilities |
+----------+-------------------------------------------+
| abb | ["fan", "furnace", "smart_fan" ] |
| xyzzy | ["programmable", "fan", "ac", "furnace" ] |
+----------+-------------------------------------------+
mysql> update test_model set capabilities = json_merge ( capabilities, json_array('https','passwd')) where model_i='xyzzy';
Query OK, 1 row afected (0.00 sec)
Rows matched: 1 Changed: 1 Warning: 0
mysql> select * from test_model;
+----------+--------------------------------------------------------------+
| model_id | capabilities |
+----------+--------------------------------------------------------------+
| abb | ["fan", "furnace", "smart_fan" ] |
| xyzzy | ["programmable", "fan", "ac", "furnace", "https", "passwd" ] |
+----------+--------------------------------------------------------------+
2.5.2 JSON_SET
이 함수도 Json 데이터를 수정하고자 할 때 사용한다. 사용 방법은 다음과 같다.
mysql> select * from test_reading;
+-------------------------------------+
| reading |
+-------------------------------------+
| {"unixtime":2222, "device_id":3001} |
| {"unixtime":3333, "device_id":3000} |
| {"unixtime":3334, "device_id":3001} |
+-------------------------------------+
mysql> update test_reading set reading = json_set(reading, '$.unixtime', cast(json_extract(reading, '$.device_id') as decimal(5) -1);
Query OK, 3 rows affected ( 0.03 sec)
Rows matched: 3 Changed: 3 Warning: 0
mysql> select * from test_reading;
+-------------------------------------+
| reading |
+-------------------------------------+
| {"unixtime":3000, "device_id":3001} |
| {"unixtime":2999, "device_id":3000} |
| {"unixtime":3000, "device_id":3001} |
+-------------------------------------+
2.5.3 JSON_APPEND
이 함수를 이용하면 Json 데이터로 추가 항목을 입력할 수 있다. 다음과 같이 사용한다.
mysql> select * from test_model;
+----------+-------------------------------------------+
| model_id | capabilities |
+----------+-------------------------------------------+
| abb | ["fan", "furnace" ] |
| xyzzy | ["programmable", "fan", "ac", "furnace" ] |
+----------+-------------------------------------------+
mysql> update test_model set capabilities = json_append ( capabilities, '$', 'smart_fan') where model_i='abb';
Query OK, 1 row afected (0.03 sec)
Rows matched: 1 Changed: 1 Warning: 0
mysql> select * from test_model;
+----------+-------------------------------------------+
| model_id | capabilities |
+----------+-------------------------------------------+
| abb | ["fan", "furnace", "smart_fan" ] |
| xyzzy | ["programmable", "fan", "ac", "furnace" ] |
+----------+-------------------------------------------+
2.5.4 JSON_ARRAY_APPEND
이 함수는 배열 타입의 Json 데이터에 최적화 되어 배열 값을 입력할 수 있는 함수로서, 깊이가 얼마나 깊든 상관없이 추가가 가능하다.
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"] |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"] |
+-------------------------------------+
2.6 JSON 정보 수집
2.6.1 JSON_DEPTH
이 함수는 JSON 타입의 컬럼에 속성들의 깊이가 얼마나 깊게 만들어져 있는지 확인하고자 할때 사용한다.
mysql> select * from thermostat_reading;
+------------------------------------------------------------------------------------------------+
| reading |
+------------------------------------------------------------------------------------------------+
| {"on": null,"setting": 69, "unixtime": 1428462061,"device_id": 3001, "current_temp": 62.05} |
| {"on": null,"setting": 64, "unixtime": 1428483661,"device_id": 3001, "current_temp": 70.25} |
| {"on": null, "setting":68, "unixtime": 1428462061, "device_id": 3002,"current_temp": 61.05} |
| {"on": null,"setting": 62, "unixtime": 1428483661,"device_id": 3002, "current_temp": 71.25} |
| {"on": true,"setting": 69, "unixtime": 1428548461,"device_id": 3001, "current_temp": 62.05} |
| {"on": false,"setting": 64, "unixtime": 1428570061,"device_id": 3001, "current_temp": 75.25} |
| {"on": true,"setting": 68, "unixtime": 1428548461,"device_id": 3002, "current_temp": 61.05} |
| {"on": false,"setting": 62, "unixtime": 1428570061,"device_id": 3002, "current_temp": 76.25} |
+------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> select json_depth(reading) from thermostat_reading;
+--------------------+
| json_depth(reading) |
+--------------------+
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
+--------------------+
8 rows in set (0.00 sec)
이 경우에 scalar 값을 가진 JSON 오브젝트를 가진 JSON document라서 중첩된 것은 아니지만, 2로 표시되는 것이고, JSON scalar 값 자체 1 depth를 가졌다고 판단하고 처리된다.
2.6.2 JSON_KEYS
JSON_KEYS()를 사용하여 현재 일고 있는 컬럼에 내부적으로 생성된 키 정보를 확인할 수 있다. 이렇게 사용하면 현재 커럼에 포함된 Json 데이터의 Key로 지정되어있는 array 들의 내용을 확인하 수 있다.
mysql> select json_keys(reading) from thermostat_reading;
+------------------------------------------------------------+
| json_keys(reading) |
+------------------------------------------------------------+
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
| ["on", "setting","unixtime", "device_id", "current_temp"] |
+------------------------------------------------------------+
8 rows in set (0.00 sec)
2.6.3 JSON_LENGTH
이 함수는 JSON 타입의 컬럼에 얼마나 많은 종류의 속성들이 있는지 확인해 주는 함수로 다음과 같이 사용이 가능하다.
mysql> select * from thermostat_reading;
+------------------------------------------------------------------------------------------------+
| reading |
+------------------------------------------------------------------------------------------------+
| {"on": null,"setting": 69, "unixtime": 1428462061,"device_id": 3001, "current_temp": 62.05} |
| {"on": null,"setting": 64, "unixtime": 1428483661,"device_id": 3001, "current_temp": 70.25} |
| {"on": null, "setting":68, "unixtime": 1428462061, "device_id": 3002,"current_temp": 61.05} |
| {"on": null,"setting": 62, "unixtime": 1428483661,"device_id": 3002, "current_temp": 71.25} |
| {"on": true,"setting": 69, "unixtime": 1428548461,"device_id": 3001, "current_temp": 62.05} |
| {"on": false,"setting": 64, "unixtime": 1428570061,"device_id": 3001, "current_temp": 75.25} |
| {"on": true,"setting": 68, "unixtime": 1428548461,"device_id": 3002, "current_temp": 61.05} |
| {"on": false,"setting": 62, "unixtime": 1428570061,"device_id": 3002, "current_temp": 76.25} |
+------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> select json_length(reading) from thermostat_reading;
+---------------------+
| json_length(reading)|
+---------------------+
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
+---------------------+
8 rows in set (0.00 sec)
2.6.4 JSON_CONTAINS_PATH
이 함수는 현재 Json 타입의 컬럼에 저장된 document안에 특정한 element가 존재하는지 존재하지 않는지 확인할 수 있다.
mysql> select json_contains_path(reading,'all', '$.on') from thermostat_reading;
+-------------------------------------------+
| json_contains_path(reading, 'all','$.on') |
+-------------------------------------------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+-------------------------------------------+
8 rows in set (0.01 sec)
여기에 확인하려는 element는 여러개를 지정할 수가 있고, 이때 'all' 옵션을 사용하게 되면 둘다 element가 있는것인지 확인할 수 있다. 이럴떼 ‘all’ 옵션이 의미가 있다.
mysql> select json_contains_path(reading,'all', '$.spread', '$.on') from thermostat_reading;
+-------------------------------------------------------+
| json_contains_path(reading, 'all','$.spread', '$.on') |
+-------------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+-------------------------------------------------------+
2.6.5 JSON_CONTAINS
만약 Document 안에 특정 Element가 특정 값을 가지고 있는지 아닌지 확인하고 싶은경우 이 함수를 사용하면 된다.
mysql> select json_contains(reading,jsn_object('on', true)) from thermostat_reading;
+-----------------------------------------------+
| json_contains(reading, jsn_object('on',true)) |
+-----------------------------------------------+
| 0|
| 0|
| 0 |
| 0|
| 1|
| 0|
| 1|
| 0|
+-----------------------------------------------+
8 rows in set (0.00 sec)
해당 작업은 jsn_extract() 함수를 이용해서도 똑같이 진행할 수 있다.
mysql> select json_extract(reading,'$.on') = true from thermostat_reading;
+-------------------------------------+
| json_extract(reading, '$.on') = true |
+-------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 0 |
| 1 |
| 0 |
+-------------------------------------+
8 rows in set (0.00 sec)
무슨 방법을 사용할 지는 본인이 판단하는 것이다. 하지만, JSON_CONTAINS()는 또다른 장점을 가지고 있다. 바로, arrary 와 중첩 object도 처리할 수 있다는 것이다.
mysql> select * from thermostat_model;
+----------+------------------------------------------+
| model_id | capabilities |
+----------+------------------------------------------+
| abc123 | ["fan","furnace"] |
| xyzzy | ["programmable", "fan", "ac","furnace"] |
+----------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> select json_contains(capabilities, json_array('ac', 'fan')) from thermostat_model;
+----------------------------------------------------+
| json_contains(capabilities,json_array('ac', 'fan')) |
+----------------------------------------------------+
| 0 |
| 1 |
+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from thermostat_model;
+------------+---------------------------------------------------------------------------------------+
| model_id | capabilities |
+------------+---------------------------------------------------------------------------------------+
| abc123 | ["fan","furnace"] |
| Acme basic | [{"rpm": 2000,"voltage": 110, "capability": "fan"},"furnace"] |
| Acme super | ["programmable",{"rpm": 3000, "voltage": 220, "capability":"fan"}, "ac", "furnace"] |
| xyzzy | ["programmable","fan", "ac", "furnace"] |
+------------+---------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> select json_contains(capabilities,json_array('furnace', json_object('voltage', 110))) from thermostat_model;
+--------------------------------------------------------------------------------+
| json_contains(capabilities,json_array('furnace', json_object('voltage', 110))) |
+--------------------------------------------------------------------------------+
| 0 |
| 1 |
| 0 |
| 0 |
+---------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
2.7 Spatial GeoJSON 함수
MySQL Ver. 5.7 부터 Spatial GeoJson 다큐먼트에 대한 지원기능을 추가하였다. GeoJSON 은 Open Standard for encoding geometric/geographical feature로서 자세한 내용은 http://geojson.org에 작성되어있다. 여기서 지원하는 함수들은 GeoJSON 1.0 버젼에 맞춘 함수들이다.
2.7.1 ST_AsGeoJSON
이 함수는 geometry g로부터 GeoJSON 오브젝트를 생성하는 함수로 다음과 같이 사용한다.
ST_AsGeoJSON(g [, max_dec_digits [, options]])
예제는 다음과 같다.
mysql> SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(11.11111 12.22222)"),2);
+-------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText("POINT(11.11111 12.22222)"),2) |
+-------------------------------------------------------------+
| {"type":"Point","coordinates":[11.11,12.22]} |
+-------------------------------------------------------------+
오브젝트 문자열은 연결된 세션의 character set과 collation으로 만들어진다.
사용할 수 있는 옵션값은 4가지가 있다.
값 설명
0 아무런 옵션이 아니다. 기본값이다.
1 결과물에 bounding box를 추가한다.
2 결과물에 shot-format인 CRS URN을 추가한다. 기본 포맷은 EPSG:srid 이다.
4 결과물에 long-format인 CRS URN(urn:ogc:def:crs:EPSG:srid) 를 추가한다.
위 옵션들 외에 g와 옵션 사이에 max_dec_digits 라는 값을 줄 수도 있다. 사용하지 않으면 기본 값은 0으로 설정된다. 해당 값이 설정되면 해당 값에 따라 반올림이 이루어 진다.
2.7.2 ST_GeomFromGeoJSON
이 함수는 뮨자열로 표현된 GeoJSON을 파싱하여 geometry 를 만들어 준다. 해당 문자열 다음에 옵션들이 올 수 있다. 이 옵션은 2보다 높은 차원에 대한 좌표 제공시 어떻게 동작할 것인지를 나타낸다.
사용 방식은 다음과 같다.
ST_GeomFromGeoJSON(str [, options [, srid]])
사용할 수 있는 옵션 값은 다음과 같다.
값 설명
1 Document를 취소하고 에러를 발생한다. 이게 기본값이다. 명시하지 않으면 이 값으로 설정되어 사용된다.
2,3,4 Document를 받아들인다. 2보다 더 높은 차원에 대한 좌표 정보를 없앤다.
2,3,4값으로 설정하면, 현재는 똑같은 방식대로 동작한다. 2 이상의 값에 대한 것은 이후에 구현될 것이다. 현재는 값만 존재한다.
srid 값도 추가하여 나타 낼 수 있다. 이 값은 32-bit unsigned interger 이어야 하고, 주여지지 않으면 만들어지는 geometry는 SRID 4326 으로 만들어진다.
사용법은 다음과 같다.
mysql> SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
mysql> SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
+--------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@json)) |
+--------------------------------------+
| POINT(102 0) |
+--------------------------------------+
3. 인덱스 생성
우리는 MySQL Ver. 5.7 에서 제공하는 기능을 사용하여 Json 데이터를 기준으로 하는 인덱스를 생성할 수 있다. 이때 사용하는 MySQL 의 기능은 다음의 2가지 이다.
-- Generated Columns의 Virtual Column 기능
-- 함수 인덱스
가상 컬럼을 사용하는 함수 인덱스는 다음과 같은 특징을 가지고 있다.
-- MVCC 가능
-- 모든 isolation level 지원 가능
-- GAP lock 영향 받음
JSON 데이터를 사용하는 인덱스에 대한 특징은 다음과 같다.
-- PK는 가상 컬럼에 포함할 수 없다.
-- 가상 컬럼과 일반 컬럼을 혼합해서 인덱스를 구성할 수 없다.
사용 예제는 다음과 같다.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database json;
Query OK, 1 row affected (0.00 sec)
mysql> use json;
Database changed
mysql> create table employees(id bigint not null primary key auto_increment, info JSON);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into employees (info) values ('{ "name": "Matt Lord", "age": 38, "Duties": { "Product Manager": ["stuff", "more stuff"]} }');
Query OK, 1 row affected (0.01 sec)
mysql> select json_valid(info) from employees;
+------------------+
| json_valid(info) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> select id, json_extract(info,'$.name') from employees;
+----+------------------------------+
| id | json_extract(info, '$.name') |
+----+------------------------------+
| 1 | "Matt Lord" |
+----+------------------------------+
1 row in set (0.00 sec)
mysql> alter table employees add name varchar(100) generated always as (json_extract(info, '$.name')) virtual;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table employees add index (name);
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table employees \G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`info` json DEFAULT NULL,
`name` varchar(100) GENERATED ALWAYS AS (jsn_extract(info, '$.name')) VIRTUAL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain format=JSON select id, name from employees where name = "Matt Lord" \G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"name"
],
"key": "name",
"used_key_parts": [
"name"
],
"key_length": "103",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "128"
},
"used_columns": [
"id",
"info",
"name"
]
}
}
}
1 row in set, 1 warning (0.00 sec)
mysql>
'개발자를 위한 MySQL > 기타' 카테고리의 다른 글
| MySQL에서의 시간 정보 관리하기 (0) | 2016.03.17 |
|---|---|
| MySQL Ver.5.7 Generated Columns (0) | 2016.02.29 |
| MySQL for Excel (0) | 2014.03.05 |
