Rails how to make a scope to filter for a json key in a column-CodePudding

In one of my columns I have a json object like this:

{"name":"test", "age":'12'}

This data is in my column. I want to make a scope to select for all records that have age: 12. How can I make a rails scope to filter out based on a json key in this column. I was trying something like

scope :age_filter, -> {where(column["age"]: 12)}

however this results in a syntax error; im not sure how else I should go about a secenario like this since the value in the column is a json object rather than a single value.

CodePudding user response：

You have to query JSON using your database's JSON operators.

For example, if this is a Postgres JSON column called data...

scope :age_filter, ->{ where("data->>'age' = ?", 12) }

Alternatively, you can use jsonb_accessor.

scope :age_filter, ->{ data_where(age: 12) }

Note: I would advise using regular columns where possible and only use JSON for free-form data. While jsonb-accessor helps, Ruby on Rails is not well integrated with JSON and traditional columns will be less hassle. In your example above, age should be a normal integer column.