⛳

MySQLでのJSON検索メモ

2024/04/11に公開

MySQL

SQL

JSON

tech

はじめに

MySQLでJSONカラムを検索する場合のメモです

JSON関数

名前	説明
JSON_EXTRACT(json_doc, path[, path] ...)	データを返す
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)	パスのデータが含まれているか
JSON_CONTAINS(target, candidate[, path])	特定のオブジェクトが含まれているかどうか

実行例

table_nameテーブルのattributeカラム

{
  "items":[
    {
      "a": "foo",
      "b": "bar"
    },
    {
      "a": "baz"
    },
  ]
}

例1）値をまとめて取得

SELECT JSON_EXTRACT(`attribute`, "$.items[*].a") FROM table_name;

例2）あるキーが全部になかったら取得

SELECT * FROM table_name
WHERE JSON_CONTAINS_PATH(attributes, 'all', '$.items[*].b');

例3）あるキーがいずれかになかったら取得
（配列の最大は2の前提、もうちょっとうまく書けそうな気がする。。。）

SELECT * FROM table_name
WHERE (
  CASE
    WHEN (
      JSON_CONTAINS_PATH(attributes, 'all', '$.items[0]') = 0
    ) THEN 1
    WHEN (
      JSON_CONTAINS_PATH(attributes, 'all', '$.items[0]') = 1 and
      JSON_CONTAINS_PATH(attributes, 'all', '$.items[0].b') = 0
    ) THEN 1
    WHEN (
      JSON_CONTAINS_PATH(attributes, 'all', '$.items[1]') = 1 and
      JSON_CONTAINS_PATH(attributes, 'all', '$.items[1].b') = 0
    ) THEN 1
    ELSE 0
  END
)

例4）キーの値を指定して取得
candidateを""で囲む必要あり

SELECT * FROM table_name
WHERE JSON_CONTAINS(attributes, '"foo"', '$.items[0].a');

※JSON_CONTAINSはワイルドカードは使えません（'$.items[*].a'という指定はできない）

MySQLでJSON型カラムを使う？

データベースの設計として、JSON型はできるだけ使わない方が良いとされています
（そもそも正規化されていません）

以下の書籍にもある、SQLアンチパターンの「ジェイウォーク」が相当しますが、
値の検索、集約、結合などでデメリットが発生し、クエリも複雑化します

どんな時に使うか

参照だけの場合
- 検索や編集しない
- 外部結合しない
- 集約しない
SQLアンチパターンの「EAV」対策→多少はまし
シリアライズしたデータの保存
正規化しにくいデータでも信頼性や可搬性からMySQLを選択したい時
ログなどのとりあえず保存しておいて後で集計するようなデータ
保存したデータを後で使う（DBの更新ログからデータ移行など）
~~仕様が決まらないのでとりあえず開発するため~~

JSONカラムで検索したいとなった時は、設計を見直すと良いかもしれません

はじめに

JSON関数

実行例

MySQLでJSON型カラムを使う？

Discussion