MySQL5.7で実装されたJSONデータ型とJSON関数のレビューになります。
ご利用になる場合は、参考にして頂ければと思います。

ネイティブJSONデータ型 (バイナリ形式)
Insert時のJSON構文バリデーション機能
組み込みJSON関数 (保存、検索、更新、操作)
ドキュメントにインデックス設定し高速アクセス
SQLとの統合を容易にする、新しいインライン構文
utf8mb4の文字セットとutf8mb4_binの照合 「🐬」
サイズはmax_allowed_packetの値で制限 (Default:4MB)

MySQL5.7からは、リレーショナル、スキーマレスを同じ技術スタックで利用可能になっています。

13.16.1 JSON Function Reference
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

参考: Modifying JSON Values in MySQL 5.7
https://planet.mysql.com/entry/?id=5994648

JSONで表現する全てのデータ型をサポート

数値, 文字列, bool(true,false)
オブジェクト {“キー”: “値”}, 配列 [123456, “String”, …]
null
日付(date), 時刻, 日付(datetime), タイムスタンプ, その他


[CONFIRM]> show create table T_JSON_SUPPORT\G
*************************** 1. row ***************************
       Table: T_JSON_SUPPORT
Create Table: CREATE TABLE `T_JSON_SUPPORT` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`body` json DEFAULT NULL,
`type` varchar(20) GENERATED ALWAYS AS (json_type(`body`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4


[CONFIRM]> select * from T_JSON_SUPPORT;
+----+---------------------------------------------+----------+
| id | body                                        | type     |
+----+---------------------------------------------+----------+
|  1 | 123456789                                   | INTEGER  |
|  2 | NULL                                        | NULL     |
|  3 | true                                        | BOOLEAN  |
|  4 | "abcde"                                     | STRING   |
|  5 | {"id": 5, "name": "オブジェクト"}            | OBJECT   |
|  6 | [-122.42200352825247, 37.80848009696725, 0] | ARRAY    |
|  7 | "2016-02-29"                                | DATE     |
|  8 | "2016-02-29 00:00:00.000000"                | DATETIME |
+----+---------------------------------------------+----------+

データ型と照合順
4byteなので絵文字を格納可能、またutf8mb4_binなので大文字と小文字を区別します。


 [NEW57]> SET @j = JSON_OBJECT('key', 'value');
1 row in set (0.00 sec)

 [NEW57]> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+
1 row in set (0.00 sec)

 [NEW57]> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+
1 row in set (0.00 sec)

JSONドキュメントと生成列を利用して列を作成し、対象列にINDEXを付けて高速な検索を行う事が可能です
生成列はファンクションインデックスとして利用可能

こちらは、生成列を利用したテーブルの作成例になります。
string1とstring2にデータをINSERTする事で、string1_w_string、string2_w_string、compareのデータは自動生成されます。


[CONFIRM]> show create table T_Character_COLLATE_utf8mb4_bin\G
*************************** 1. row ***************************
       Table: T_Character_COLLATE_utf8mb4_bin
Create Table: CREATE TABLE `T_Character_COLLATE_utf8mb4_bin` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string1` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string2` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `string1_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
  `string2_w_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
  `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','a');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('あ','ぁ');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> insert into T_Character_COLLATE_utf8mb4_bin(string1,string2) values('A','A');
Query OK, 1 row affected (0.00 sec)

 [CONFIRM]> select * from T_Character_COLLATE_utf8mb4_bin;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 000041           | 000061           | 0       |
|   2 | あ      | ぁ      | 003042           | 003041           | 0       |
|   3 | A       | A       | 000041           | 000041           | 1       |
+-----+---------+---------+------------------+------------------+---------+

JSONデータと生成列を利用すると、特定のJSON識別子からデータを抜き出して列を作成し、その列にインデックスを付ける事が可能
例えば、JSONデータをINSERTしてJSONドキュメントにUSER_IDやPRODUCT_IDがあれば、その項目だけを抜き出して列にしてINDEXを付与。
検索する場合は、それらの値を利用して検索するとJSONドキュメントも高速検索が可能になります。


[NEW57]> CREATE TABLE `T_JSON` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,‘$.properties.STREET’)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


[NEW57]> alter table features add index idx_feature_type(`feature_type`);
Query OK, 0 rows affected (6.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

[NEW57]> explain select distinct(feature_type) from features;
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys    | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | features | NULL       | index | idx_feature_type | idx_feature_type | 123     | NULL | 199013 |   100.00 | Using index |
+----+-------------+----------+------------+-------+------------------+------------------+---------+------+--------+----------+-------------+

【JSON関数の使い方】

(1)JSONドキュメントの情報取得


[NEW57]> SELECT JSON_VALID(body) from T_JSON_DOC where id = 5;
+------------------+
| JSON_VALID(body) |
+------------------+
|                1 |
+------------------+

[NEW57]> SELECT JSON_TYPE(body) from T_JSON_DOC where id = 5;
+-----------------+
| JSON_TYPE(body) |
+-----------------+
| OBJECT          |
+-----------------+

[NEW57]> SELECT JSON_KEYS(body) from T_JSON_DOC where id = 5;
+---------------------------------------+
| JSON_KEYS(body)                       |
+---------------------------------------+
| ["id", "name", "price", "Conditions"] |
+---------------------------------------+


[NEW57]> SELECT JSON_SEARCH(feature,'one','MARKET') AS extract_path FROM features WHERE id = 121254;
+-----------------------+
| extract_path          |
+-----------------------+
| "$.properties.STREET" |
+-----------------------+

(2)通常のリレーショナルテーブルから,JSONドキュメントを作成する
Object{}とArray[]関数を利用


/*** リレーショナルテーブル ***/
[NEW57]> SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+

/*** リレーショナルテーブルからJSONデータを作成 ({})***/
[NEW57]> SELECT JSON_OBJECT('CITY',NAME,'Country',CountryCode) from world.City where CountryCode ='JPN' limit 1;
+------------------------------------------------+
| JSON_OBJECT('CITY',NAME,'Country',CountryCode) |
+------------------------------------------------+
| {"CITY": "Tokyo", "Country": "JPN"}            |
+------------------------------------------------+

/*** VIEWにしておくと呼び出しがより楽になります ***/
[NEW57]> CREATE VIEW v_City_json AS
    -> SELECT JSON_OBJECT('ID', ID, 'name', Name, 'CountryCode', CountryCode, 'District', District,'Population',Population) as doc FROM City where CountryCode = 'JPN';
Query OK, 0 rows affected (0.01 sec)

[NEW57]> select * from v_City_json limit 1\G
*************************** 1. row ***************************
doc: {"ID": 1532, "name": "Tokyo", "District": "Tokyo-to", "Population": 7980230, "CountryCode": "JPN"}
1 row in set (0.01 sec)


/*** リレーショナルテーブル ***/
[NEW57]>SELECT NAME,CountryCode from world.City where CountryCode ='JPN' limit 1;
+-------+-------------+
| NAME  | CountryCode |
+-------+-------------+
| Tokyo | JPN         |
+-------+-------------+


/*** リレーショナルテーブルからJSONデータを作成 ([])***/
[NEW57]> SELECT JSON_ARRAY(NAME,CountryCode)  from world.City where CountryCode ='JPN' limit 1;
+------------------------------+
| JSON_ARRAY(NAME,CountryCode) |
+------------------------------+
| ["Tokyo", "JPN"]             |
+------------------------------+


/*** リレーショナルテーブル***/
[NEW57]> select * from T_JSON_PLACE;
+----+-------------------------------------+----------+-----------+
| id | place                               | latitude | longitude |
+----+-------------------------------------+----------+-----------+
|  1 | 東京都港区北青山2-5-8                | 35.67125 | 139.71864 |
|  2 | 大阪府大阪市北区堂2-4-27            | 34.69584 | 135.49291 |
+----+-------------------------------------+----------+-----------+


/*** リレーショナルテーブルからJSONデータを作成 ({},[])***/
[NEW57]> select JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude)) from T_JSON_PLACE;
+---------------------------------------------------------------------------------------------------+
| JSON_OBJECT('ID',id,'住所',place,'緯度経度', JSON_ARRAY(latitude,longitude))                      |
+---------------------------------------------------------------------------------------------------+
| {"ID": 1, "住所": "東京都港区北青山2-5-8", "緯度経度": [35.67125, 139.71864]}                     |
| {"ID": 2, "住所": "大阪府大阪市北区堂2-4-27", "緯度経度": [34.69584, 135.49291]}                 |
+---------------------------------------------------------------------------------------------------+

メモ: ST_AsGeoJSONやST_GeomFromGeoJSON等のSpatial GeoJSON Functionsで空間を表すJSONも利用する事が可能

JSONドキュメントの更新処理


/**** JSON_REMOVE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+---------------------------------------------------------------------------------------------------+------------+
| id | body                                                                                              | price_only |
+----+---------------------------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}             |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}                 |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 17131, "Conditions": ["NEW", 2015]}                          |   17131.00 |
|  4 | {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}                     |  500000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |   25000.00 |
+----+---------------------------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REMOVE(body,"$.Quantity") from T_JSON_DOC where id = 5;
+-----------------------------------------------------------------------------+
| JSON_REMOVE(body,"$.Quantity")                                              |
+-----------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REMOVE(body,"$.Quantity") where id = 5;



/**** JSON_SET (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_SET(body,'$.name',"オートバイ", '$.price',500000) from T_JSON_DOC where id = 4;
+------------------------------------------------------------------------------------+
| JSON_SET(body,'$.name',"オートバイ", '$.price',500000)                             |
+------------------------------------------------------------------------------------+
| {"id": 4, "name": "オートバイ", "price": 500000, "Conditions": ["NEW", 2015]}      |
+------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_SET(body,'$.name',"オートバイ", '$.price',500000) where id = 4;


/**** JSON_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_INSERT(body,'$.Quantity','[1,10]') from NEW57.T_JSON_DOC where id = 5;
+---------------------------------------------------------------------------------------------------+
| JSON_INSERT(body,'$.Quantity','[1,10]')                                                           |
+---------------------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Quantity": "[1,10]", "Conditions": ["NEW", 2015]}    |
+---------------------------------------------------------------------------------------------------+

[NEW57]> update NEW57.T_JSON_DOC set body = JSON_INSERT(body,'$.Quantity','[1,10]') where id = 5;


/**** JSON_REPLACE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) from T_JSON_DOC where id = 3;
+-----------------------------------------------------------------------------+
| JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000)))                 |
+-----------------------------------------------------------------------------+
| {"id": 3, "name": "冷蔵庫", "price": 18359, "Conditions": ["NEW", 2015]}    |
+-----------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_REPLACE(body,"$.price",FLOOR(10000 + (RAND() * 9000))) where id = 3;


/**** JSON_ARRAY_INSERT (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]>SELECT JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') from T_JSON_DOC where id = 5;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(body,'$.Conditions[2]','January')                                    |
+----------------------------------------------------------------------------------------+
| {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015, "January"]}    |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_INSERT(body,'$.Conditions[2]','January') where id = 5;



/**** JSON_ARRAY_APPEND (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> select JSON_ARRAY_APPEND(body,'$.Conditions','故障') from NEW57.T_JSON_DOC where id = 2;
+----------------------------------------------------------------------------------------+
| JSON_ARRAY_APPEND(body,'$.Conditions','故障')                                          |
+----------------------------------------------------------------------------------------+
| {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED", 2013, "故障"]}      |
+----------------------------------------------------------------------------------------+

[NEW57]> update T_JSON_DOC set body = JSON_ARRAY_APPEND(body,'$.Conditions','故障') where id = 2;


/**** JSON_MERGE (SELECTでの確認とUPDATEによる更新)****/

[NEW57]> select * from T_JSON_DOC;
+----+------------------------------------------------------------------------------+------------+
| id | body                                                                         | price_only |
+----+------------------------------------------------------------------------------+------------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}     |   10000.00 |
|  2 | {"id": 2, "name": "テレビ", "price": 30000, "Conditions": ["USED",2013]}     |   30000.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 11154, "Conditions": ["NEW", 2015]}     |   11154.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}     |   50000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}     |   25000.00 |
+----+------------------------------------------------------------------------------+------------+

[NEW57]> SELECT JSON_MERGE(body,'{"Conditions":"Excellent"}') from T_JSON_DOC where id = 1;
+------------------------------------------------------------------------------------------+
| JSON_MERGE(body,'{"Conditions":"Excellent"}')                                            |
+------------------------------------------------------------------------------------------+
| {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}    |
+------------------------------------------------------------------------------------------+


[NEW57]> update T_JSON_DOC set body = JSON_MERGE(body,'{"Conditions":"Excellent"}') where id = 1;

補足情報
json_extractと->は同等

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL

メモ:
-> /* JSON_EXTRACT() */
->> /* JSON_UNQUOTE(JSON_EXTRACT()) */

必要に応じてjson_unquoteでQuoteを外して列を作成

`feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(feature->"$.type")) VIRTUAL
`feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL

[NEW57]> select feature_type,feature_street from T_JSON limit 10,5;
+--------------+----------------+
| feature_type | feature_street |
+--------------+----------------+
| Feature      | "JEFFERSON"    |
| Feature      | "TAYLOR"       |
| Feature      | "BEACH"        |
| Feature      | "BEACH"        |
| Feature      | "JEFFERSON"    |
+--------------+----------------+

生成列のVirtualとStoredの違いはありますが、Virtualであれば作成時も参照、更新もオンライン処理が可能。
但し、参照時にCPUを利用するのでStoredを選択するかは状況により使い分け。
Storedは実データを含む為、高速だが追加でディスク容量が必用。また、作成時は参照のみが可能になります。

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE T_ONLINE ALGORITHM=INPLACE, ADD feature_type varchar(30) AS (feature->"$.type") STORED;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

ALTER TABLE T_ONLINE ADD feature_type varchar(30) AS (feature->"$.type") STORED;
Query OK, 206560 rows affected (6.17 sec)
Records: 206560  Duplicates: 0  Warnings: 0



先日は、mysqlxプラグインをMySQL Shellから処理して基本動作確認しました。
mysqlxとMySQL Shell初期設定と基本動作確認
本日は、mysqlxに対応しているConnector/Node.jsからMySQLにJSONデータを処理を確認してみました。
基本的には、JohannesさんがMySQL Server Blogに書いた記事をベースに検証しています。
http://mysqlserverteam.com/mysql-5-7-12-part-5-connectornode-js/

【mysqlxとConnector/Node.jsを利用する為の前提条件】
MySQL 5.7.12 or higher, with the X plugin enabled
Node.JS 4.2

Download Connector/Node.js
~概要~
Connector/Node.js is the official Node.js driver for MySQL.
MySQL Connector/Node.js is an native asychronous promise-based client library for the of
MySQL 5.7.12+ providing New CRUD APIs for Document and Relational development

http://dev.mysql.com/downloads/connector/nodejs/

設定前の各種バージョン確認


[root@misc01 nodejs]# node -v
v4.4.3
[root@misc01 nodejs]# npm -v
2.15.1
[root@misc01 nodejs]# 

admin@192.168.56.113 [NEW57]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.12-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [NEW57]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE_VERSION from information_schema.plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+---------------+---------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE_VERSION |
+-------------+----------------+---------------+---------------------+
| mysqlx      | 1.0            | ACTIVE        | 50712.0             |
+-------------+----------------+---------------+---------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [NEW57]> 

ダウンロード
node.jsもMySQLもコネクター対応している事が確認出来たので、コネクターをダウンロードしてインストールしてみます。


[root@misc01 nodejs]# wget http://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
--2016-04-18 11:00:12--  http://dev.mysql.com/get/Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
dev.mysql.com (dev.mysql.com) をDNSに問いあわせています... 137.254.60.11
dev.mysql.com (dev.mysql.com)|137.254.60.11|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://cdn.mysql.com//Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz [続く]
--2016-04-18 11:00:18--  http://cdn.mysql.com//Downloads/Connector-Nodejs/mysql-connector-nodejs-1.0.2.tar.gz
cdn.mysql.com (cdn.mysql.com) をDNSに問いあわせています... 104.78.21.123
cdn.mysql.com (cdn.mysql.com)|104.78.21.123|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 121705 (119K) [application/x-tar-gz]
`mysql-connector-nodejs-1.0.2.tar.gz' に保存中
100%[==============================================================================================>] 121,705      157KB/s 時間 0.8s   
2016-04-18 11:00:19 (157 KB/s) - `mysql-connector-nodejs-1.0.2.tar.gz' へ保存完了 [121705/121705]
[root@misc01 nodejs]# 

npmを使用してインストール
http://dev.mysql.com/doc/dev/connector-nodejs/
http://dev.mysql.com/doc/dev/connector-nodejs/tutorial-Getting_Started.html


[root@misc01 nodejs]# npm install mysql-connector-nodejs-1.0.2.tar.gz
mysqlx@1.0.2 node_modules/mysqlx
[root@misc01 nodejs]# 

シェルのサンプルを利用した基本動作確認

[root@misc01 nodejs]# cat sample_node_X_API.js 
const mysqlx = require('mysqlx');

mysqlx.getSession({
    host: 'localhost',
    port: 33060,
    dbUser: 'demo_user',
    dbPassword: 'password'
}).then(function (session) {
    return session.createSchema("test_schema").then(function (schema) {
        return schema.createCollection("myCollection");
    }).then(function (collection) {
        return Promise.all([
            collection.add(
                {baz: { foo: "bar"}},
                {foo: { bar: "baz"}}
            ).execute(),
            collection.find("$.baz.foo == 'bar'").execute(function (row) {
                console.log("Row: %j", row);
            }).then(function (res) {
                console.log("Collection find done!");
            }),
            collection.remove("($.foo.bar) == 'baz'").execute().then(function () {
                console.log("Document deleted");
            }),
            collection.drop()
        ]);
    }).then(function () {
        return session.dropSchema("test_schema");
    }).then(function () {
        return session.close();
    });
}).catch(function (err) {
    console.log(err.stack);
    process.exit();
});

上記スクリプトを実行


[root@misc01 nodejs]# node sample_X_API.js 
Row: {"_id":"d0ef5b1d-86e6-34b2-5794-3ebcab02","baz":{"foo":"bar"}}
Collection find done!
Document deleted
[root@misc01 nodejs]# 

実行すると、以下のオブジェクトとデータがmysqlx経由(33060)でjavascriptから登録されている

admin@192.168.56.113 [test_schema]> show tables;
+-----------------------+
| Tables_in_test_schema |
+-----------------------+
| myCollection          |
+-----------------------+
1 row in set (0.01 sec)

admin@192.168.56.113 [test_schema]> select * from myCollection;
+--------------------------------------------------------------------+----------------------------------+
| doc                                                                | _id                              |
+--------------------------------------------------------------------+----------------------------------+
| {"_id": "21dddfdd-2d4e-3cf1-fc5f-05a0e9ea", "foo": {"bar": "baz"}} | 21dddfdd-2d4e-3cf1-fc5f-05a0e9ea |
| {"_id": "7a7a7497-95c8-b7e7-fa15-064a8aba", "baz": {"foo": "bar"}} | 7a7a7497-95c8-b7e7-fa15-064a8aba |
+--------------------------------------------------------------------+----------------------------------+
2 rows in set (0.00 sec)

admin@192.168.56.113 [test_schema]> 

MySQLXのClassは以下のページを参照してください。
http://dev.mysql.com/doc/dev/connector-nodejs/NodeSession.html

その他、幾つかのパターンを検証してみましたが、node.js経由でJSONドキュメントデータをプラグイン経由で処理する事が可能なので、
JSONドキュメント処理をXプロトコル経由で高速に処理出来そうです。ただ、SQL処理とSQLX経由の処理に関しては、自分の方では実際のベンチマークは行っていないので、
Dimitriさんがブログで結果を発表してくれるのを待とうかと思います。
まだリリース間もないので、色々と情報を集めて紹介出来ればと思います。


先日、リリースされたMySQL5.7.12に含まれるmysqlxプラグインと、
同時にリリースされた、MySQL Shellの初期設定と基本動作確認を確認してみました。
基本的にこれらは、MySQL5.7で追加されたJSONデータ型を含むスキーマレスなドキュメントデータを、
mysqlxプラグインを利用する事でより柔軟に処理出来るようにする為に追加され、
MySQLをドキュメントデータベースとしてより利用し易くする為に追加された機能となっています。

MySQL Shellは、MySQLサーバの開発と管理をサポートする、
JavaScript、Python、SQLの対話型のインタフェースをサポートするMySQLの新しいコンポーネント。
データのクエリと更新処理だけでなく、様々な管理操作を実行する為にMySQLのシェルを使用することができます。
※MySQL Shellを利用する為には、事前にMySQL5.7.12以降に含まれているXプラグインをインストールしておく必要があります。

Xプラグイン
Xプロトコルを使用して通信を可能にするMySQLサーバプラグイン。
X DevAPIを実装するクライアントをサポートし、ドキュメントストアとしてMySQLを使用することができます。

Xプロトコル
Xプロトコルは、Xプラグインを実行しているMySQLサーバーと通信します。
Xプロトコルは、SSL経由でCRUDとSQL操作、認証の両方をサポートしていて、
コマンドのストリーミング処理を可能にし、プロトコルとメッセージレイヤ上に拡張可能。

root@localhost [information_schema]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_DESCRIPTION from plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+--------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_DESCRIPTION |
+-------------+----------------+--------------------+
| mysqlx      | 1.0            | X Plugin for MySQL |
+-------------+----------------+--------------------+
1 row in set (0.00 sec)

root@localhost [information_schema]> 

【X Pluginインストール】
参照:http://dev.mysql.com/doc/refman/5.7/en/x-plugin-installation.html

通常通り、INSTALL PLUGINコマンドでインストール

root@localhost [mysql]> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
Query OK, 0 rows affected (0.25 sec)

root@localhost [mysql]> show plugins;
+----------------------------+----------+--------------------+-------------------+-------------+
| Name                       | Status   | Type               | Library           | License     |
+----------------------------+----------+--------------------+-------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
<SNIP>
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ngram                      | ACTIVE   | FTPARSER           | NULL              | PROPRIETARY |
| mecab                      | ACTIVE   | FTPARSER           | libpluginmecab.so | PROPRIETARY |
| MYSQL_FIREWALL             | ACTIVE   | AUDIT              | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_USERS       | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_WHITELIST   | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so         | PROPRIETARY |
+----------------------------+----------+--------------------+-------------------+-------------+
49 rows in set (0.00 sec)

root@localhost [mysql]> 

【X Plugin オプションと変数について】
設定パラメータに関しては、此方を参照下さい。
http://dev.mysql.com/doc/refman/5.7/en/x-plugin-option-variable-reference.html

【接続方法】
既に、mysqlshをインストール済みなので –sqlオプションを仕様してSQLを流し込んでみる。
mysqlxのPort33060(Default:mysqlx_port)から、ローカルのMySQLにアクセスしてSQLを実行しています。

【サンプルデータベース作成】
参照:
http://downloads.mysql.com/docs/world_x-db.zip
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-download.html

mysqlsh経由でサンプルデータベースの作成して、mysqlクライアントで接続してスキーマとテーブルの確認

[root@misc01 MID2016]# mysqlsh -u admin -p --sql --recreate-schema world_x < world_x.sql
Enter password: 
Recreating schema world_x...

[root@misc01 MID2016]# mysql -u root -p -e "show databases like 'world%'"
Enter password: 
+-------------------+
| Database (world%) |
+-------------------+
| world             |
| world2            |
| world_x           |
+-------------------+
[root@misc01 MID2016]# mysql -u root -p -e "show tables from world_x"
Enter password: 
+-------------------+
| Tables_in_world_x |
+-------------------+
| City              |
| Country           |
| CountryInfo       |
| CountryLanguage   |
+-------------------+

【接続モード】
参考) 接続方法としては、現状では、以下のモードを選択できるようです。

# mysqlsh --help
MySQL Shell 1.0.3 Development Preview

<SNIP>

  --sql                    Start in SQL mode using a node session.
  --sqlc                   Start in SQL mode using a classic session.
  --js                     Start in JavaScript mode.
  --py                     Start in Python mode.

<SNIP>

こちらでは、mysqlsh経由でjava scriptモードの状態でJSONデータを処理してみます。


[root@misc01 MID2016]# mysqlsh -u admin -p 
Creating an X Session to admin@localhost:33060
Enter password: 
No default schema selected.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> news_collection = session.getSchema('NEW57').createCollection("X_JSON");
<Collection:X_JSON>
mysql-js> news_collection.add({"id": 1, "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]});
Query OK, 1 item affected (0.01 sec)

mysql-js> news_collection.find('name like :n').bind('n','Document%');
[
    {
        "Conditions": [
            "NEW",
            2016
        ],
        "_id": "26c8af795703e611630c0800279cea3c",
        "id": 1,
        "name": "Document Data",
        "price": 60000
    }
]
1 document in set (0.03 sec)

mysql-js>  

MySQLに接続してデータを確認した状態


root@localhost [NEW57]> desc X_JSON;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> select * from X_JSON\G
*************************** 1. row ***************************
doc: {"id": 1, "_id": "26c8af795703e611630c0800279cea3c", "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]}
_id: 26c8af795703e611630c0800279cea3c
1 row in set (0.00 sec)

root@localhost [NEW57]> 

【Beta Draft】
X DevAPI User Guide / Overview
http://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html
その他、X DevAPI経由でコーディング出来そう。但し、現時点ではベータリリースなのでもう少ししたら確認。

【その他:Status変数】
こちらで、mysqlx経由の処理を確認する事が可能です。


[root@misc01 MID2016]# mysql -u root -p -e "show status like 'mysqlx%'"
Enter password: 
+-------------------------------------+--------------------------+
| Variable_name                       | Value                    |
+-------------------------------------+--------------------------+
| Mysqlx_bytes_received               | 389773                   |
| Mysqlx_bytes_sent                   | 3466                     |
| Mysqlx_connection_accept_errors     | 0                        |
| Mysqlx_connection_errors            | 0                        |
| Mysqlx_connections_accepted         | 2                        |
| Mysqlx_connections_closed           | 2                        |
| Mysqlx_connections_rejected         | 0                        |
| Mysqlx_crud_delete                  | 0                        |
| Mysqlx_crud_find                    | 0                        |
| Mysqlx_crud_insert                  | 0                        |
| Mysqlx_crud_update                  | 0                        |
| Mysqlx_errors_sent                  | 1                        |
| Mysqlx_expect_close                 | 0                        |
| Mysqlx_expect_open                  | 0                        |
| Mysqlx_init_error                   | 1                        |
| Mysqlx_notice_other_sent            | 76                       |
| Mysqlx_notice_warning_sent          | 2                        |
| Mysqlx_rows_sent                    | 18                       |
| Mysqlx_sessions                     | 0                        |
| Mysqlx_sessions_accepted            | 1                        |
| Mysqlx_sessions_closed              | 1                        |
| Mysqlx_sessions_fatal_error         | 0                        |
| Mysqlx_sessions_killed              | 0                        |
| Mysqlx_sessions_rejected            | 1                        |
| Mysqlx_ssl_accepts                  | 0                        |
| Mysqlx_ssl_active                   |                          |
| Mysqlx_ssl_cipher                   |                          |
| Mysqlx_ssl_cipher_list              |                          |
| Mysqlx_ssl_ctx_verify_depth         | 18446744073709551615     |
| Mysqlx_ssl_ctx_verify_mode          | 5                        |
| Mysqlx_ssl_finished_accepts         | 0                        |
| Mysqlx_ssl_server_not_after         | Oct 19 05:28:15 2025 GMT |
| Mysqlx_ssl_server_not_before        | Oct 22 05:28:15 2015 GMT |
| Mysqlx_ssl_verify_depth             |                          |
| Mysqlx_ssl_verify_mode              |                          |
| Mysqlx_ssl_version                  |                          |
| Mysqlx_stmt_create_collection       | 0                        |
| Mysqlx_stmt_create_collection_index | 0                        |
| Mysqlx_stmt_disable_notices         | 0                        |
| Mysqlx_stmt_drop_collection         | 0                        |
| Mysqlx_stmt_drop_collection_index   | 0                        |
| Mysqlx_stmt_enable_notices          | 0                        |
| Mysqlx_stmt_execute_sql             | 69                       |
| Mysqlx_stmt_execute_xplugin         | 2                        |
| Mysqlx_stmt_kill_client             | 0                        |
| Mysqlx_stmt_list_clients            | 0                        |
| Mysqlx_stmt_list_notices            | 0                        |
| Mysqlx_stmt_list_objects            | 2                        |
| Mysqlx_stmt_ping                    | 0                        |
| Mysqlx_worker_threads               | 2                        |
| Mysqlx_worker_threads_active        | 0                        |
+-------------------------------------+--------------------------+
[root@misc01 MID2016]# 

参考までに、MySQL ShellのSQLモードの場合とjavascriptモードの場合では接続が3306か33060からの接続かどうかは以下のステータス変数でも確認する事が可能です。

【SQLモードの場合】


[root@misc01 MID2016]# echo "INSERT INTO T_UC01 VALUES (1,'mysql shell'),(2,'マイエスキュウエルシェル');" | mysqlsh -u demo_user -ppassword --sql --schema=world_x2
mysqlx: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 MID2016]# 


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> 


【JavaScriptモードの場合】


[root@misc01 MID2016]# mysqlsh --uri demo_user@localhost/NEW57 -ppassword
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating an X Session to demo_user@localhost:33060/NEW57
Default schema `NEW57` accessible through db.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> db.createCollection("x_posts");
<Collection:x_posts>
mysql-js> db.x_posts.add({"title":"Hello World", "text":"This is the first post via mysqlx"});
Query OK, 1 item affected (0.01 sec)

mysql-js> db.x_posts.find("title = 'Hello World'").sort(["title"]);
[
    {
        "_id": "d2c6eb188b08e6113d110800279cea3c",
        "text": "This is the first post via mysqlx",
        "title": "Hello World"
    }
]
1 document in set (0.00 sec)

mysql-js> 

mysqlx経由のCRUDの実行数が確認出来る。
INSERT x 1回
SELECT x 1回


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 1     |
| Mysqlx_crud_insert | 1     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.01 sec)

root@localhost [sys]> 


【参考】
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell.html
http://dev.mysql.com/doc/refman/5.7/en/document-store.html
http://dev.mysql.com/doc/dev/connector-nodejs/
http://mysqlserverteam.com/mysql-5-7-12-part-3-more-than-just-sql/
http://mysqlserverteam.com/mysql-5-7-12-part-4-a-new-mysql-command-line-shell/

メモ:Node.jsのサンプルもあるので、後日、別途JSONドキュメント処理を実施してみる。
http://dev.mysql.com/doc/dev/connector-nodejs/


Generated_Column(生成列)とJSONデータを扱う場合に考慮しておく事
MySQL5.7.9とMySQL5.7.10以降で挙動が異なる為、念の為こちらにメモしておきます。
基本的には、MySQL5.7.10で改善されたという事になります。

http://bugs.mysql.com/bug.php?id=79552
>I think the behaviour you observed in 5.7.9 was actually a bug.
>The bug was fixed in 5.7.10, which is why you see different results now. There is some discussion about this in bug#78464 and bug#76834.

関連要望チケット from Morganさん
https://bugs.mysql.com/bug.php?id=78736

root@localhost [NEW57]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.10-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

確認に利用したテーブル定義

root@localhost [NEW57]> show create table features\G
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(`feature`,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_type` (`feature_type`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

上記で定義された生成列からJSONデータをSELECTする場合
生成列のデータは””を含む為、SELECTした場合には””を付けなければいけない。

root@localhost [NEW57]> select id,feature_street from NEW57.features where feature_street = 'MARKET' limit 1;
Empty set (0.00 sec)

root@localhost [NEW57]> select id,feature_street from NEW57.features where feature_street = '"MARKET"' limit 1;
+-------+----------------+
| id    | feature_street |
+-------+----------------+
| 12250 | "MARKET"       |
+-------+----------------+
1 row in set (0.00 sec)

上記の挙動としては、CASTした時に”(ダブルクオート)をそのまま付けるからという事のようです。
CASTしないでそのままjson_extract関数でSELECTした場合は”(ダブルクオート)は不要です。詳細は以下の例を参照下さい。

root@localhost [NEW57]> select * from NEW57.features where cast(json_extract(feature,'$.properties.STREET') as char) = 'MARKET' limit 1;
Empty set (1.03 sec)

root@localhost [NEW57]> select * from NEW57.features where cast(json_extract(feature,'$.properties.STREET') as char) = '"MARKET"' limit 1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id    | feature                                                                                                                                                                                                                                                                                                                                                                                                                                                | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | "Feature"    | "MARKET"       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.05 sec)


root@localhost [NEW57]> select * from NEW57.features where json_extract(feature,'$.properties.STREET') = '"MARKET"' limit 1;
Empty set (1.36 sec)

root@localhost [NEW57]> select * from NEW57.features where json_extract(feature,'$.properties.STREET') = 'MARKET' limit 1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id    | feature                                                                                                                                                                                                                                                                                                                                                                                                                                                | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | "Feature"    | "MARKET"       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.08 sec)

