current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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"}
     ]
}
share|improve this question
    
PostgreSQL version? –  Craig Ringer Mar 27 at 1:52
    
PostgrSQL 9.3 is what I am using –  Mohamed El Mahallawy Mar 27 at 4:11
    
OK, and did you look at the json operators like -> ? –  Craig Ringer Mar 27 at 4:28
    
Record.where("data -> 'status' = 200 ") not working –  Mohamed El Mahallawy Mar 27 at 4:32
    
Ah, you want to write it in Rails/ActiveRecord syntax. In that case ... runs rapidly away. –  Craig Ringer Mar 27 at 4:35
show 3 more comments

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
This doesn't work for me in Postgres 9.3.3 unless I cast the JSON result to text and then to an integer: SELECT * FROM users WHERE (data->'count')::text::integer > 500; –  JacobEvelyn Jun 7 at 17:10
add comment
up vote 2 down vote

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.

Postgres important rails commands:


Sort based on the Hstore data:
2.1.1 :022 > Post.order("data->'hello' DESC")

=> #<ActiveRecord::Relation [#<Post id: 4, created_at: "2014-04-16 01:05:49", updated_at: "2014-04-16 01:05:49", data: {"hi"=>"23", "hello"=>"22"}>, #<Post id: 3, created_at: "2014-04-16 01:05:37", updated_at: "2014-04-16 01:05:37", data: {"hi"=>"13", "hello"=>"21"}>, #<Post id: 2, created_at: "2014-04-16 01:05:28", updated_at: "2014-04-16 01:05:28", data: {"hi"=>"3", "hello"=>"2"}>, #<Post id: 1, created_at: "2014-04-16 01:05:05", updated_at: "2014-04-16 01:05:05", data: {"hi"=>"2", "hello"=>"1"}>]> 



Where inside a JSON object:
Record.where("data ->> 'likelihood' = '0.89'")

Searching nested json object:
2.1.1 :130 > r.column_data
 => {"data1"=>[1, 2, 3], "data2"=>"data2-3", "array"=>[{"hello"=>1}, {"hi"=>2}], "nest"=>{"nest1"=>"yes"}} 

2.1.1 :130 > Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ")

Searching within array:
Record.where("column_data #>> '{data1,1}' = '2' ")


Searching 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%' ")
This is advised against in rails, use below:
Record.where("column_data ->> 'array' LIKE ?", "%hello%")
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.