current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
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 9 down vote favorite
7

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

3 Answers 3

up vote 20 down vote accepted

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%")

Update I will be writing a blog post on this very shortly and will put the link here.

share|improve this answer
    
There's a helpful answer here that deals with multiple levels: stackoverflow.com/questions/23609331/… and the logic behind it here: postgresql.org/docs/9.3/static/functions-json.html –  Ameet Wadhwani Nov 29 '14 at 19:43
    
Thanks @AmeetWadhwani I am probably going to write a blog post on this topic. Stay tuned for other coming across this answer! –  Mohamed El Mahallawy Dec 1 '14 at 20:38
    
@MohamedElMahallawy Can you help with this question: stackoverflow.com/questions/27697489/…. I am not able to figure out where query for a row which has an array of json elements without a key. –  Aravind Dec 30 '14 at 11:17
up vote 2 down vote

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
1  
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 '14 at 17:10
up vote 1 down vote

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).

share|improve this answer

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.