JSON_EXTRACTと生成列を常に同じように”(ダブルクオート)を利用する事無く比較するには?
結論としては,CASTしてしまうと”(ダブルクオート)はついてしまうので、生成列を作る時に以下の例のように、
json_unquoteとjson_extractを同時に使う事で常に”(ダブルクオート)を利用する事無くJSONデータを参照する事が可能になります。

CREATE TABLE `features_with_unquote` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.type'))) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_feature_type` (`feature_type`),
  KEY `idx_feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4;


root@localhost [NEW57]> insert into features_with_unquote(id,feature) select id,feature from features;
Query OK, 206560 rows affected (9.62 sec)
Records: 206560  Duplicates: 0  Warnings: 0

json_extractでJSONデータを参照

root@localhost [NEW57]> select * from NEW57.features_with_unquote where json_extract(feature,'$.properties.STREET') = 'MARKET' limit 1; 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id    | feature                                                                                                                                                                                                                                                                                                                                                                                                                                                | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | Feature      | MARKET         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.04 sec)

生成列の参照(json_unquoteとjson_extractで生成)

root@localhost [NEW57]> select * from NEW57.features_with_unquote where feature_street = 'MARKET' limit 1; 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| id    | feature                                                                                                                                                                                                                                                                                                                                                                                                                                                | feature_type | feature_street |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
| 12250 | {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}} | Feature      | MARKET         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------------+
1 row in set (0.00 sec)

こちらの処理により常に、”(ダブルクオート)を付けずにデータが参照できるようになりました。

参照: 8.3.9 Optimizer Use of Generated Column Indexes


10月19日にMySQL5.7がGAになったので、新規追加されたJSONデータ型の確認を行いました。
JSONはXMLと同じように、スマートフォンアプリ用のAPIやB2BやB2C連携でJSON APIが多く使われているようです。

基本的な動作検証
1. 基本的な構文バリデーション機能
2. JSONデータ型とTEXT型のパフォーマンス差
3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
4. JSONドキュメントの部分アップデート

ちなみに、ザックリとGoogleで検索してみると以下のように多くのサイトがJSON用のAPIを提供していました。
Twitter
https://dev.twitter.com/rest/reference/get/statuses/user_timeline
原子力規制委員会
http://radioactivity.nsr.go.jp/data/ja/real/area_24000/2401_trend.json
Google API
https://storage.googleapis.com/maps-devrel/google.json
ぐるナビ
http://api.gnavi.co.jp/api/tools/?apitype=ver1_RestSearchAPI
シカゴ市
https://data.cityofchicago.org/resource/alternative-fuel-locations.json?$limit=100&$offset=50

1. 基本的な構文バリデーション機能

検証テーブル (TEXT型)

CREATE TABLE `employees_txt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

