MySQLでJSONカラムを利用する（備忘録)

usagi1975 edit mysql json

MySQLでJSONカラムを利用する（備忘録)

MySQLでは、ver5.7.8以降でJSON型のカラムが利用可能になっている

当初MariaDBで検証していたが、JSONカラムについては対応の差が出てきており、ネットの情報もほとんどMySQLなので、本件からMySQLに切り替えた。

今回はdocker版の最新ver8.0.23を使っている

docker-compose.yml

version: '3.5'
services:
  db_serv:
    #image: mariadb:10.5.8
    image: mysql:8.0.23
    container_name: test-db-container
    hostname: test-db-server
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=pwd
      - MYSQL_DATABASE=testdb
      - MYSQL_USER=user1
      - MYSQL_PASSWORD=pwd
      - TZ=Asia/Tokyo
      - LANG=C.UTF-8
      - LANGUAGE=en_US.en
      - LC_ALL=C.UTF-8
    volumes:
      - test-data-volume:/var/lib/mysql
      - ./data:/home
    working_dir: /home
    restart: always
    networks:
      - test-network

MySQL + JSONの参考サイト

本家

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path

基本編

https://www.s-style.co.jp/blog/2017/05/301/

パフォーマンス編

https://www.s-style.co.jp/blog/2017/06/420/

設計編

https://www.s-style.co.jp/blog/2017/06/505/

JSON関数編

https://www.s-style.co.jp/blog/2019/03/3614/

MySQLのJSON関係の関数を勉強してみた
https://qiita.com/okumurakengo/items/fa159f8b01c1ff4c689c

サンプルテーブル定義

CREATE TABLE tbl_test_json1 (
  id int(10) unsigned AUTO_INCREMENT NOT NULL,
  str varchar(100),
  num int,
  js1 JSON,
  js2 JSON,
  CHECK (JSON_VALID(js1)),
  CHECK (JSON_VALID(js2)),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CHECK(JSON_VALID())を追加することによって、JSON文字列かどうかチェックして挿入されるので、単なる文字列が入ってしまうことはない。

> desc tbl_test_json1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| str   | varchar(100) | YES  |     | NULL    |                |
| num   | int          | YES  |     | NULL    |                |
| js1   | json         | YES  |     | NULL    |                |
| js2   | json         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

テストデータ準備

ほかにもJSON関数を使ったものもあるが、とりあえず文字列化したJSONデータを色々INSERTしてみた。

json型になっているが、内部的にはlogtext型と同じらしい。jsonだからといってドデカイデータを挿入するときは、max_allowed_packet（デフォルト4MB）の制限を受けるので注意する。

-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  'aaa',-5,'[]','{}'
);
-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  'a bbb',2,'["xxx"]',NULL
);
-- insert ng (v1がダブルクォートで囲まれてない)
-- あくまでJSON.stringify()相当じゃないとダメ
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  '',10,NULL,'{v1:"aaa"}'
);
-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  '',1,NULL,'{"v1":"aaa"}'
);
-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  'bbb',30,'["xxx", "yyy"]','{"v1":"dog","v2":0.3,"v3":{"a1":10,"a2":"cat"}}'
);
-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  'ccc',12,'[]','{"v2":-1.5,"v3":{"a1":15,"a2":"bird"}}'
);
-- insert ng (jsonはNULLはOKだが、空文字はダメ)
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  NULL,NULL,'','{"v2":0.123,"v3":null}'
);
-- insert ok
INSERT INTO tbl_test_json1 (str, num, js1, js2) VALUES (
  NULL,NULL,NULL,'{"v2":0.123,"v3":null}'
);

テストデータ

> select * from tbl_test_json1;
+----+-------+------+----------------+---------------------------------------------------------+
| id | str   | num  | js1            | js2                                                     |
+----+-------+------+----------------+---------------------------------------------------------+
|  1 | aaa   |   -5 | []             | {}                                                      |
|  2 | a bbb |    2 | ["xxx"]        | NULL                                                    |
|  3 |       |    1 | NULL           | {"v1": "aaa"}                                           |
|  4 | bbb   |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
|  5 | ccc   |   12 | []             | {"v2": -1.5, "v3": {"a1": 15, "a2": "bird"}}            |
|  6 | NULL  | NULL | NULL           | {"v2": 0.123, "v3": null}                               |
+----+-------+------+----------------+---------------------------------------------------------+

JSONデータの検索（SELECT)

JSON内の検索および抽出(JSON_EXTRACT)

JSONの中身を検索条件に加えたい場合はJSON_EXTRACTまたはcolumn->path

