JSON Data Type

Whenever we save data from one of our Rails models, each attribute is mapped one to one to a field in the database. These fields are generally of a simple type, like a string or an integer. However, it’s also possible to save an entire data object in JSON format in a field. Let’s see an example of how to do this from a Ruby on Rails application.

For this example, let’s assume that I have a Page model where I want to save some stats. To begin, we’re going to generate a new migration and add the stats field and define it as type JSON which by default will save an empty array

def change
  add_column :pages, :stats, :json, default: []
end

Once migrated, let’s have a deeper look at how our pages table looks like

\d pages
Table "public.pages"
 Column | Type | Default
...
 stats  | json | '[]'::json

Now that is interesting, unlike the more common types which can be 0 or false, the default value of this field is literally the string cast to JSON. Let’s play a little bit with this and cast an array with values

SELECT '[1, 2, 3]'::json
   json    
-----------
 [1, 2, 3]
(1 row)

Turns out PostgreSQL offers also a set of functions to handle JSON data. Let’s say for example that I wanted to get all pages that have no pre-calculated stats. This can be done using the json_array_length function

SELECT *
  FROM people
 WHERE json_array_length(stats) = 0

This is way more performant than, fetching the data, serializing it, and loading it to a Ruby array to then calculate its length.

Ok, that’s all nice, what about the cases when I do need to load the data in a Ruby object and then save it back? You’ll be happy to know that you don’t need to do anything else, Rails will do all the heavy lifting of serializing and deserializing for you, and provide a getter and setter methods so you can interact with the attribute as you normally would

page = Page.find(1)
page.stats.class
=> Array
page.stats = [1, 2, 3]
=> [1, 2, 3]
page.save
=> true

Throughout this example, I used a very simple array, but you can of course use much more complex data objects like you normally would with JSON but be careful not to shoot yourself in the foot! Just because you can save a lot of data into a JSON field doesn’t mean that you should. Evaluate first if what you need is an additional model that relates to the model you’re working with.

Want to know more? Checkout PostgreSQL documentation on the JSON datatype and the functions you can use

Reply by Email