検証テーブル (JSON型)

CREATE TABLE `employees_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

それぞれのテーブルに、構文ミスしたJSONデータを入れてみると以下のようになる。
JSONデータ型はINSERT時のバリデーションが正常に効いている。


root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_txt(data) VALUES ('{"id": 2, "name": "Joe"');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> SELECT * FROM NEW57.employees_txt;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
|  2 | {"id": 2, "name": "Joe"   |
+----+---------------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0.01 sec)

root@localhost [NEW57]> INSERT INTO NEW57.employees_json(data) VALUES ('{"id": 2, "name": "Joe"');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value (or column) '{"id": 2, "name": "Joe"'.
root@localhost [NEW57]> SELECT * FROM NEW57.employees_json;
+----+---------------------------+
| id | data                      |
+----+---------------------------+
|  1 | {"id": 1, "name": "Jane"} |
+----+---------------------------+
1 row in set (0.00 sec)

root@localhost [NEW57]> 

2. JSONデータ型とTEXT型のパフォーマンス差
セミナーでも実施しましたが、以下のようなシンプルなテーブルに対して、
ダウンロードしてきた、20万6千件のJSONデータをインサートして参照レスポンス比較。

それぞれの参照クエリにて同じコマンドと同じデータでレスポンス比較
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_txt;
SELECT distinct json_extract(feature,’$.type’) as feature FROM NEW57.features_json;

結果としては、JSON型の方がTEXT型に比べて圧倒的に早い事が確認出来る。

[root@misc01 SOD2015]# ./json_and_text_without_index.sh 
INDEXの無いテーブルに対するSELECTを、TEXT型とJSONデータ型で比較します。
SELECT distinct json_extract(feature,'$.type') as feature FROM 各テーブル

【TEXT型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m9.724s
user    0m0.005s
sys     0m0.002s

【JSON型】
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| feature   |
+-----------+
| "Feature" |
+-----------+

real    0m1.506s
user    0m0.004s
sys     0m0.004s
[root@misc01 SOD2015]# 

3. JSONとGenerated Columnの連携によるインデックス利用とパフォーマンス
JSONドキュメントのオブジェクトの中からSTREET名の部分からデータを抽出して列を作成しIndexを付与。

列:      json_extract(feature,’$.properties.STREET’))
インデックス: KEY `feature_street` (`feature_street`)

 

JSONデータ型のサンプルを入れたテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12250
       feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.39836263491878, 37.79189388899312, 0], [-122.39845248797837, 37.79233030084018, 0], [-122.39768507706792, 37.7924280850133, 0], [-122.39836263491878, 37.79189388899312, 0]]]}, "properties": {"TO_ST": "388", "BLKLOT": "0265003", "STREET": "MARKET", "FROM_ST": "388", "LOT_NUM": "003", "ST_TYPE": "ST", "ODD_EVEN": "E", "BLOCK_NUM": "0265", "MAPBLKLOT": "0265003"}}
  feature_type: "Feature"
feature_street: "MARKET"
Press [Enter] key to resume.

JSONデータ型とGenerated Columnを利用したテーブル
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: features
Create Table: CREATE TABLE `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  `feature_type` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.type')) VIRTUAL,
  `feature_street` varchar(30) GENERATED ALWAYS AS (json_extract(feature,'$.properties.STREET')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `feature_type` (`feature_type`),
  KEY `feature_street` (`feature_street`)
) ENGINE=InnoDB AUTO_INCREMENT=206561 DEFAULT CHARSET=utf8mb4
Press [Enter] key to resume.

