MySQL5.7でJSONを使ってみる

MySQL5.7でJSONが使えるようになったというので、少し理解を深めようと思います。
作業メモレベルで恐縮なのですが記載します。

# ドキュメントについて

公式のドキュメントは以下となります。
http://dev.mysql.com/doc/refman/5.7/en/json.html

他のリンクも色々と見ていたのですが、動かないことが多かったので徒労に終わりました。。


# MySQL5.7のインストール

ubuntu14.04にて作業しています。
以下のような感じでインストールしました。

```bash

$ wget http://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb
$ sudo dpkg -i mysql-apt-config_0.3.5-1ubuntu14.04_all.deb
$ sudo apt-get update
$ sudo apt-get install mysql-server-5.7
```

# 実際にやってみる

mysqlを起動し、公式ドキュメントの通りに動かすと程よく動きます。

## JSON_TYPE

引数にあるものを確認するものみたいです。

```sql

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+
1 row in set (0.00 sec)
```


## JSON_ARRAY

引数にあるものを配列に変換するようです。

```sql

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-08-19 17:38:20.000000"] |
+----------------------------------------+
1 row in set (0.00 sec)
```

## JSON_OBJECT

引数にあるものをJSONに変換するようです。

```sql

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+
1 row in set (0.00 sec)
```

## JSON_MERGE

引数にあるものをマージするようです。

```sql

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+
1 row in set (0.00 sec)
```

JSON_MERGEを使う場合、ちょっと癖がありそうですね。詳細は以下URLとなります。
http://dev.mysql.com/doc/refman/5.7/en/json.html#json-normalization


## JSON_EXTRACT

JSONの値を検索します。`$.key名`で値を探し、ない場合はNULLが返ってきます。

```sql

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.hoge');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.hoge') |
+---------------------------------------------------------+
| NULL                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)
```

ネストしたJSONを検索する場合は以下のようにすると良いみたいでした。

```sql

mysql> SELECT JSON_EXTRACT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','$.f4.f6');
+---------------------------------------------------------------------+
| JSON_EXTRACT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','$.f4.f6') |
+---------------------------------------------------------------------+
| "foo"                                                               |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
```

ワイルドカードによる検索も出来るようです。

```sql

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)
```

配列の中を指定する場合、`$.c[1]`のようにすると値を取得することが出来ます。

```sql

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[1]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[1]') |
+------------------------------------------------------------+
| 4                                                          |
+------------------------------------------------------------+
1 row in set (0.00 sec)
```

配列の中をすべて取ってくる場合、`$.c[*]`とすると良いみたいです。

```sql

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+
1 row in set (0.00 sec)
```

aとかcにあるbの値を取得する場合、`$**.b`とすると良いみたいです。

```sql

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)
```

## JSON_UNQUOTE

文字列に付いているダブルクォートを取り除きます。前述にあるJSON_EXTRACTで取得した値はダブルクォートが付いているので、JSON_UNQUOTEを付けることによって取り除くことが出来るようです。

```sql

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'));
+-----------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name')) |
+-----------------------------------------------------------------------+
| Aztalan                                                               |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
```

また、バージョンによって使い方が変わってくるようでした。以下については、5.7.9から利用できる方法です。

```sql
mysql> SELECT JSON_UNQUOTE(a.json_data->'$.name') FROM (SELECT '{"id": 14, "name": "Aztalan"}' AS json_data) a;
+-------------------------------------+
| JSON_UNQUOTE(a.json_data->'$.name') |
+-------------------------------------+
| Aztalan                             |
+-------------------------------------+
1 row in set (0.00 sec)
```

5.7.13以降から以下のような方法で取得することが出来るようです。

```sql
mysql> SELECT a.json_data->>'$.name' FROM (SELECT '{"id": 14, "name": "Aztalan"}' AS json_data) a;
+------------------------+
| a.json_data->>'$.name' |
+------------------------+
| Aztalan                |
+------------------------+
1 row in set (0.00 sec)
```

表でまとめると以下のようになります。

|version|query|
|:--|:--|
|5.7.8|JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.name'))|
|5.7.9|JSON_UNQUOTE(json_data->'$.name')|
|5.7.13|json_data->>'$.name'|

---

とりあえず、検索は出来そうな感じもしますが引き続き調査しながらメモを残すようにしていきます。