MySQLのJSONデータ型カラムを検索する方法

MySQLにはJSONデータ型というものがあります。

MySQL は、JSON (JavaScript Object Notation) ドキュメント内のデータへの効率的なアクセスを可能にする、RFC 7159 によって定義されたネイティブ JSON データ型をサポートしています。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.5 JSON データ型

JSONデータ型は検索用の関数があります。何も知らず・・・likeで検索してしまいました。そんな無知を改善するため「JSONデータ型カラムを検索する方法」を書き溜めます。

配列内の要素の値で検索

検索条件にはJSON_CONTAINSを使います。

JSON_CONTAINS(target, candidate[, path])
特定の candidate JSON ドキュメントが target JSON ドキュメント内に含まれているかどうか、または path 引数が指定されているかどうか (候補がターゲット内の特定のパスで見つかったかどうか) を 1 または 0 を返して示します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.18.3 JSON 値を検索する関数

「-> 演算子」を使ってJSONの要素を指定し、配列の中身は「[*]」で参照します。

column->path
-> 演算子は、左側のカラム識別子と JSON ドキュメント (カラム値) に対して評価される右側の JSON パスの 2 つの引数で使用される JSON_EXTRACT() 関数のエイリアスとして機能します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.18.3 JSON 値を検索する関数

検索結果の列にはJSON_EXTRACTを使って見たい要素を抜き出します。

JSON_EXTRACT(json_doc, path[, path] ...)
path 引数に一致するドキュメントの一部から選択された JSON ドキュメントからデータを返します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.18.3 JSON 値を検索する関数

[json]というjsonデータ型のカラムにこんなJSONが格納されている

{
    "no": "09xxxxx00000",
    "syurui": [
        {
            "seq": 1,
            "name": "001",
            "syuruiCd": "030"
        },
        {
            "seq": 1,
            "name": "004",
            "syuruiCd": "010"
        },
        {
            "seq": 1,
            "name": "006",
            "syuruiCd": "090"
        }
    ]
}

配列内のsyuruiCdが「030」を検索するSQL

-- 検索値の「'"030"'」を「'030'」にするとエラーになるのでご注意ください(Invalid JSON text in argument 2 to function json_contains: "The document root must not follow by other values." at position 1.)
select JSON_EXTRACT(json,'$.syurui[*].syuruiCd') from table_name where JSON_CONTAINS(json->'$.syurui[*].syuruiCd','"030"');

検索結果は以下のようになります。

$.syurui[*].syuruiCd
["002", "002", "011", "030", "030"]