JSONドキュメントに対して、INDEX検索が利用出来るか確認 -> where json_extract(feature,'$.properties.STREET') = '"MARKET"'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 123
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
[root@misc01 SOD2015]# 

4. JSONドキュメントの部分アップデート
JSONドキュメントの一部をUPDATEする場合の処理方法確認。
TEXT型だと、全ての列データを入れ替える必要がありますが、JSONだと一部のみ変更可能です。

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+------------------------------------------------------------------------+---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}| 50000   |
+----+------------------------------------------------------------------------+---------+

update T_JSON_DOC set T_JSON_DOC.body = JSON_REPLACE(body,"$.price",15000) 
where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select id,body,json_extract(body,"$.price") as extract from T_JSON_DOC where id = 3;
+----+----------------------------------------------------------------------- +---------+
| id | body                                                                   | extract |
+----+------------------------------------------------------------------------+---------+
|  3 | {"id": 3, "name": "冷蔵庫", "price": 15000, "Conditions": ["NEW", 2015]}| 15000   |
+----+------------------------------------------------------------------------+---------+

文字入れ替えに便利なJSONファンクション(例)
JSON_SET() 既存の値を置き換え、存在しない値を追加
JSON_INSERT() 既存の値を置き換えずに値を挿入
JSON_REPLACE() 既存の値のみを置き換えます

