MySQL JSONで格納した値を検索したい（JSON_CONTAINS()、JSON_CONTAINS_PATH()、JSON_EXTRACT()）

JSONで格納した値を対象に検索したくなった・・・

作成日:2022-03-02, 更新日:2022-10-12

基本

戻り値 めも
JSON_CONTAINS() 0 or 1 JSONをデコードしたときの配列の値が存在するか確認
JSON_CONTAINS_PATH() 0 or 1 JSONをデコードしたときの配列のキーが存在するか確認
JSON_EXTRACT() 指定された値 JSONをデコードしたときの配列の値

以下、サンプルは取得対象のJSONが格納されているカラム名が「colm_json」の場合

取得: JSON_EXTRACT()

連想配列のとき

{"id":"23","title":"オープン"}

上記のようなJSONの場合

JSON_EXTRACT(`colm_json`, "$.title")

多次元の連想配列のとき

[
  {"id":"23","title":"オープン"},
  {"id":"24","title":"クローズ"},
  {"id":"25","title":"ラスト"}
]

上記のようなJSONの場合

JSON_EXTRACT(`colm_json`, "$[*].title")

取得された値の「"」: TRIM()

取得したが値が数値なら良いんだけど、文字の場合「"」でくくられている

▼「"」を削除

trim(BOTH '"' FROM JSON_EXTRACT(`colm_json`, "$.title"))

json形式じゃないとき

JSON形式じゃないのに「JSON_EXTRACT()」とかするとwaringがでてくる
その対応はcase文であらかじめjson形式か確認しておけばOK

「カラム: colm_json」に何かしたい

CASE WHEN JSON_VALID(`colm_json`) 
THEN 
  JSON_EXTRACT(`colm_json`, "$[*].title") 
ELSE 
  null
END

where句で使うとき

where 1
and (
  CASE WHEN JSON_VALID(`colm_json`) 
  THEN 
    JSON_EXTRACT(`colm_json`, "$[*].title") 
  ELSE 
    null
  END
)  like "%〇〇〇%"

select句で使うとき

select句で使うときは下記あたりを考慮しなくちゃいけないかもしれない

  • 取得対象のレコードに対象のカラムが含まれるかチェック。含まれていてもNULLかチェック
  • 対象のカラムがJSONかチェック
  • 対象のカラムのJSONに対象のキーが存在するかチェック
  • 対象のカラムのJSONに対象のキーの値がNULL、空白かチェック
  • 対象のカラムのJSONに対象のキーの値を出力

流れ

取得対象のレコードに対象のカラムが含まれるかチェック。含まれていてもNULLかチェック

CASE
WHEN `colm_json` is null THEN "NOT FOUND"
ELSE ～省略１～
END

対象のカラムがJSONかチェック

上記の「省略１」のトコ

if(JSON_VALID(`colm_json`), ～省略２～, "NOT JSON")

対象のカラムのJSONに対象のキーが存在するかチェック

上記の「省略２」のトコ

IF(JSON_CONTAINS_PATH(`colm_json`, "all", "$.title"), ～省略３～, "NOT HAVE")

対象のカラムのJSONに対象のキーの値がNULL、空白かチェック

上記の「省略３」のトコ

CASE ～省略４～
WHEN ""   THEN "IS EMPTY" # 値が空白
WHEN NULL THEN "IS NULL"  # 値がNULL
ELSE ～省略４～
END

対象のカラムのJSONに対象のキーの値を出力

上記の「省略４」のトコ

TRIM(BOTH '"' FROM JSON_EXTRACT(`colm_json`, "$.title"))

サンプル（※「カラム: colm_json」がJSONで対象は「キー: title」のとき）

合体させたやつ

(
    CASE

    # 「カラム:colm_json」が無い or nullのとき
    WHEN `colm_json` is null THEN "NOT FOUND"

    # 「カラム:colm_json」があるとき
    ELSE (
        IF(
            JSON_VALID(`colm_json`), # JSONの書式かチェック

            # 真のとき: JSONの書式である
            IF(
                JSON_CONTAINS_PATH(`colm_json`, "all", "$.title"), # titleが含まれるかチェック

                # 真のとき: titleが含まれる
                (
                    CASE TRIM(BOTH '"' FROM JSON_EXTRACT(`colm_json`, "$.title")) # colm_jsonにtitleの値で処理分岐
                    WHEN ""   THEN "IS EMPTY" # 値が空白
                    WHEN NULL THEN "IS NULL"  # 値がNULL
                    ELSE TRIM(BOTH '"' FROM JSON_EXTRACT(`colm_json`, "$.title")) # 値があるので最初と最後の「"」を削除
                    END
                ),

                # 偽のとき: titleが含まれない
                "NOT HAVE"
            ),

            # 偽のとき: JSONの書式ではない
            "NOT JSON"
        )
    )
    END
)

※これだと不十分な気もする・・・都度、状況を見て対応するのが無難

where句のサンプル

インデックスも張れないらしいので、件数や内容次第では時間はかかるっぽい
・・・インデックスは張れるんじゃないかな？　意味は無さそうだけど。

値の方が存在する・しないで、取得するWHERE句

