Postgres JSON data type Rails query

For any who stumbles upon this. I have come up with a list of queries using ActiveRecord and Postgres' JSON data type. Feel free to edit this to make it more clear.

Documentation to the JSON operators used below: https://www.postgresql.org/docs/current/functions-json.html.

# Sort based on the Hstore data:Post.order("data->'hello' DESC")=> #<ActiveRecord::Relation [    #<Post id: 4, data: {"hi"=>"23", "hello"=>"22"}>,     #<Post id: 3, data: {"hi"=>"13", "hello"=>"21"}>,     #<Post id: 2, data: {"hi"=>"3", "hello"=>"2"}>,     #<Post id: 1, data: {"hi"=>"2", "hello"=>"1"}>]> # Where inside a JSON object:Record.where("data ->> 'likelihood' = '0.89'")# Example json object:r.column_data=> {"data1"=>[1, 2, 3],     "data2"=>"data2-3",     "array"=>[{"hello"=>1}, {"hi"=>2}],     "nest"=>{"nest1"=>"yes"}} # Nested search:Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ")# Search within array:Record.where("column_data #>> '{data1,1}' = '2' ")# Search within a value that's an array:Record.where("column_data #> '{array,0}' ->> 'hello' = '1' ")# this only find for one element of the array. # All elements:Record.where("column_data ->> 'array' LIKE '%hello%' ") # badRecord.where("column_data ->> 'array' LIKE ?", "%hello%") # good

According to this http://edgeguides.rubyonrails.org/active_record_postgresql.html#jsonthere's a difference in using -> and ->>:

# db/migrate/20131220144913_create_events.rbcreate_table :events do |t|  t.json 'payload'end# app/models/event.rbclass Event < ActiveRecord::Baseend# UsageEvent.create(payload: { kind: "user_renamed", change: ["jack", "john"]})event = Event.firstevent.payload # => {"kind"=>"user_renamed", "change"=>["jack", "john"]}## Query based on JSON document# The -> operator returns the original JSON type (which might be an object), whereas ->> returns textEvent.where("payload->>'kind' = ?", "user_renamed")

So you should try Record.where("data ->> 'status' = 200 ") or the operator that suits your query (http://www.postgresql.org/docs/current/static/functions-json.html).

Your question doesn't seem to correspond to the data you've shown, but if your table is named users and data is a field in that table with JSON like {count:123}, then the query

SELECT * WHERE data->'count' > 500 FROM users

will work. Take a look at your database schema to make sure you understand the layout and check that the query works before complicating it with Rails conventions.