MySQLのJSONのkeyに一致するオブジェクトのvalueを取得したい：EAV

はじめに

カスタムフィールドみたいな、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 コンテナで動作確認します。

1. 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
   

2. サンプルデータ作成

   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"}]}');
   

3. 目的のコマンド実行（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;
   

4. 実行結果

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

5. コンテナ破棄（後片付け）

   exit
   exit
   docker stop db
   docker rm db
   

おわりに

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