はじめに

カスタムフィールドみたいな、EAV（エンティティ、アトリビュート、バリュー）形式でデータを持っている JSON のキーでひっかけて、同階層にあるバリューの値を MySQL で取得したいケースがでて、ハマったので備忘録的に残します。

// JSON 型の値に保存されている Rascal の文字列を、`key` = 'name' を条件とした上で取得したい
{
    "fields": [
        {
            "key": "id",
            "value": "1"
        },
        {
            "key": "name",
            "value": "Rascal"
        },
        {
            "key": "category",
            "value": "raccoon"
        }
    ]
}

結論

これで取得できます。

SELECT
    JSON_UNQUOTE(
        JSON_EXTRACT(
            `custom_fields`.`custom_field`,
            REPLACE(
                JSON_UNQUOTE(
                    JSON_SEARCH(`custom_fields`.`custom_field`, 'one', 'name')
                ),
                'key',
                'value'
            )
        )
    ) AS 'Name'
FROM
    `custom_fields`
WHERE
    `custom_fields`.`id` = 1;

手順

Docker の MySQL コンテナで動作確認します。

MySQL コンテナの MySQL に入る

docker pull mysql:8.0
docker run --name db -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=study_mysql -d -p 3306:3306 mysql:8.0
docker exec -it db bash
# MySQL が起動する 20 秒くらい待ってから
mysql -u root -proot

サンプルデータ作成

use study_mysql;

CREATE TABLE IF NOT EXISTS `custom_fields` (
    `id` MEDIUMINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `custom_field` json DEFAULT NULL COMMENT 'custom field',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `custom_fields` VALUE
    (NULL, '{"fields":[{"key":"id","value":"1"},{"key":"name","value":"Rascal"},{"key":"category","value":"raccoon"}]}');

目的のコマンド実行（Rascal の文字列を、key = 'name' を条件として取得したい）

SELECT
    -- ダブルクォート除外
    -- e.g. Rascal
    JSON_UNQUOTE(
        -- value カラムの値を取得する
        -- e.g. "Rascal"
        JSON_EXTRACT(
            `custom_fields`.`custom_field`,
            -- value キーの値を取得するため、 JSON パスを value へ置換
            -- e.g. $.fields[1].value
            REPLACE(
                -- ダブルクォート除外
                -- e.g. $.items[1].key
                JSON_UNQUOTE(
                    -- custom_field カラムの値で、最初に name の値を持つ JSON パスを取得
                    -- e.g. "$.fields[1].key"
                    JSON_SEARCH(`custom_fields`.`custom_field`, 'one', 'name')
                ),
                'key',
                'value'
            )
        )
    ) AS 'Name'
FROM
    `custom_fields`
WHERE
    `custom_fields`.`id` = 1;

実行結果

+--------+
| Name   |
+--------+
| Rascal |
+--------+
1 row in set (0.00 sec)

コンテナ破棄（後片付け）
```
exit
exit
docker stop db
docker rm db
```

おわりに

今回の SQL は、MySQL 5.7 の VARCHAR に JSON のシリアライズ文字列が入っていた場合でも、同様に取得することができることを確認できています。
この記事が他のエンジニアの助けになれば幸いです。