1

I have a table with a field of type 'json', which I am using to store array of hashes.

This is my what my schema looks like:

create_table "recommendations", force: true do |t|
    t.text     "message"
    t.json     "vendors",               default: {}
    t.datetime "created_at"
    t.datetime "updated_at"
end

This is my sample data:

>> Recommendation.last.vendors
[{"name" => "Alphabeta"}, {"name" => "Gamma"}]

How do I get all the recommendations which includes or has vendor named "Gamma"?

I tried this

>> Recommendation.where("vendors ->> 'name' = 'Gamma'").count

but doesn't work.

UPDATE: I found a way from answers here Query for element of array in JSON column

Here it is:

s = "SELECT * FROM recommendations t, json_array_elements(t.vendors) AS elem WHERE  elem->>'name' = 'Gamma';"
ActiveRecord::Base.connection.execute(s).to_a
Community
  • 1
  • 1
roxxypoxxy
  • 2,973
  • 1
  • 21
  • 28
  • 2
    Since that data is in an array, it looks like you need to do a little more work to get the data out, take a look at this answer: http://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type – Nick Veys May 14 '15 at 15:07
  • I could not derive any solution from there. I am actually storing array in 'vendor' column whereas they have json data in the column. – roxxypoxxy May 14 '15 at 16:14
  • Have you tried querying it using an admin tool to figure out exactly what query you need to write? – Nick Veys May 14 '15 at 16:17
  • I used rails console. – roxxypoxxy May 14 '15 at 16:25
  • @roxxypoxxy you need to look closer at the question/answers in http://stackoverflow.com/q/22736742/152786 although you're right that their top level object being stored is an object where yours is an array, they are actually trying to query an array within that, just like you - I'm voting to close your question as a duplicate of it. – smathy May 14 '15 at 17:03
  • Not that solution but this http://stackoverflow.com/questions/19568123/query-for-element-of-array-in-json-column/19868697#19868697 worked. Can you please update the duplicate question with this one. – roxxypoxxy May 14 '15 at 17:34

0 Answers0