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.

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.

share|improve this question

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.