参照:
11.6 The JSON Data Type
https://dev.mysql.com/doc/refman/5.7/en/json.html

MySQL5.7セミナー資料
https://www-jp.mysql.com/news-and-events/seminar/downloads.html


MySQL5.7において、JSON(JavaScript Object Notation)がサポートされていて、
様々な言語やアプリ間で、より簡単にデータの受け渡しが行えるようになりました。
ドキュメントをJSONフォーマットでデータベースに保存して、
様々なアプリケーションから呼び出して利用出来るなど、
汎用性と運用面での活用が出来るようになっています。

1) ネイティブのJSONデータ型
効率的なデータ処理と保管にネイティブ内部バイナリ形式をサポート。
2) 組み込みJSONファンクション
効率よくドキュメントを保存,検索,更新,操作する事を可能にします。
3) JSON コンパレーター
文書データを容易にSQLクエリと統合することが可能
4) Generated Columnsを利用し、ドキュメントにインデックスを利用する事が可能。
新しいアナライザーは,自動的に利用可能で最適な機能インデックスを利用。

ファンクションの詳細は、こちらを参照してください。
12.16 JSON Functions

検証バージョン


root@localhost [USER01]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.00 sec)

検証用テーブル


root@localhost [USER01]> show create table T_JSON\G
*************************** 1. row ***************************
       Table: T_JSON