> select * from tbl_test_json1 where JSON_EXTRACT(js2, '$.v1')="dog";
> select * from tbl_test_json1 where js2->'$.v1'="dog";
+----+------+------+----------------+---------------------------------------------------------+
| id | str  | num  | js1            | js2                                                     |
+----+------+------+----------------+---------------------------------------------------------+
|  4 | bbb  |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
+----+------+------+----------------+---------------------------------------------------------+

> select * from tbl_test_json1 where JSON_EXTRACT(js2, '$.v2') > 0;
> select * from tbl_test_json1 where js2->'$.v2'> 0;
+----+------+------+----------------+---------------------------------------------------------+
| id | str  | num  | js1            | js2                                                     |
+----+------+------+----------------+---------------------------------------------------------+
|  4 | bbb  |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
|  6 | NULL | NULL | NULL           | {"v2": 0.123, "v3": null}                               |
+----+------+------+----------------+---------------------------------------------------------+

> select * from tbl_test_json1 where JSON_EXTRACT(js2, '$.v1')="dog" And JSON_EXTRACT(js2, '$.v2') > 0;
> select * from tbl_test_json1 where js2->'$.v1'="dog" And js2->'$.v2'> 0;
+----+------+------+----------------+---------------------------------------------------------+
| id | str  | num  | js1            | js2                                                     |
+----+------+------+----------------+---------------------------------------------------------+
|  4 | bbb  |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
+----+------+------+----------------+---------------------------------------------------------+

表示側も抽出して表示できる

> select id, js1, js2->'$.v1' as v1, js2->'$.v2' as v2 from tbl_test_json1;
+----+----------------+-------+-------+
| id | js1            | v1    | v2    |
+----+----------------+-------+-------+
|  1 | []             | NULL  | NULL  |
|  2 | ["xxx"]        | NULL  | NULL  |
|  3 | NULL           | "aaa" | NULL  |
|  4 | ["xxx", "yyy"] | "dog" | 0.3   |
|  5 | []             | NULL  | -1.5  |
|  6 | NULL           | NULL  | 0.123 |
+----+----------------+-------+-------+

JSON配列の検索(JSON_CONTAINS)

上記サンプルでは、js1(配列型JSON)については、触れていない。

https://qiita.com/ukyooo/items/19da224866affa97a211

配列に含まれる場合はJSON_CONTAINS

> select * from tbl_test_json1 where JSON_CONTAINS(js1, '"xxx"', '$');
+----+-------+------+----------------+---------------------------------------------------------+
| id | str   | num  | js1            | js2                                                     |
+----+-------+------+----------------+---------------------------------------------------------+
|  2 | a bbb |    2 | ["xxx"]        | NULL                                                    |
|  4 | bbb   |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
+----+-------+------+----------------+---------------------------------------------------------+

値がNULLのとき

> select * from tbl_test_json1 where JSON_EXTRACT(js1, '$') is NULL;
+----+------+------+------+---------------------------+
| id | str  | num  | js1  | js2                       |
+----+------+------+------+---------------------------+
|  3 |      |    1 | NULL | {"v1": "aaa"}             |
|  6 | NULL | NULL | NULL | {"v2": 0.123, "v3": null} |
+----+------+------+------+---------------------------+

2個目の値が"yyy"のレコードを抽出

> select * from tbl_test_json1 where JSON_EXTRACT(js1, '$[1]') = "yyy";
+----+------+------+----------------+---------------------------------------------------------+
| id | str  | num  | js1            | js2                                                     |
+----+------+------+----------------+---------------------------------------------------------+
|  4 | bbb  |   30 | ["xxx", "yyy"] | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
+----+------+------+----------------+---------------------------------------------------------+

JSON配列の要素数取得(JSON_DEPTH)

空だと1、要素が増えるたびに+1している感じ。NULLはNULL。

js2のオブジェクト型のJSONの場合、Key=Valueのセットの数が増えるごとに+1かな？

JSON_LENGTHというのもある。

> select id, js1, JSON_DEPTH(js1) from tbl_test_json1;
+----+----------------+-----------------+
| id | js1            | JSON_DEPTH(js1) |
+----+----------------+-----------------+
|  1 | []             |               1 |
|  2 | ["xxx"]        |               2 |
|  3 | NULL           |            NULL |
|  4 | ["xxx", "yyy"] |               2 |
|  5 | []             |               1 |
|  6 | NULL           |            NULL |
+----+----------------+-----------------+

