Is it possible to use postgresql 9.3 json type in associations (belongs_to) as foreign_key

Question

I have ruby on rails 4.0 and postgresql 9.3.

I have two models:

class Person < ActiveRecord::Base
       belongs_to :address_city, :class_name =>'City', :foreign_key => "address['city_id']"
end

class City < ActiveRecord::Base
   has_many: :address_cities, :class => "Person", :foreign_key => "address['city_id']"
end

Field address in table Person is of JSON type, and city_id is one of possible keys in this JSON field.

So, can I make association between two models to use like Person.first.address_city?

Answer 1

Rails by default uses the foreign_key in the belongs_to side to do a SQL Join between the two associated tables. So it does not go inside your JSON field looking for the foreign_key values.

What you could do is build an Association Extension to look inside your json field. You can look here in the rails association guides on how to do that: http://guides.rubyonrails.org/association_basics.html#association-extensions

In my opinion this is against rails conventions and you should not do that. Instead you could create a new column named city_id in the persons table. Then you do a job that iterates the persons table to extract the city_id from the json and update the city_id column. After this you could do a normal 1 to many association between the persons and cities table. If I where you I would also extract other field I may need. Then you can add indexes in the database for speed.

Update:

In Rails 4.2 and Postgres you can put json content directly in a column and then you can query it easily using the Postgres json query syntax in rails.

So I think you could build that Association Extension that could look inside the json content although I would not recommend that because is against conventions.

References:

Example using json queries whith rails models: http://robertbeene.com/rails-4-2-and-postgresql-9-4/

Active Record json datatype: http://edgeguides.rubyonrails.org/active_record_postgresql.html#json

Postgres json query syntax: http://www.postgresql.org/docs/9.4/static/functions-json.html