JSON型の使い方(検索, 抽出, 挿入, 更新, 置換)

2022/11/12

MySQL5.7から利用できるようになったJSON型の基本的な操作方法を確認します。JSONデータを扱うための便利な関数も用意されています。主な関数の利用例も紹介します。

作業環境

JSONは MySQL5.7以上 で利用できます。ここでは、以下環境で作業します。

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper

JSON型

テーブル生成

JSON型のカラムを含むテーブルを生成します。

mysql> CREATE TABLE `json_users` (`col` JSON);
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW COLUMNS FROM `json_users`;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col   | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

レコード挿入

下記クエリを実行してみます。

INSERT INTO `json_users`
VALUES
('{"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}'),
('{"name": "yamada", "gender": 2, "options": {"x": 300}}'),
('{"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}');

mysql> INSERT INTO `json_users`
    -> VALUES
    -> ('{"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}'),
    -> ('{"name": "yamada", "gender": 2, "options": {"x": 300}}'),
    -> ('{"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

レコード取得

先ほど挿入したレコードを取得してみます。

mysql> SELECT * FROM `json_users`;
+----------------------------------------------------------------------------------+
| col                                                                              |
+----------------------------------------------------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} |
+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

JSON情報が格納できたことを確認できました。

パス指定で部分的に抽出

オブジェクトの場合 ->"$.key" の形式で抽出できます。
リストの場合 ->"$[]" の形式で抽出できます。

mysql> SELECT `col`->"$.name" FROM `json_users`;
+-----------------+
| `col`->"$.name" |
+-----------------+
| "tanaka"        |
| "yamada"        |
| "suzuki"        |
+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT `col`->"$.options.z[1]" FROM `json_users`;
+-------------------------+
| `col`->"$.options.z[1]" |
+-------------------------+
| NULL                    |
| NULL                    |
| 3                       |
+-------------------------+
3 rows in set (0.00 sec)

ダブルクォートを取りのぞきたい場合、JSON_UNQUOTE関数 を利用します。

mysql> SELECT JSON_UNQUOTE(`col`->"$.name") FROM `json_users`;
+-------------------------------+
| JSON_UNQUOTE(`col`->"$.name") |
+-------------------------------+
| tanaka                        |
| yamada                        |
| suzuki                        |
+-------------------------------+
3 rows in set (0.00 sec)

WHERE句での利用

JSONデータに含まれるデータを条件にレコードを抽出してみます。

mysql> SELECT * FROM `json_users` WHERE `col`->"$.options.x" = 100;
+----------------------------------------------------------------------------------+
| col                                                                              |
+----------------------------------------------------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

GROUP BY句での利用

JSONデータに含まれるデータでレコードを集計してみます。

mysql> SELECT `col`->"$.gender" as gender, COUNT(*) FROM `json_users` GROUP BY gender;
+--------+----------+
| gender | COUNT(*) |
+--------+----------+
| 1      |        2 |
| 2      |        1 |
+--------+----------+
2 rows in set (0.00 sec)

null判定は注意が必要

JSON型のnull判定は注意が必要です。

以下テーブルで確認します。

mysql> SELECT * FROM `json_users2`;
+------------------------------------+
| col                                |
+------------------------------------+
| {"name": "tanaka", "gender": 1}    |
| {"name": "yamada", "gender": null} |
| {"name": "suzuki", "gender": 2}    |
+------------------------------------+
3 rows in set (0.02 sec)

以下の形ですと、null判定できません。

mysql> SELECT * FROM `json_users2` WHERE col->"$.gender" IS NULL;
Empty set (0.01 sec)

mysql> 
mysql> SELECT * FROM `json_users2` WHERE col->"$.gender" = 'null';
Empty set (0.00 sec)

以下のように CAST('null' AS JSON) と比較します。

mysql> SELECT * FROM `json_users2` WHERE col->"$.gender" = CAST('null' AS JSON);
+------------------------------------+
| col                                |
+------------------------------------+
| {"name": "yamada", "gender": null} |
+------------------------------------+
1 row in set (0.01 sec)

JSONデータ生成関数

JSON_ARRAY
( JSONリストを生成 )

JSON_ARRAY([val[, val] ...])

mysql> SELECT JSON_ARRAY(1, 2, 100, 'tanaka', Null);
+---------------------------------------+
| JSON_ARRAY(1, 2, 100, 'tanaka', Null) |
+---------------------------------------+
| [1, 2, 100, "tanaka", null]           |
+---------------------------------------+
1 row in set (0.00 sec)

