Rails 4 ActiveRecord: How to drill down to nested 3 layer record and see if an array contains string

Question

So I have a nested activerecord which contains an array of hashes. I am trying to get the country in an app I am making using a country code that is stored in one of the elements in the array.

the record is described:

user.rules.first.countries.first["country_code"]

user has_many rules,

rules contains a jsonb column called countries

countries is a jsonb array of hashes

at the moment I am iterating through all of them to find the record. e.g.

country_code_to_find = "US"
user.rules.each do |r|
  r.countries.each do |c|
    if c["country_code"] == "US"
      # Do some stuff
    end
  end
end

Is there a way I can access that country with a single line using a .where() or scope or something like that? I am using rails 4, activerecord and postgres.

score 1 · Answer 1 · edited May 23 '17 at 12:15

Without knowing more about the JSON structure, I'm not confident you can access "that country" with a single query, since a "country" is an element in an array. You can query for the Rule objects that contain the desired "country". Something like this might work

user.rules.where("
  countries @> '[{\"country_code\": \"US\"}]'
")

Depending on your business logic, it might be enough to know that this user has at least one rule with country=US.

country_code_to_find = "US"
if user.rules.where("countries @> '[{\"country_code\": \"#{country_code_to_find}\"}]'").exists?
  # Do some stuff
end

More on Postgres' JSONB functions.

These questions seem related, but are not Rails-specific:

Postgresql query array of objects in JSONB field.

Query for array elements inside JSON type

score 0 · Accepted Answer · answered Feb 03 '16 at 07:23

Using the answer from messenjah I was able to get a solution that worked. Had to find the index of the array so I could use it. To give some more information that messenjar was after here is the json:

countries: [{"code"=>"US", "name"=>"United States", "states"=>{"NY" => "New York"}}, {"code"=>"MX", "name"=>"Mexico", "states"=>{"YUC" => "Yucatán"}}]

Then to get an array of the states I used:

country_code = "MX"
rule = @user.rules.where("countries @> '[{\"code\": \"#{country_code}\"}]'").first    
country_index = rule.countries.index {|h| h["code"] == country_code  }
states = rule.countries[country_index]["states"]

Basically this get the index of the array of hashes that I want. Not sure if this is better or worse than what I was using to begin with. But it works. Happy to consider other answers if they can clean this up.

Rails 4 ActiveRecord: How to drill down to nested 3 layer record and see if an array contains string

2 Answers2