カルボナーラ街道

計測と観察

MySQLでJSONを試す

MySQL_8.0

本記事は以下書籍 4.3.2 を参考にしています。

www.shoeisha.co.jp

開発環境

github.com

# ホスト
> docker --version
Docker version 20.10.8, build 3967b7d

> docker-compose --version
docker-compose version 1.29.2, build 5becea4c

# コンテナ
root@{CONTAINER_ID}:/# mysql --version
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

テーブル作成

CREATE TABLE idol_json(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `j` json
) DEFAULT CHARSET = utf8mb4
;

レコード作成

INSERT INTO idol_json(
    j
)
VALUES(
    JSON_OBJECT('name', 'sakuragi', 'height', '155')
),
(
    JSON_OBJECT('name', 'hachimiya', 'height', '157')
),
(
    JSON_OBJECT('name', 'kazano', 'height', '154')
),
(
    JSON_OBJECT('name', 'tsukioka', 'height', '165')
),
(
    JSON_OBJECT('name', 'yukoku', 'height', '160')
)
;

中身確認

SELECT
    *
FROM
    idol_json
;

↓

+----+----------------------------------------+
| id | j                                      |
+----+----------------------------------------+
|  1 | {"name": "sakuragi", "height": "155"}  |
|  2 | {"name": "hachimiya", "height": "157"} |
|  3 | {"name": "kazano", "height": "154"}    |
|  4 | {"name": "tsukioka", "height": "165"}  |
|  5 | {"name": "yukoku", "height": "160"}    |
+----+----------------------------------------+

JSON_EXTRACT

SELECT
    id,
    JSON_EXTRACT(j, '$.name')
FROM
    idol_json
;

↓

+----+---------------------------+
| id | JSON_EXTRACT(j, '$.name') |
+----+---------------------------+
|  1 | "sakuragi"                |
|  2 | "hachimiya"               |
|  3 | "kazano"                  |
|  4 | "tsukioka"                |
|  5 | "yukoku"                  |
+----+---------------------------+

JSON_EXTRACTで指定するキーが存在しない場合はNULLが返る。

+----+--------------------------+
| id | JSON_EXTRACT(j, '$.nae') |
+----+--------------------------+
|  1 | NULL                     |
|  2 | NULL                     |
|  3 | NULL                     |
|  4 | NULL                     |
|  5 | NULL                     |
+----+--------------------------+

JSON_UNQUOTE

SELECT
    id,
    JSON_UNQUOTE(JSON_EXTRACT(j, '$.name'))
FROM
    idol_json
;

↓

+----+-----------------------------------------+
| id | JSON_UNQUOTE(JSON_EXTRACT(j, '$.name')) |
+----+-----------------------------------------+
|  1 | sakuragi                                |
|  2 | hachimiya                               |
|  3 | kazano                                  |
|  4 | tsukioka                                |
|  5 | yukoku                                  |
+----+-----------------------------------------+
SELECT
    id,
    j - > > '$.name'
FROM
    idol_json
;

上記でも同様の結果を得られる。

WHERE句を使って絞り込み

SELECT
    *
FROM
    idol_json
WHERE
    j - > > '$.name' LIKE 'k%'
;

↓

+----+-------------------------------------+
| id | j                                   |
+----+-------------------------------------+
|  3 | {"name": "kazano", "height": "154"} |
+----+-------------------------------------+

WHERE句でのカラムエイリアスへの参照は非許可

SELECT
    j - > > '$.name' AS name
FROM
    idol_json
WHERE
    name LIKE 'k%'
;

↓

ERROR 1054 (42S22): Unknown column 'name' in 'where clause'

標準 SQL では、WHERE 句でのカラムエイリアスへの参照は許可されません。 WHERE 句が評価されるときに、カラム値がまだ判別されていない場合があるため、この制限が課されています。
https://dev.mysql.com/doc/refman/8.0/ja/problems-with-alias.html

JSON絡みの問題ではなくて、MySQL全体でWHERE句でカラムエイリアスは使えないみたい。

参考