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/

Comments are closed.

Post Navigation