Categories


Authors

JSON Just As It Is

JSON Just As It Is

My project team has spent the last few months building a Rails application that can migrate data from a set of CSV's into a database via an API. As we migrate that data, we store many of its attributes in our Rails app in order to reference for future changes, provide feedback on the progress of the data migration, and perform sanity checks. We receive the attributes we must store in our app's database from the destination app’s API responses, which are returned in JSON.

Since we are storing data from an external application, we would like to maintain its structure and format. In this case, the JSON API responses are nested and contain multiple arrays, and each object type has a different JSON structure. For example, customers are returned with their first_name, last_name, and customer_id, whereas tickets are returned with their date_time and ticket_type. Rather than attempting to extract elements and store them as other data types, it is preferable to store the data exactly as it comes and in a single column - but still be able to query against it.

If the JSON API responses were the primary data we store, we may have wanted to consider a non relational database solution, such as MongoDB. Whereas we must normally define each model’s attributes and data types before creating an instance, a non relational database solution does not require a fixed schema.

For our application, however, we do have a defined schema of information we wish to store - and only one of those data types comes back in JSON format. As such, we chose to use PostgreSQL, which is a relationship database that offers a number of specific data types from the more common Integer, String, Float to a newer data type: json - which, as you might guess, is used for storing JSON.

The jsonb Data Type

If you need to query your JSON data, the much faster and therefore more commonly used data type is jsonb, which was part of the PostgreSQL 9.4 release and is supported by Rails as of Version 4.2. Unlike json, which is stored in plain text and simply validates the JSON format,  jsonb is stored in binary format and supports GIN indexes, which means the database does not have to scan every record to see if a condition holds true when filtering records. Also, jsonb does not preserve white space or the order of object keys, and keeps only the last value of any duplicate object keys.

PostgreSQL’s jsonb data type allows us to maintain the structure of a fixed schema while still taking advantage of the flexibility of storing more unstructured data in JSON. In short, with this approach relational and non relational data can be integrated within the same application.

Defining jsonb Columns

jsonb columns can be defined just like any other data type. Below is an example of creating an objects table that stores data in a jsonb column.

 
class CreateObjects < ActiveRecord::Migration
   def change
      create_table :objects do |t|
      t.jsonb :data
   end
end

Creating a Record

The creation of a record of a model with a jsonb data attribute can be done when the data you wish to store comes back from the external service. For example, we receive the JSON response below from the service and store it in an instance of the model Response.

 
response = 
{
   body: {
      response: {
         responseDateTime: “2016-04-28T12:01:28+0000,
         totalAmount: 120,
         totalCharge: 100,
         responseItems: [{
            responseItemID: 12345
         }]
      }
   }
}                    

Response.create!(data: response.body)

Retrieving jsonb From A Record

A jsonb data attribute can be retrieved just like any other attribute. For example:

 
response = Response.first
response.data

{
   body: {
      response: {
         responseDateTime: “2016-04-28T12:01:28+0000,
         totalAmount: 120,
         totalCharge: 100,
         responseItems: [{
            responseItemID: 12345
         }]
      }
   }
} 

Filtering Results

With a jsonb column, we can query our results based on the data inside the JSON attribute.

Example of a query

For example, below we can filter our results by our “data” column by searching for records where the property called “responseItemID” is “12345”.

Note: The -> operator returns the original JSON type (which might be an object), whereas ->> returns text

 
Response.where("data->'responseItemID' = ?", "12345")

Example of a scope

 
scope :with_data, -> (key, value) { where('data @> ?', { key => value }.to_json) }

 def self.find_with_data(key, value)
   with_data(key, value).first
 end

 Response.find_with_data("responseItemID", “12345)

Example of a LIKE

 
Response.where("data->>'responseItemID' LIKE ?", "123")

Example of a comparison:

 
scope :inconsistent_responses, -> { where("data->>'total_amount' != data->>'total_charge'") }

Read more about the various jsonb functions and operators here

In Conclusion

When your application requires the storage of JSON and you're looking for the benefits of a NoSQL, using PostgreSQL and the jsonb data type is a great solution. One thing to note, though, is that in order to update a single field within that JSON, we cannot update a single element of the stored JSON but must rather rewrite the entire element of the record. For example, if we wanted to update a Response record with a new responseDateTime, we would have to rewrite that record’s entire data attribute. When much of your data fits comfortably into a relational database and you need a JSON solution, PostgreSQL and the jsonb data type can be used effectively to store, retrieve, and query JSON. 

 

Houston, We Have Apple Push Notifications

Houston, We Have Apple Push Notifications

Testing a Rails Template

Testing a Rails Template