Postgres JSON data type Rails query

Question

I am using Postgres' json data type but want to do a query/ordering with data that is nested within the json.

I want to order or query with .where on the json data type. For example, I want to query for users that have a follower count > 500 or I want to order by follower or following count.

Thanks!

Example:

model User  data: {      "photos"=>[        {"type"=>"facebook", "type_id"=>"facebook", "type_name"=>"Facebook", "url"=>"facebook.com"}       ],       "social_profiles"=>[          {"type"=>"vimeo", "type_id"=>"vimeo", "type_name"=>"Vimeo", "url"=>"http://vimeo.com/", "username"=>"v", "id"=>"1"},          {"bio"=>"I am not a person, but a series of plants", "followers"=>1500, "following"=>240, "type"=>"twitter", "type_id"=>"twitter", "type_name"=>"Twitter", "url"=>"http://www.twitter.com/", "username"=>"123", "id"=>"123"}      ] } 

Answer 1

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%' ") # bad Record.where("column_data ->> 'array' LIKE ?", "%hello%") # good 

Answer 2

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

# db/migrate/20131220144913_create_events.rb create_table :events do |t|   t.json 'payload' end  # app/models/event.rb class Event < ActiveRecord::Base end  # Usage Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]})  event = Event.first event.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 text Event.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).