Updating a single key/value pair in a Rails 4 and PostgreSQL json column?

Randy Burgess picture Randy Burgess · Aug 21, 2014 · Viewed 14.1k times · Source

I have a rather large json document that I have to store in a field for each Evaluation instance in my app. The over time, certain actions on the app will require me to change various key/value pairs in the document. The Rails 4 and PostgreSQL json data type seems ideal for this problem, but I cannot get changes to commit to the database.

Here's an abbreviated workflow:

I have a json file that I import into a field for each Evaluation record:

// example.json
{ "question_1":"no" }

I create a record and import the json:

>> evaluation = Evaluation.create assessments: File.read("example.json")
>> evaluation.assessments = File.read("#{Rails.root}/example.json")
=> "{ \"question_1\":\"no\" }"

Calling on the assessments field seems to work fine:

>> evaluation.assessments
=> {"question_1"=>"no"}

OR

>> evaluation.assessments["question_1"]
=> "no"

Example 1

Changing the json isn't working too well. This doesn't actually commit anything:

>> evaluation.assessments["question_1"] = "yes"
=> "yes"

>> evaluation.assessments["question_1"]
=> "yes"

>> evaluation.save
   (0.3ms)  BEGIN
   (0.2ms)  COMMIT
=> true

Example #2

Replacing with a completely new object does commit:

>> evaluation.assessments = {"question_1"=>"yes"}
=> {"question_1"=>"yes"}

>> evaluation.save
(0.3ms)  BEGIN
SQL (0.7ms)  UPDATE "evaluations" SET "assessments" = $1, "updated_at" = $2 WHERE "evaluations"."id" = 1  [["assessments", "{\"question_1\":\"yes\"}"], ["updated_at", "2014-08-21 00:52:03.581817"]]
(3.8ms)  COMMIT
=> true
  • What am I doing wrong in Example #1?
  • Why won't the database commit the change I made to the evaluation.assessment column when I call it, change the value, and call save on the instance?

Answer

Randy Burgess picture Randy Burgess · Aug 21, 2014

This ended up being an easy fix due to a known bug related to ActiveRecord not knowing the changed attribute is dirty:

https://github.com/rails/rails/issues/6127

Here's the solution:

>> evaluation.assessments["question_1"] = "yes"
=> "yes"

>> evaluation.assessments_will_change!
>> evaluation.save
(1.1ms)  BEGIN
SQL (1.6ms)  UPDATE "evaluations" SET "assessments" = $1, "updated_at" = $2 WHERE "evaluations"."id" = 4  [["assessments", "{\"question_1\":\"yes\"}"], ["updated_at", "2014-08-21 01:59:47.331216"]]
(2.5ms)  COMMIT
=> true