Mauro Morales

software developer

Day: March 22, 2021

  • Ruby On Rails: Storing JSON Directly in PostgreSQL

    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