I'm having an issue searching a json record with an array of strings. I keep getting PG::DatatypeMismatch: ERROR: type errors
I've got an array type like this:
popup.rb
add_column :popups, :popup_scope, :json, default: {}, null: false
with the data
popup_scope:
=> ["collection","2","5","9"]
or
popup_scope:
=> ["vendor","Alchemists Inc"]
I really want to find out if the first value is collection or vendor, then I want to search the resulting group with an array of strings such as ["2", "5"]. I've tried:
Popup.where("'collection' = ANY (popup_scope)").where(popup_scope: collection_local_ids)
where collection_local_ids = ["2"] which gives errors, and also:
Popup.where("popup_scope ->> 'collection'")
I've also tried many of the examples here: Postgres JSON data type Rails query
Please help! Not sure how to get the values from this. Let me know if you would like more information.
Thanks,
Pete
Edit:
So I've got a non-optimal solution that works for me. I am still looking for something more elegant. Basically I've changed the data type from json to a text array
add_column :popups, :popup_scope, :text, array: true, default: []
Then I find the collection/vendor string and loop through all the returned values checking for common elements
collection_local_ids = ["2"]
popups_with_collection = []
shop.popups.where("'collection' = ANY (popup_scope)").each do |c|
if (c.popup_scope & collection_local_ids).empty?
# Nothing in common
else
# Common elements
popups_with_collection << c
end
end
Not happy with this but running with it as it works.