JSON_OBJECT
( JSONオブジェクトを生成 )

JSON_OBJECT([key, val[, key, val] ...])

mysql> SELECT JSON_OBJECT('name', 'tanaka', 'options', JSON_OBJECT('x', 100, 'y', 200));
+---------------------------------------------------------------------------+
| JSON_OBJECT('name', 'tanaka', 'options', JSON_OBJECT('x', 100, 'y', 200)) |
+---------------------------------------------------------------------------+
| {"name": "tanaka", "options": {"x": 100, "y": 200}}                       |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSONデータ検索関数

JSON_CONTAINS
( 指定値を含むか判定 )

JSON_CONTAINS(target, candidate[, path])

mysql> SELECT `col`, JSON_CONTAINS(`col`, '2', '$.gender') FROM `json_users`;
+----------------------------------------------------------------------------------+---------------------------------------+
| col                                                                              | JSON_CONTAINS(`col`, '2', '$.gender') |
+----------------------------------------------------------------------------------+---------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 |                                     0 |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           |                                     1 |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} |                                     0 |
+----------------------------------------------------------------------------------+---------------------------------------+
3 rows in set (0.00 sec)

JSON_CONTAINS_PATH
( 指定パスを含むか判定 )

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

以下、oneの利用例です。(指定パスが1つでも存在すればtrue)

mysql> SELECT `col`, JSON_CONTAINS_PATH(`col`, 'one', '$.options.y', '$.options.z') FROM `json_users`;
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
| col                                                                              | JSON_CONTAINS_PATH(`col`, 'one', '$.options.y', '$.options.z') |
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 |                                                              1 |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           |                                                              0 |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} |                                                              1 |
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
3 rows in set (0.00 sec)

以下、allの利用例です。(指定パスが全て存在すればtrue)

mysql> SELECT `col`, JSON_CONTAINS_PATH(`col`, 'all', '$.options.y', '$.options.z') FROM `json_users`;
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
| col                                                                              | JSON_CONTAINS_PATH(`col`, 'all', '$.options.y', '$.options.z') |
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 |                                                              0 |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           |                                                              0 |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} |                                                              1 |
+----------------------------------------------------------------------------------+----------------------------------------------------------------+
3 rows in set (0.00 sec)

JSON_EXTRACT
( 指定パスのデータ抽出 )

JSON_EXTRACT(json_doc, path[, path] ...)

mysql> SELECT `col`, JSON_EXTRACT(`col`, '$.name') FROM `json_users`;
+----------------------------------------------------------------------------------+-------------------------------+
| col                                                                              | JSON_EXTRACT(`col`, '$.name') |
+----------------------------------------------------------------------------------+-------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 | "tanaka"                      |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           | "yamada"                      |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} | "suzuki"                      |
+----------------------------------------------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT `col`, JSON_EXTRACT(`col`, '$.options.y') FROM `json_users`;
+----------------------------------------------------------------------------------+------------------------------------+
| col                                                                              | JSON_EXTRACT(`col`, '$.options.y') |
+----------------------------------------------------------------------------------+------------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 | 200                                |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           | NULL                               |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} | 200                                |
+----------------------------------------------------------------------------------+------------------------------------+
3 rows in set (0.00 sec)

->演算子

-> 演算子でも同様の操作が可能です。

mysql> SELECT `col`, `col`->"$.name" FROM `json_users`;
+----------------------------------------------------------------------------------+-----------------+
| col                                                                              | `col`->"$.name" |
+----------------------------------------------------------------------------------+-----------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 | "tanaka"        |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           | "yamada"        |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} | "suzuki"        |
+----------------------------------------------------------------------------------+-----------------+
3 rows in set (0.00 sec)

JSON_KEYS
( キーだけを抽出 )

JSON_KEYS(json_doc[, path])

