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"}
     ]
}

Ah, you want to write it in Rails/ActiveRecord syntax. In that case ... runs rapidly away. — Craig Ringer
– Craig Ringer, Commented Mar 27, 2014 at 4:35

user664833 · Accepted Answer · 2020-04-27 19:49:44Z

173

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

edited Apr 27, 2020 at 19:49

user664833

19.7k20 gold badges97 silver badges151 bronze badges

answered Jun 28, 2014 at 5:34

Mohamed El Mahallawy

14k13 gold badges56 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ameet Wadhwani Over a year ago

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

Mohamed El Mahallawy Over a year ago

Thanks @AmeetWadhwani I am probably going to write a blog post on this topic. Stay tuned for other coming across this answer!

Aravind Over a year ago

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

Dennis Over a year ago

When searching within an array, what does the latter 1 mean in {data1,1}?

PJSCopeland Over a year ago

Record.where("column_data ->> 'array' LIKE '%hello%' ") is OK. Record.where("column_data ->> 'array' LIKE '%#{user_input}%' ") is bad. That's where you need the SQL parameter: Record.where("column_data ->> 'array' LIKE '%?%' ", user_input)

|

guapolo · Accepted Answer · 2014-10-01 03:02:51Z

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

Md. Hafizul Islam · Accepted Answer · 2022-09-07 09:52:07Z

4

JSON filtering in Rails

Event.create( payload: [{ "name": 'Jack', "age": 12 },
                                 { "name": 'John', "age": 13 },
                                 { "name": 'Dohn', "age": 24 }]

Event.where('payload @> ?', '[{"age": 12}]')
#You can also filter by name key
Event.where('payload @> ?', '[{"name": "John"}]')
#You can also filter by {"name":"Jack", "age":12}
Event.where('payload @> ?', {"name":"Jack", "age":12}.to_json)

You can find more about this here

edited Sep 7, 2022 at 9:52

answered Sep 7, 2022 at 9:18

Md. Hafizul Islam

513 bronze badges

Comments

Meekohi · Accepted Answer · 2014-03-27 19:47:51Z

3

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.

answered Mar 27, 2014 at 19:47

Meekohi

11k6 gold badges52 silver badges60 bronze badges

1 Comment

JacobEvelyn Over a year ago

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;

Collectives™ on Stack Overflow

Postgres JSON data type Rails query

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related