I know that Postgres JSON data type has been released more than a year ago, but I just recently had the opportunity to use this feature on a Rails project.
I’ve been asked to develop an app that allow users to check into nearby event.
One of the requests was to track whether the user location was inaccurate or whether the user region differed from the event region. At the beginning I was not aware of exactly what information was required, so I decided to create a json column in my checkins table called report
. Here’s my checkins table structure:
create_table "check_ins", force: :cascade do |t|
t.integer "user_id", null: false
t.integer "event_id", null: false
t.json "report", default: {}, null: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
add_index "check_ins", ["event_id"], name: "index_check_ins_on_event_id", using: :btree
add_index "check_ins", ["user_id", "event_id"], name: "index_check_ins_on_user_id_and_event_id", using: :btree
add_index "check_ins", ["user_id"], name: "index_check_ins_on_user_id", using: :btree
The report
column would contain a list of check-in status {:status_type => 'Message for status type', :other_status_type => "Message for other status type"}
that are stored only when some constraints are met.
My decision to store report data as JSON turned out to be the right one when the client decided to include some new tracking information in the check-in record. With this dynamic attribute I was able to implement this new feature very quickly without problems.
JSON attributes type not only allow schemaless data structures but also can be queried with ease. For example:
anna = User.create name: 'Anna'
paul = User.create name: 'Paul'
john = User.create name: 'John'
party = Event.create name: 'Big party', starts_at: 2.days.from_now, ends_at: 3.days.from_now
CheckIn.create user: anna, event: party, report: {with_soda: 'Participant brings soda'}
CheckIn.create user: paul, event: party, report: {without_mask: 'Participant is not masquerade', bring_friends: false}
CheckIn.create user: john, event: party, report: {without_mask: 'Participant is not masquerade', with_soda: 'Participant brings soda', bring_friends: true}
CheckIn.where("(report ->> 'with_soda')::text IS NOT NULL")
[#<CheckIn id: 1, user_id: 1, event_id: 1, report: {"with_soda"=>"Participant brings soda"}, created_at: "2015-11-05 13:29:04", updated_at: "2015-11-05 13:29:04">, #<CheckIn id: 3, user_id: 3, event_id: 1, report: {"without_mask"=>"Participant is not masquerade", "with_soda"=>"Participant brings soda", "bring_friends"=>true}, created_at: "2015-11-05 13:29:05", updated_at: "2015-11-05 13:29:05">]
Or you can search for a specific value of a key:
CheckIn.where("report ->> 'bring_friends' = 'true'")
[#<CheckIn id: 3, user_id: 3, event_id: 1, report: {"without_mask"=>"Participant is not masquerade", "with_soda"=>"Participant brings soda", "bring_friends"=>true}, created_at: "2015-11-05 13:29:05", updated_at: "2015-11-05 13:29:05">]
It also possible to create a scope to filter JSON data:
class User < ActiveRecord::Base
has_many :check_ins
has_many :events, through: :check_ins
scope :without_mask, -> { joins(:check_ins).where("(check_ins.report ->> 'without_mask')::text IS NOT NULL") }
end
As far as I can tell, it has been a positive experience using JSON type. I’ll definitely use this new feature again, Postgres with JSON type is now mature and offer a complete set of functions and operators.
Leave a Reply