> select id, js2, JSON_DEPTH(js2) from tbl_test_json1;
+----+---------------------------------------------------------+-----------------+
| id | js2                                                     | JSON_DEPTH(js2) |
+----+---------------------------------------------------------+-----------------+
|  1 | {}                                                      |               1 |
|  2 | NULL                                                    |            NULL |
|  3 | {"v1": "aaa"}                                           |               2 |
|  4 | {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |               3 |
|  5 | {"v2": -1.5, "v3": {"a1": 15, "a2": "bird"}}            |               3 |
|  6 | {"v2": 0.123, "v3": null}                               |               2 |
+----+---------------------------------------------------------+-----------------+

検索 (JSON_SEARCH)

JSON内のどこにcatが含まれているか、パスを返す。存在してない場合はNULLになる。

> select JSON_SEARCH(js2, 'all', 'cat') from tbl_test_json1;
+--------------------------------+
| JSON_SEARCH(js2, 'all', 'cat') |
+--------------------------------+
| NULL                           |
| NULL                           |
| NULL                           |
| "$.v3.a2"                      |
| NULL                           |
| NULL                           |
+--------------------------------+

NULLを返さない（つまりJSONのどこかに"cat"が存在している）レコードを返す

> select js2 from tbl_test_json1 where JSON_SEARCH(js2, 'all', 'cat') is not null;
+---------------------------------------------------------+
| js2                                                     |
+---------------------------------------------------------+
| {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} |
+---------------------------------------------------------+

ダブルクォート除去(JSON_UNQUOTE)

JSON_EXTRACTで取得される値は文字列だとダブルクオートがついたままになっているので、除去する関数がJSON_UNQUOTEになる。

> select JSON_UNQUOTE(JSON_EXTRACT(js2, '$.v1')) from tbl_test_json1;
+-----------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(js2, '$.v1')) |
+-----------------------------------------+
| NULL                                    |
| NULL                                    |
| aaa                                     |
| dog                                     |
| NULL                                    |
| NULL                                    |
+-----------------------------------------+

JSON_EXTRACTの進化版？(JSON_VALUE)

MySQL8.0.21で追加されたもので、従来の関数を組み合わせて使いやすくしたもの

JSON_VALUE(json_doc, path RETURNING type) = CAST + JSON_UNQUOTE + JSON_EXTRACT

-- CAST不要
JSON_VALUE(json_doc, path) = JSON_UNQUOTE + JSON_EXTRACT

> select js2, JSON_VALUE(js2, '$.v1') as v1, JSON_VALUE(js2, '$.v2' RETURNING DECIMAL(6,3)) as v2 from tbl_test_json1;
+---------------------------------------------------------+------+--------+
| js2                                                     | v1   | v2     |
+---------------------------------------------------------+------+--------+
| {}                                                      | NULL |   NULL |
| NULL                                                    | NULL |   NULL |
| {"v1": "aaa"}                                           | aaa  |   NULL |
| {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} | dog  |  0.300 |
| {"v2": -1.5, "v3": {"a1": 15, "a2": "bird"}}            | NULL | -1.500 |
| {"v2": 0.123, "v3": null}                               | NULL |  0.123 |
+---------------------------------------------------------+------+--------+

JSONキー一覧 (JSON_KEYS)

> select js2, JSON_KEYS(js2) from tbl_test_json1;
+---------------------------------------------------------+--------------------+
| js2                                                     | JSON_KEYS(js2)     |
+---------------------------------------------------------+--------------------+
| {}                                                      | []                 |
| NULL                                                    | NULL               |
| {"v1": "aaa"}                                           | ["v1"]             |
| {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} | ["v1", "v2", "v3"] |
| {"v2": -1.5, "v3": {"a1": 15, "a2": "bird"}}            | ["v2", "v3"]       |
| {"v2": 0.123, "v3": null}                               | ["v2", "v3"]       |
+---------------------------------------------------------+--------------------+

JSON配列に含まれているかどうか(MEMBER OF)

JSON_CONTAINSと似てるけど、こちらはJSONの配列型専用

> select id, js1 from tbl_test_json1 where "xxx" MEMBER OF(js1);
+----+----------------+
| id | js1            |
+----+----------------+
|  2 | ["xxx"]        |
|  4 | ["xxx", "yyy"] |
+----+----------------+

JSONデータの追加（INSERT)

https://qiita.com/tentatsu/items/116f061ca2be88f24b48

https://qiita.com/okumurakengo/items/fa159f8b01c1ff4c689c

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set

データ作成時のINSERTのように文字列化したJSONをテキストデータとしてINSERTする方法と、下記のようにJSON_OBJECT、JSON_ARRAY関数でJSONを構築する方法もある。

