Is it possible to use postgresql 9.3 json type in associations (belongs_to) as foreign_key
json, postgresql, ruby-on-rails
Solution
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
Problem
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`?