はじめに
MySQL 5.7からJSON型カラムを扱えるようになりました。
この記事ではActiveRecordでJSON型カラムを扱う際に引っかかったポイントを3つまとめています。
実行環境
Rails 6.1.3.1
MySQL 8.0.28
テーブル定義
記事で示すサンプルコードは、次のように定義したUserモデルを用いて実行しています。
db/schema.rb
create_table "users", charset: "utf8mb3", force: :cascade do |t|
t.string "name"
t.json "profile"
end
1. 保存時にto_jsonは不要
JSON型カラムにはハッシュや配列をそのまま渡します。
User.create(
name: 'サンプルユーザー1',
profile: {
age: 20,
hometown: '東京'
}
)
=> #<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20,"hometown"=>"東京"}>
to_jsonしたものを渡すと、profileカラムの値が文字列として入るようになります。
このとき下で示したようなJSON_EXTRACTやJSON_CONTAINSによる検索がうまく動きません。
User.create(
name: 'サンプルユーザー2',
profile: {
age: 30,
hometown: '大阪'
}.to_json
)
=> #<User id: 2, name: "サンプルユーザー2", profile: "{\"age\":30,\"hometown\":\"大阪\"}">
2. JSONの中身はキーを文字列で指定して参照する
user = User.find(1)
=> #<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>
user.profile['age']
=> 20
次のようにシンボルでキーを指定すると値を取得できません。
user = User.find(1)
=> #<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>
user.profile[:age]
=> nil
3. JSON_CONTAINSを用いる際は引用符に注意
JSON_EXTRACTやJSON_CONTAINSを使うと、JSONの中身を条件に用いた検索が可能になります。
JSON_EXTRACTを用いると次のように検索できます。
age = 20
User.where("JSON_EXTRACT(profile, '$.age') = ?", age)
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
hometown = '東京'
User.where("JSON_EXTRACT(profile, '$.hometown') = ?", hometown)
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
JSON_CONTAINSを用いる場合は引用符の使い方に注意が必要です。
検索に用いる値が整数のとき
値を引用符で囲う必要があります。
User.where("JSON_CONTAINS(profile, '20', '$.age')")
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
変数で渡す場合は文字列化すれば大丈夫です。
age = 20
User.where("JSON_CONTAINS(profile, ?, '$.age')", age.to_s)
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
整数のまま渡すとエラーになります。
User.where("JSON_CONTAINS(profile, 20, '$.age')")
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid data type for JSON data in argument 1 to function json_contains; a JSON string or JSON type is required.)
age = 20
User.where("JSON_CONTAINS(profile, ?, '$.age')", age)
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid data type for JSON data in argument 1 to function json_contains; a JSON string or JSON type is required.)
検索に用いる値が文字列のとき
値をダブルクォーテーションで囲い、それをさらに引用符で囲う必要があります。
User.where("JSON_CONTAINS(profile, '\"東京\"', '$.hometown')")
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
hometown = '東京'
User.where("JSON_CONTAINS(profile, ?, '$.hometown')", "\"#{hometown}\"")
=> #<ActiveRecord::Relation [#<User id: 1, name: "サンプルユーザー1", profile: {"age"=>20, "hometown"=>"東京"}>]>
引用符が足りない場合エラーになります。
User.where("JSON_CONTAINS(profile, '東京', '$.hometown')")
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.)
hometown = '東京'
User.where("JSON_CONTAINS(profile, ?, '$.hometown')", hometown)
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.)
hometown = '東京'
User.where("JSON_CONTAINS(profile, ?, '$.hometown')", "#{hometown}")
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.)
また二重に囲っていても内側がシングルクォーテーションだとエラーになります。
User.where("JSON_CONTAINS(profile, \"'東京'\", '$.hometown')")
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.)
hometown = '東京'
User.where("JSON_CONTAINS(profile, ?, '$.hometown')", "'#{hometown}'")
=> # ActiveRecord::StatementInvalid (Mysql2::Error: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.)