mysql> SELECT `col`, JSON_KEYS(`col`) FROM `json_users`;
+----------------------------------------------------------------------------------+-------------------------------+
| col                                                                              | JSON_KEYS(`col`)              |
+----------------------------------------------------------------------------------+-------------------------------+
| {"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}                 | ["name", "gender", "options"] |
| {"name": "yamada", "gender": 2, "options": {"x": 300}}                           | ["name", "gender", "options"] |
| {"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}} | ["name", "gender", "options"] |
+----------------------------------------------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

JSON_SEARCH
( 指定値が含まれるパスを検索 )

JSON_SEARCH は、指定値が含まれるパスを取得できます。

パスを引数とする JSON_SET や JSON_REMOVE などと一緒に活用することもできます。

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

one の場合は最初に一致したパスを返します。

mysql> SELECT `col`, JSON_SEARCH(`col`, 'one', "yama%") FROM `json_users`;
+-------------------------------------------------------+------------------------------------+
| col                                                   | JSON_SEARCH(`col`, 'one', "yama%") |
+-------------------------------------------------------+------------------------------------+
| {"name": "yamada", "options": ["maeda"]}              | "$.name"                           |
| {"name": "suzuki", "options": ["yamada", "yamamoto"]} | "$.options[0]"                     |
| {"name": "yamada", "options": ["tanaka", "yamamoto"]} | "$.name"                           |
+-------------------------------------------------------+------------------------------------+
3 rows in set (0.00 sec)

all の場合は一致した全てのパスを返します。

mysql> SELECT `col`, JSON_SEARCH(`col`, 'all', "yama%") FROM `json_users`;
+-------------------------------------------------------+------------------------------------+
| col                                                   | JSON_SEARCH(`col`, 'all', "yama%") |
+-------------------------------------------------------+------------------------------------+
| {"name": "yamada", "options": ["maeda"]}              | "$.name"                           |
| {"name": "suzuki", "options": ["yamada", "yamamoto"]} | ["$.options[0]", "$.options[1]"]   |
| {"name": "yamada", "options": ["tanaka", "yamamoto"]} | ["$.name", "$.options[1]"]         |
+-------------------------------------------------------+------------------------------------+
3 rows in set (0.00 sec)

注意) 数値は検索できない

文字列の配列であれば検索できますが、数値の配列だと検索できません。

mysql> mysql> SELECT JSON_SEARCH('["1", "2", "3"]', 'one', '2');
+--------------------------------------------+
| JSON_SEARCH('["1", "2", "3"]', 'one', '2') |
+--------------------------------------------+
| "$[1]"                                     |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT JSON_SEARCH('[1, 2, 3]', 'one', 2);
+------------------------------------+
| JSON_SEARCH('[1, 2, 3]', 'one', 2) |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set (0.01 sec)

参考
- MYSQL Triggers: JSON_SEARCH an integer value in a json array of integers – Stack Overflow

JSONデータ更新関数

JSON_ARRAY_APPEND
( 指定リストの最後に値を追加 )

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[0]', 777);
+----------------------------------------------------------+
| JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[0]', 777) |
+----------------------------------------------------------+
| [["a", 777], ["b", "c"], "d"]                            |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][1]', 777);
+-------------------------------------------------------------+
| JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][1]', 777) |
+-------------------------------------------------------------+
| ["a", ["b", ["c", 777]], "d"]                               |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_APPEND('{"a": 10, "b": 20}', '$.a', 777);
+-----------------------------------------------------+
| JSON_ARRAY_APPEND('{"a": 10, "b": 20}', '$.a', 777) |
+-----------------------------------------------------+
| {"a": [10, 777], "b": 20}                           |
+-----------------------------------------------------+
1 row in set (0.00 sec)

JSON_ARRAY_INSERT
( 指定位置に値を挿入 )

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

mysql> SELECT JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[0]', 777);
+----------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[0]', 777) |
+----------------------------------------------------------+
| [777, "a", ["b", "c"], "d"]                              |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1][1]', 777);
+-------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1][1]', 777) |
+-------------------------------------------------------------+
| ["a", ["b", 777, "c"], "d"]                                 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT('{"a": [10, 20], "b": 20}', '$.a[1]', 777);
+--------------------------------------------------------------+
| JSON_ARRAY_INSERT('{"a": [10, 20], "b": 20}', '$.a[1]', 777) |
+--------------------------------------------------------------+
| {"a": [10, 777, 20], "b": 20}                                |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_INSERT
( 指定パスに値を挿入 )

JSON_INSERT(json_doc, path, val[, path, val] ...)

すでに存在するキーを指定した場合、挿入できません。

mysql> SELECT JSON_INSERT('{"a": 10, "b": 20}', '$.a', 777);
+-----------------------------------------------+
| JSON_INSERT('{"a": 10, "b": 20}', '$.a', 777) |
+-----------------------------------------------+
| {"a": 10, "b": 20}                            |
+-----------------------------------------------+
1 row in set (0.00 sec)

存在しないキーを指定すると挿入できます。

