In my Rails 5 app I have a Postgres table with a JSON column. I want to query for a term in all values, not only a specific one.

For example following JSON:

{
  "Cidade": "Rio de janeiro ",
  "Estado": "Rj "
}

I would like to use Rio or de or Janeiro or Rj to select the row in the table without specifying the key.

CodePudding user response：

Let's assume that your table which contains json field is named records and your field name is data then your SQL query will be:

SELECT * FROM records 
JOIN json_each_text(records.data) r ON true
WHERE value LIKE '%Rio%' OR value LIKE '