▼「カラム: colm_json」に格納されているJSONの例

[
  {"id":"23","title":"オープン","at":["10:00","10:00","20:00"]},
  {"id":"24","title":"クローズ","at":["19:00","20:00"]},
  {"id":"25","title":"ラスト","at":["18:30","19:30","20:00"]}
]
where JSON_CONTAINS(`colm_json`, '"ラスト"', '$[2].title')
 取得できる 
where JSON_CONTAINS(`colm_json`, 'ラスト', '$[2].title')
 取得できない 「"」で囲まないとダメ（※trim()すればOK） 
where JSON_CONTAINS(`colm_json`, '"ラス%"', '$[2].title')
 取得できない like検索にはなってくれない 
where JSON_CONTAINS(`colm_json`, '"ラスト"', '$[0].title')
 取得できない 「$[0].title」は「オープン」だから 
where !JSON_CONTAINS(`colm_json`, '"ラスト"', '$[0].title')
 取得できる 「$[0].title」は「ラスト」じゃないから 
where !JSON_CONTAINS(`colm_json`, '"ラスト"', '$[*].title')
 エラー 「JSON_CONTAINS()」ではワイルドカードはダメっぽい 
where !JSON_CONTAINS(`colm_json`, '"ラスト"', '$[0].*')
 エラー 「JSON_CONTAINS()」ではワイルドカードはダメっぽい 
where JSON_CONTAINS(`colm_json`, '["ラスト"]', '$[2].title')
 取得できない 「$[2].title」は配列じゃない 
where JSON_CONTAINS(`colm_json`, '["18:30"]', '$[2].at')
 取得できる 「$[2].at」の配列の中に「18:30」が含まれる 
where JSON_CONTAINS(`colm_json`, '["18:00"]', '$[2].at')
 取得できない 「$[2].at」の配列の中に「18:00」は含まれない 
where JSON_CONTAINS(`colm_json`, '["18:30","19:30"]', '$[2].at')
 取得できる 「$[2].at」の配列の中に「18:30」と「19:30」の両方が含まれる 
where JSON_CONTAINS(`colm_json`, '["18:30","19:00"]', '$[2].at')
 取得できない 「$[2].at」の配列の中に「19:00」は含まれない

※「where JSON_CONTAINS(～～)」は「where JSON_CONTAINS(～～)=1」としても同じ
※「where !JSON_CONTAINS(～～)」は「where JSON_CONTAINS(～～)!=1」としても同じ
※「where !JSON_CONTAINS(～～)」は「where JSON_CONTAINS(～～)=0」としても同じ

キーの方が存在する・しないで、取得するWHERE句

▼「カラム: colm_json」に格納されているJSONの例

[
  {"id":"23","title":"オープン","at":["10:00","10:00","20:00"]},
  {"id":"24","title":"クローズ","at":["19:00","20:00"]},
  {"id":"25","title":"ラスト","at":["18:30","19:30","20:00"]}
]
where JSON_CONTAINS_PATH(`colm_json`, 'all', '$[2].title', '$[2].at')
 取得できる 「$[2].title」と「$[2].at」の両方が存在する 
where JSON_CONTAINS_PATH(`colm_json`, 'all', '$[2].name', '$[2].at')
 取得できない 「$[2].name」が存在しない 
where JSON_CONTAINS_PATH(`colm_json`, 'one', '$[2].title', '$[2].at')
 取得できる 
where JSON_CONTAINS_PATH(`colm_json`, 'one', '$[2].name', '$[2].at')
 取得できる 「$[2].name」が存在しないけど、「$[2].at」は存在する 
where JSON_CONTAINS_PATH(`colm_json`, 'one', '$[2].name', '$[2].ad')
 取得できる 「$[2].name」も「$[2].ad」も存在しない

値の方でlike検索したい

▼「カラム: colm_json」に格納されているJSONの例

[
  {"id":"23","title":"オープン","at":["10:00","10:00","20:00"]},
  {"id":"24","title":"クローズ","at":["19:00","20:00"]},
  {"id":"25","title":"ラスト","at":["18:30","19:30","20:00"]}
]
where JSON_EXTRACT(`colm_json`, "$[0].title") like "%プン%"
 取得できる 「0番目のtitle」を指定 
where JSON_EXTRACT(`colm_json`, "$[*].title") like "%プン%"
 取得できる 「すべてのtitle」を指定 
where JSON_EXTRACT(`colm_json`, "$[*].*") like "%プン%"
 取得できる 「すべて」を指定 
where JSON_EXTRACT(`colm_json`, "$[*]") like "%titl%"
 取得できる キーまで対象になる 
where JSON_EXTRACT(`colm_json`, "$[0].title") = "オープン"
 取得できない 「=」はダメっぽい。「JSON_CONTAINS()」を使う感じ 
where JSON_EXTRACT(`colm_json`, "$[0].title") = '"オープン"'
 取得できない 「JSON_CONTAINS()」みたいに「'」で囲ってもダメ

多次元配列じゃないとき

▼「カラム: colm_json」に格納されているJSONの例

["23","オープン"]
where JSON_EXTRACT(`colm_json`, "$[*]") like "%オープン%"
 取得できる

関連項目