MySQL JSON型

MySQL SQL

準備

$ sudo docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
$ mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307

mysql > create database jsonsample;

JSON型カラムを持つテーブルの作成

mysql> CREATE TABLE menu (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, j JSON) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS IN menu;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| j     | json | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

JSON型のレコードの挿入

JSON_OBJECT()で挿入する場合はカンマ区切りで、{}で挿入する場合は中をダブルクォートで外をシングルクォートで挿入する。

mysql> INSERT INTO menu (j) VALUES 
    -> (JSON_OBJECT('name', '定食', 'price', 1200)),
    -> ('{"name": "弁当", "price": 1000}');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from menu;
+----+-----------------------------------+
| id | j                                 |
+----+-----------------------------------+
|  1 | {"name": "定食", "price": 1200}   |
|  2 | {"name": "弁当", "price": 1000}   |
+----+-----------------------------------+
2 rows in set (0.00 sec)

値の抽出

JSON_EXTRACT(カラム名, '$.key')で抽出できます。
抽出したいkeyが2つ以上ある場合は配列、1つの場合は値として抽出されます。

mysql> SELECT JSON_EXTRACT(j, '$.name', '$.price') FROM menu;
+--------------------------------------+
| JSON_EXTRACT(j, '$.name', '$.price') |
+--------------------------------------+
| ["定食", 1200]                       |
| ["弁当", 1000]                       |
+--------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(j, '$.name') FROM menu;
+---------------------------+
| JSON_EXTRACT(j, '$.name') |
+---------------------------+
| "定食"                    |
| "弁当"                    |
+---------------------------+
2 rows in set (0.00 sec)

クォートが邪魔なときはJSON_UNQUOTE()で無視できます。
ちなみにWHERE句で文字列と比較する際はJSON_UNQUOTE()をかましてもかまさなくても同じようです。

mysql> SELECT * FROM menu WHERE JSON_EXTRACT(j, '$.name') = "弁当";
+----+-----------------------------------+
| id | j                                 |
+----+-----------------------------------+
|  2 | {"name": "弁当", "price": 1000}   |
+----+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM menu WHERE JSON_UNQUOTE(JSON_EXTRACT(j, '$.name')) = "弁当";
+----+-----------------------------------+
| id | j                                 |
+----+-----------------------------------+
|  2 | {"name": "弁当", "price": 1000}   |
+----+-----------------------------------+
1 row in set (0.00 sec)

上述のJSON_UNQUOTE()の有無は次のように書くこともできます。

-- SELECT JSON_UNQUOTE(JSON_EXTRACT(j, '$.name')) FROM menu;
mysql> SELECT j->>'$.name' FROM menu;
+--------------+
| j->>'$.name' |
+--------------+
| 定食         |
| 弁当         |
+--------------+
2 rows in set (0.00 sec)

-- SELECT JSON_EXTRACT(j, '$.name') FROM menu;
mysql> SELECT j->'$.name' FROM menu;
+-------------+
| j->'$.name' |
+-------------+
| "定食"      |
| "弁当"      |
+-------------+
2 rows in set (0.00 sec)

配列の操作

配列から値の抽出は$[index]のようにして指定します。

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

-- 多次元配列
mysql> SELECT JSON_EXTRACT('["a", "b", ["x", "y", "z"]]', '$[2][1]');
+--------------------------------------------------------+
| JSON_EXTRACT('["a", "b", ["x", "y", "z"]]', '$[2][1]') |
+--------------------------------------------------------+
| "y"                                                    |
+--------------------------------------------------------+
1 row in set (0.00 sec)

path の表記方法は下記のようにします。

'$.name' nameの値
'$[0]' 配列の一番頭
'$.a[1] {"a": []}となっているときa配列の2番目
'$.*' 名前のワイルドカード
'$[*]' 配列要素のワイルドカード
'$**' すべてのパスから 
mysql> SELECT JSON_EXTRACT('{"a":1, "b":"z"}', '$.*');
+-----------------------------------------+
| JSON_EXTRACT('{"a":1, "b":"z"}', '$.*') |
+-----------------------------------------+
| [1, "z"]                                |
+-----------------------------------------+
1 row in set (0.00 sec)

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

参考

www.shoeisha.co.jp