Create Table: CREATE TABLE `T_JSON` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `body` json DEFAULT NULL,
  `WithTax` decimal(10,2) GENERATED ALWAYS AS (json_extract(body,'$.price')*1.08) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_total_cost_v` (`WithTax`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [USER01]> 

※WithTaxは、MySQL5.7から利用可能なGenerated Columnsで作成して、
 文書内のデータからインデックスを作成出来るようにしています。
 ここでは、文書からpriceを抜き出して、TAXを含むコストをベースにインデックスを作成しています。

テストデータ


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":1,"name":"自転車","price":10000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.15 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":2,"name":"TV","price":30000,"Conditions":["USED",2013]}');
Query OK, 1 row affected (0.01 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":3,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":4,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.00 sec)

root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":5,"name":"自転車","price":25000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.01 sec)

<SNIP>

JSONデータ型で作成した列に、データをINSERTする時にフォーマットが正しいかバリデーションしてくれます。
間違えていると以下のようにエラーになります。


root@localhost [USER01]> INSERT INTO T_JSON(body) VALUES ('{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 66 in value (or column) '{"id":6,"name":"冷蔵庫","price":50000,"Conditions":["NEW",2015]'.
root@localhost [USER01]> 

データの参照


root@localhost [USER01]> select * from T_JSON;
+----+-----------------------------------------------------------------------------+----------+
| id | body                                                                        | WithTax  |
+----+-----------------------------------------------------------------------------+----------+
|  1 | {"id": 1, "name": "自転車", "price": 10000, "Conditions": ["NEW", 2015]}    | 10800.00 |
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]}       | 32400.00 |
|  3 | {"id": 3, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  4 | {"id": 4, "name": "冷蔵庫", "price": 50000, "Conditions": ["NEW", 2015]}    | 54000.00 |
|  5 | {"id": 5, "name": "自転車", "price": 25000, "Conditions": ["NEW", 2015]}    | 27000.00 |
+----+-----------------------------------------------------------------------------+----------+
5 rows in set (0.01 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "TV"                        |
| "冷蔵庫"                    |
| "冷蔵庫"                    |
| "自転車"                    |
+-----------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> 

select

該当のデータを含むデータの抽出例 (json_search())等。


root@localhost [USER01]> SELECT json_extract(body,'$.name')="自転車" FROM T_JSON;
+-----------------------------------------+
| json_extract(body,'$.name')="自転車"    |
+-----------------------------------------+
|                                       1 |
|                                       0 |
|                                       0 |
|                                       0 |
|                                       1 |
+-----------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT id,json_search(body,'one','%自転車%') FROM T_JSON;
+----+---------------------------------------+
| id | json_search(body,'one','%自転車%')    |
+----+---------------------------------------+
|  1 | "$.name"                              |
|  2 | NULL                                  |
|  3 | NULL                                  |
|  4 | NULL                                  |
|  5 | "$.name"                              |
+----+---------------------------------------+
5 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') 
    -> FROM T_JSON where json_extract(body,'$.name') = '自転車';
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "自転車"                    |
| "自転車"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> 

JONS_WHERE

Generated Columnにインデックスを張ってあるので、インデックスを使用したドキュメントの検索の確認。


root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+-----------------------------+
| json_extract(body,'$.name') |
+-----------------------------+
| "冷蔵庫"                    |
| "冷蔵庫"                    |
+-----------------------------+
2 rows in set (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where json_extract(body,'$.price') > 40000;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> explain SELECT json_extract(body,'$.name') FROM T_JSON where WithTax > 40000;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T_JSON | NULL       | range | idx_total_cost_v | idx_total_cost_v | 6       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [USER01]> 

index

【アップデート】

MySQL5.7 JSONデータ型とTEXTデータ型の比較

JSONデータとGenerated_Columnを使う場合の考慮事項

MySQL JSONデータ型とJSON関数の復習

参考:
12.16 JSON Functions
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

MySQL and PostgreSQL JSON functions: do they differ much?
https://blogs.oracle.com/svetasmirnova/entry/mysql_and_postgresql_json_functions

JSON Labs Release: Effective Functional Indexes in InnoDB
http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/


MySQL 5.7: オプティマイザ

追加要素にてコストを設定可能
ディスクI/O処理性能
メモリ処理性能

コスト変更前

root@localhost [nyosm]>select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)

root@localhost [nyosm]>

Configurable Costs
root@localhost [nyosm]>SELECT * FROM mysql.engine_cost;
+-------------+-------------+--------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name          | cost_value | last_update         | comment |
+-------------+-------------+--------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost |       NULL | 2014-10-09 18:51:46 | NULL    |
+-------------+-------------+--------------------+------------+---------------------+---------+
1 row in set (0.00 sec)

root@localhost [nyosm]>SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2014-10-09 18:51:46 | NULL    |
| disk_temptable_row_cost      |       NULL | 2014-10-09 18:51:46 | NULL    |
| key_compare_cost             |       NULL | 2014-10-09 18:51:46 | NULL    |
| memory_temptable_create_cost |       NULL | 2014-10-09 18:51:46 | NULL    |
| memory_temptable_row_cost    |       NULL | 2014-10-09 18:51:46 | NULL    |
| row_evaluate_cost            |       NULL | 2014-10-09 18:51:46 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

root@localhost [nyosm]>

コスト設定変更前(Default)

root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
    -> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
    -> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
    -> SELECT 'relation' as type,id FROM relationtags WHERE
    -> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "452.40"
            },
            "table": {
              "table_name": "nodetags",
              "access_type": "ref",
              "possible_keys": [
                "i_nodekeys",
                "i_nodevalues"
              ],
              "key": "i_nodevalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 377,
              "rows_produced_per_join": 22,
              "filtered": 6.0989,
              "cost_info": {
                "read_cost": "377.00",
                "eval_cost": "4.60",
                "prefix_cost": "452.40",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "37.20"
            },
            "table": {
              "table_name": "waytags",
              "access_type": "ref",
              "possible_keys": [
                "i_waykeys",
                "i_wayvalues"
              ],
              "key": "i_wayvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 31,
              "rows_produced_per_join": 0,
              "filtered": 0.1613,
              "cost_info": {
                "read_cost": "31.00",
                "eval_cost": "0.01",
                "prefix_cost": "37.20",
                "data_read_per_join": "15"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 3,
            "cost_info": {
              "query_cost": "1.20"
            },
            "table": {
              "table_name": "relationtags",
              "access_type": "ref",
              "possible_keys": [
                "i_relationkeys",
                "i_relationvalues"
              ],
              "key": "i_relationvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": 5,
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.01",
                "prefix_cost": "1.20",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
            }
          }
        }
      ]
    }
  }
}
1 row in set, 1 warning (0.39 sec)

root@localhost [nyosm]>

explainnull

コスト設定変更

root@localhost [nyosm]>UPDATE mysql.server_cost SET cost_value = 10;
Query OK, 6 rows affected (0.19 sec)
Rows matched: 6  Changed: 6  Warnings: 0

root@localhost [nyosm]>SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |         10 | 2014-12-18 17:26:13 | NULL    |
| disk_temptable_row_cost      |         10 | 2014-12-18 17:26:13 | NULL    |
| key_compare_cost             |         10 | 2014-12-18 17:26:13 | NULL    |
| memory_temptable_create_cost |         10 | 2014-12-18 17:26:13 | NULL    |
| memory_temptable_row_cost    |         10 | 2014-12-18 17:26:13 | NULL    |
| row_evaluate_cost            |         10 | 2014-12-18 17:26:13 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

root@localhost [nyosm]>

root@localhost [nyosm]>FLUSH OPTIMIZER_COSTS;
Query OK, 0 rows affected (0.00 sec)

root@localhost [nyosm]>

コストの設定変更後

root@localhost [nyosm]>EXPLAIN FORMAT=JSON SELECT 'node' as type, id
    -> FROM nodetags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as
    -> type, id FROM waytags WHERE k='amenity' and v='cafe' UNION
    -> SELECT 'relation' as type,id FROM relationtags WHERE
    -> k='amenity' and v='cafe'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "452.40"
            },
            "table": {
              "table_name": "nodetags",
              "access_type": "ref",
              "possible_keys": [
                "i_nodekeys",
                "i_nodevalues"
              ],
              "key": "i_nodevalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 377,
              "rows_produced_per_join": 22,
              "filtered": 6.0989,
              "cost_info": {
                "read_cost": "377.00",
                "eval_cost": "4.60",
                "prefix_cost": "452.40",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`nodetags`.`v` = 'cafe') and (`nyosm`.`nodetags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "37.20"
            },
            "table": {
              "table_name": "waytags",
              "access_type": "ref",
              "possible_keys": [
                "i_waykeys",
                "i_wayvalues"
              ],
              "key": "i_wayvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 31,
              "rows_produced_per_join": 0,
              "filtered": 0.1613,
              "cost_info": {
                "read_cost": "31.00",
                "eval_cost": "0.01",
                "prefix_cost": "37.20",
                "data_read_per_join": "15"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`waytags`.`v` = 'cafe') and (`nyosm`.`waytags`.`k` = 'amenity'))"
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 3,
            "cost_info": {
              "query_cost": "1.20"
            },
            "table": {
              "table_name": "relationtags",
              "access_type": "ref",
              "possible_keys": [
                "i_relationkeys",
                "i_relationvalues"
              ],
              "key": "i_relationvalues",
              "used_key_parts": [
                "v"
              ],
              "key_length": "15",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": 5,
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.01",
                "prefix_cost": "1.20",
                "data_read_per_join": "16"
              },
              "used_columns": [
                "id",
                "k",
                "v"
              ],
              "attached_condition": "((`nyosm`.`relationtags`.`v` = 'cafe') and (`nyosm`.`relationtags`.`k` = 'amenity'))"
            }
          }
        }
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

root@localhost [nyosm]>

explain10

manual:
http://dev.mysql.com/doc/refman/5.7/en/cost-model.html

MySQL 5.6

root@localhost [(none)]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.01 sec)

MySQL5.7

root@localhost [(none)]>

root@localhost [nyosm]>SHOW VARIABLES like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on
1 row in set (0.04 sec)

root@localhost [nyosm]>