> insert into tbl_test_json1 set js2=JSON_OBJECT("v1",10, "v2","abc","v3",JSON_ARRAY(1,2,3,4,5));

+----+-------+------+----------------+---------------------------------------------------------+
| id | str   | num  | js1            | js2                                                     |
+----+-------+------+----------------+---------------------------------------------------------+
|  8 | NULL  | NULL | NULL           | {"v1": 10, "v2": "abc", "v3": [1, 2, 3, 4, 5]}          |
+----+-------+------+----------------+---------------------------------------------------------+

JSONデータの更新（UPDATE)

JSON_SET

要素を特定した上でその値に更新をかけるが、このJSON_SETで直接レコードの更新をかけるわけではない。

抽出したJSONオブジェクトに対して値を追加または更新を行ってJSON全体を返すだけなので、このオブジェクトを改めてSETする必要がある。

左のカラムがオリジナル、右がJSON_SET後のJSON。追加または更新されたJSONが返る。このクエリでは、DBデータは更新されていない。

> select js2, JSON_SET(js2, '$.v1', 'AAA', '$.v2', 0.299) from tbl_test_json1 where id = 3;
+---------------+---------------------------------------------+
| js2           | JSON_SET(js2, '$.v1', 'AAA', '$.v2', 0.299) |
+---------------+---------------------------------------------+
| {"v1": "aaa"} | {"v1": "AAA", "v2": 0.299}                  |
+---------------+---------------------------------------------+

JSON_INSERT

既存の値を置き換えずに値を挿入する。JSON_SETとの違いは、追加のみ行う。v1の値は既に存在しているので値の更新は行わない。

> select js2 , JSON_INSERT(js2, '$.v1', 'AAA', '$.v2', 0.299) from tbl_test_json1 where id = 3;
+---------------+------------------------------------------------+
| js2           | JSON_INSERT(js2, '$.v1', 'AAA', '$.v2', 0.299) |
+---------------+------------------------------------------------+
| {"v1": "aaa"} | {"v1": "aaa", "v2": 0.299}                     |
+---------------+------------------------------------------------+

JSON_REPLACE

既存の値のみを置き換える。JSON_SETとの違いは、更新のみ行う。v2の値は存在していないので値の追加は行わない。

> select js2 , JSON_REPLACE(js2, '$.v1', 'AAA', '$.v2', 0.299) from tbl_test_json1 where id = 3;
+---------------+-------------------------------------------------+
| js2           | JSON_REPLACE(js2, '$.v1', 'AAA', '$.v2', 0.299) |
+---------------+-------------------------------------------------+
| {"v1": "aaa"} | {"v1": "AAA"}                                   |
+---------------+-------------------------------------------------+

JSON_REMOVE

JSONから指定したキーに相当するデータを削除する。存在しないキーを指定した場合は、そのキーについては無視される。エラーにはならない。

> select js2 , JSON_REMOVE(js2, '$.v1','$.v2') from tbl_test_json1 where id = 4;
+---------------------------------------------------------+---------------------------------+
| js2                                                     | JSON_REMOVE(js2, '$.v1','$.v2') |
+---------------------------------------------------------+---------------------------------+
| {"v1": "dog", "v2": 0.3, "v3": {"a1": 10, "a2": "cat"}} | {"v3": {"a1": 10, "a2": "cat"}} |
+---------------------------------------------------------+---------------------------------+

UPDATEの例

上記関数を使ってJSONを更新したらそれをSETします。これは従来の更新処理と変わらない。

> select js2 from tbl_test_json1 where id = 3;
+---------------+
| js2           |
+---------------+
| {"v1": "aaa"} |
+---------------+

-- 上記のJSON_REPLACEを例に、実際に更新してみる
> update tbl_test_json1 set js2=JSON_REPLACE(js2, '$.v1', 'AAA', '$.v2', 0.299) where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
+---------------+
| js2           |
+---------------+
| {"v1": "AAA"} |
+---------------+

JSON_ARRAY_APPEND、JSON_ARRAY_INSERT

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-array-insert

JSON_APPENDはDeprecatedになっており、いくつかの配列更新系の関数が追加されている。

が、イマイチ使い所が浮かばないので、例は割愛。

next post

MySQLでJSONカラムを利用する（実践編その１)

前回 MySQLでJSONカラムを利用する（備忘録) [http://www.…

prev post

iPhone12 Pro用カバー購入

iPhone12 Proを先日予約しました。5年ぶり？ぐらいのiPhone復帰になります。発売日に発送予定になってますが、…
-->