Rails + Postgres and JSON type, my first approach

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.