MySQL 8.0 をJSON形式で操作

2018.02.05

こんにちは。YDCのさだおです。
今回は、SQLServer→Oracleに続き、MySQL における JSON のご紹介です。

MySQL 5.7 でJSON型がサポートされ、MySQL 8.0 では JSON関数がいくつか追加されます。
MySQL 8で追加される関数JSON_ARRAYAGG、JSON_OBJECTAGGや、
その他メジャーな関数の簡単な使用例を取り上げます。

今までのコラムに登場した animal テーブルデータを使います。

mysql> select * from animal;
+------+------+------+
| no | name | cry |
+------+------+------+
| 1 | dog | wan |
| 1 | cat | nya! |
+------+------+------+

関数を使っての操作(Oracleと比較)

コラム「Oracle 12cR2をJSON形式で操作」で取り上げた関数をMySQLで試します。
ここでは関数の説明は省略します。

1. JSON_OBJECT関数

JSON_OBJECT関数で animal テーブルデータをJSON形式で取得します。
JSON_OBJECT関数の引数では、"キー" + , (カンマ) + バリュー(列名) をセットします。
Oracleでは、"キー" + value + バリュー(列名)と指定しますのでMySQLとは違いますね。

mysql> select json_object("no",no,"name",name,"cry",cry) from animal;
+--------------------------------------------+
| json_object("no",no,"name",name,"cry",cry) |
+--------------------------------------------+
| {"no": 1, "cry": "wan", "name": "dog"} |
| {"no": 1, "cry": "nya!", "name": "cat"} |
+--------------------------------------------+

2. JSON_OBJECTAGG関数

JSON_OBJECTAGG関数は、MySQL 8.0 で追加された関数です。
JSON_OBJECTAGG関数もJSON_OBJECT関数と同様に、カンマ区切りでバリューを引数として渡します。
Oracleでは、valueで区切っていましたね。

mysql> select json_objectagg(name,cry) from animal;
+-------------------------------+
| json_objectagg(name,cry) |
+-------------------------------+
| {"cat": "nya!", "dog": "wan"} |
+-------------------------------+

3. JSON_ARRAYAGG関数

JSON_ARRAYAGG関数も MySQL 8.0 で追加された関数です。
JSON_ARRAYAGG関数は引数もOracleと同じです。

mysql> select json_arrayagg(name) from animal;
+---------------------+
| json_arrayagg(name) |
+---------------------+
| ["dog", "cat"] |
+---------------------+

4. JSON_ARRAYAGG関数とJSON_OBJECT関数の組み合わせ

1行のJSON配列として取得するには、JSON_ARRAYAGG関数とJSON_OBJECT関数を組み合わせます。
こちらもOracleと全く同じ使用方法ですね。

mysql> select json_arrayagg(json_object("no",no,"name",name,"cry",cry)) from animal;
+-----------------------------------------------------------------------------------+
| json_arrayagg(json_object("no",no,"name",name,"cry",cry)) |
+-----------------------------------------------------------------------------------+
| [{"no": 1, "cry": "wan", "name": "dog"}, {"no": 1, "cry": "nya!", "name": "cat"}] |
+-----------------------------------------------------------------------------------+

JSONデータの利用

次の関数を使って、JSON形式のデータを操作してみました。
・JSON_EXTRACT関数
・JSON_SEARCH関数

まずは準備としてJSON形式のデータを格納するテーブル(animal_json_table)を作成。
animal_json_data列に、json_object関数で取得したJSON形式のデータをインサートします。

mysql> create table animal_json_table
-> (id int not null auto_increment,
-> animal_json_data json NOT NULL,
-> primary key (id));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into animal_json_table (animal_json_data)
-> select json_object("no",no,"name",name,"cry",cry) from animal;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from animal_json_table;
+----+-----------------------------------------+
| id | animal_json_data |
+----+-----------------------------------------+
| 1 | {"no": 1, "cry": "wan", "name": "dog"} |
| 2 | {"no": 1, "cry": "nya!", "name": "cat"} |
+----+-----------------------------------------+
2 rows in set (0.00 sec)

1. JSON_EXTRACT関数

JSON_EXTRACT関数を使うと、JSON型データを検索・取得できます。

mysql> select
-> id,
-> json_extract(animal_json_data,'$.name') as name,
-> json_extract(animal_json_data,'$.cry') as cry
-> from animal_json_table;
+----+-------+--------+
| id | name | cry |
+----+-------+--------+
| 1 | "dog" | "wan" |
| 2 | "cat" | "nya!" |
+----+-------+--------+
2 rows in set (0.00 sec)

"(ダブルウォート)に囲まれて値が抽出されます。
JSON_EXTRACT関数で抽出したデータを利用する上で都合が悪ければ
trim でダブルウォートを消すといった工夫が必要です。

mysql> select
-> id,
-> trim(both '"' from json_extract(animal_json_data,'$.name')) as name,
-> trim(both '"' from json_extract(animal_json_data,'$.cry')) as cry
-> from animal_json_table;
+----+------+------+
| id | name | cry |
+----+------+------+
| 1 | dog | wan | ★"(ダブルウォート)が消えました!
| 2 | cat | nya! |
+----+------+------+

2. JSON_SEARCH関数

JSON_SEARCH関数は、検索対象のJSONデータを、指定した文字列と比較し、
一致するものがあればJSONPath、見つからない場合はNULLを返します。

mysql> select json_search(animal_json_data,'all','dog') from animal_json_tablel;
+-------------------------------------------+
| json_search(animal_json_data,'all','dog') |
+-------------------------------------------+
| "$.name" | ★1行目のname要素にdogがある
| NULL | ★2行目にdogは見つからない
+-------------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from animal_json_table
-> where json_search(animal_json_data,'all','dog') is not null;
+----+----------------------------------------+ ★WHERE句で関数を使って条件指定もできます
| id | animal_json_data |
+----+----------------------------------------+
| 1 | {"no": 1, "cry": "wan", "name": "dog"} |
+----+----------------------------------------+

今回は以上です。 ここで試した関数については、Oracle とあまり違いがないことが分かりました。
今後もJSONに関連した機能が追加されていきそうですね。

Standby Expressに関するお問合わせ

  • TEL 042-333-6217
  • FAX 042-352-6101
  • LINE
  • Mail