Rails MySQL query json column

rails_mysql_json.md

Rails, MySQL, and JSON

---

Query a JSON attribute / column

Given a model named Preference and that model has a json column named settings.

example data

 [
    {
      "type" => "boolean"
    }
  ],
  [
    {
      "type" => "array",
      "dropdown_id" => 1
    }
  ]

You can query using the example below, to get a preference that has a setting type of array.

> PreferenceValue.where.not("JSON_EXTRACT(preference_values.settings, '$.value') = 'true'")
> PreferenceValue.where("JSON_CONTAINS_PATH(preference_values.settings, 'one', '$.dropdown_value_ids')")

---

MySQL

When using an "array" column in MySQL (i.e. a JSON column as just an array) to be able to search this. Do the following.

ProfessionMapping.where("JSON_SEARCH ( `profession_mappings`.`publication_professions`, 'all', '#{profession_filter}', NULL ) IS NOT NULL")

BackgroundJob.where("JSON_EXTRACT( `background_jobs`.`args`, '$[0]') = #{api_order_id}")