mysql> SELECT JSON_INSERT('{"a": 10, "b": 20}', '$.c', 777);
+-----------------------------------------------+
| JSON_INSERT('{"a": 10, "b": 20}', '$.c', 777) |
+-----------------------------------------------+
| {"a": 10, "b": 20, "c": 777}                  |
+-----------------------------------------------+
1 row in set (0.00 sec)

複数まとめて挿入できます。

mysql> SELECT JSON_INSERT('{"a": 10, "b": 20}', '$.c', 777, '$.d', 888);
+-----------------------------------------------------------+
| JSON_INSERT('{"a": 10, "b": 20}', '$.c', 777, '$.d', 888) |
+-----------------------------------------------------------+
| {"a": 10, "b": 20, "c": 777, "d": 888}                    |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

JSON_MERGE
( JSONデータ同士をマージ )

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

mysql> SELECT JSON_MERGE('{"name": "yamada"}', '{"gender": 1}');
+---------------------------------------------------+
| JSON_MERGE('{"name": "yamada"}', '{"gender": 1}') |
+---------------------------------------------------+
| {"name": "yamada", "gender": 1}                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE('{"x": 100, "y": 200}', '{"x": "aaa", "z": "ccc"}');
+----------------------------------------------------------------+
| JSON_MERGE('{"x": 100, "y": 200}', '{"x": "aaa", "z": "ccc"}') |
+----------------------------------------------------------------+
| {"x": [100, "aaa"], "y": 200, "z": "ccc"}                      |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_REMOVE
( 指定パスの値を削除 )

JSON_REMOVE(json_doc, path[, path] ...)

配列から指定パスの値を削除してみます。

mysql> SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1][0]');
+--------------------------------------------------+
| JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1][0]') |
+--------------------------------------------------+
| ["a", ["c"], "d"]                                |
+--------------------------------------------------+
1 row in set (0.00 sec)

オブジェクトから指定パスのフィールドを削除してみます。

mysql> SELECT JSON_REMOVE('{"a": 10, "b": 20}', '$.a');
+------------------------------------------+
| JSON_REMOVE('{"a": 10, "b": 20}', '$.a') |
+------------------------------------------+
| {"b": 20}                                |
+------------------------------------------+
1 row in set (0.00 sec)

JSON_REPLACE
( 指定パスの値を置換 )

JSON_REPLACE(json_doc, path, val[, path, val] ...)

mysql> SELECT JSON_REPLACE('["a", ["b", "c"], "d"]', '$[1][0]', 777);
+--------------------------------------------------------+
| JSON_REPLACE('["a", ["b", "c"], "d"]', '$[1][0]', 777) |
+--------------------------------------------------------+
| ["a", [777, "c"], "d"]                                 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_REPLACE('{"a": 10, "b": 20}', '$.a', 777);
+------------------------------------------------+
| JSON_REPLACE('{"a": 10, "b": 20}', '$.a', 777) |
+------------------------------------------------+
| {"a": 777, "b": 20}                            |
+------------------------------------------------+
1 row in set (0.00 sec)

JSON_SET と異なり、存在しないパスが指定された場合、何もしません。

mysql> SELECT JSON_REPLACE('{"a": 10, "b": 20}', '$.c', 777);
+------------------------------------------------+
| JSON_REPLACE('{"a": 10, "b": 20}', '$.c', 777) |
+------------------------------------------------+
| {"a": 10, "b": 20}                             |
+------------------------------------------------+
1 row in set (0.00 sec)

JSON_SET
( 指定パスの値を置換 )

JSON_SET(json_doc, path, val[, path, val] ...)

mysql> SELECT JSON_SET('["a", ["b", "c"], "d"]', '$[1][0]', 777);
+----------------------------------------------------+
| JSON_SET('["a", ["b", "c"], "d"]', '$[1][0]', 777) |
+----------------------------------------------------+
| ["a", [777, "c"], "d"]                             |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET('{"a": 10, "b": 20}', '$.a', 777);
+--------------------------------------------+
| JSON_SET('{"a": 10, "b": 20}', '$.a', 777) |
+--------------------------------------------+
| {"a": 777, "b": 20}                        |
+--------------------------------------------+
1 row in set (0.00 sec)

JSON_REPLACE と異なり、存在しないパスが指定された場合、値を追加します。

mysql> SELECT JSON_SET('{"a": 10, "b": 20}', '$.c', 777);
+--------------------------------------------+
| JSON_SET('{"a": 10, "b": 20}', '$.c', 777) |
+--------------------------------------------+
| {"a": 10, "b": 20, "c": 777}               |
+--------------------------------------------+
1 row in set (0.00 sec)