Query JSON with Postgres 9.4 and Rails 4.2

Christmas came early this year. With Rails 4.2 and Postgres 9.4 being released, some of the benefits of NoSQL can be found in the RDBMS of choice. Specifically, you can drop schemaless data structures into your tables and query them with ease.

Let's dive in.

If you haven't upgraded Postgres to 9.4 yet, check out this post on upgrade directions.

I've created an example project with sample data. By following these directions below, you can follow along.

git clone git@github.com:rbeene/json_example.git  
cd json_example  
bundle  
rake db:create && rake db:migrate && rake db:seed  

If you have any issues performing the above, refer to the possible errors section.

In the app, we have users and associated Stripe charges. Instead of pulling attributes out of the stripe charge object, we'll dump the entire JSON contents into the payload attribute on charge.

You can view an example Stripe charge response.

There is a lot of data here that we may want to query on. Credit card type, paid status, or amount charged. The credit card itself is example of a nested structure that we might want to query.

Let's work through those to see how Postgres exposes these possibilities.

All charges that are paid

Charge.where("payload ->> 'paid' = 'true'")  

The ->> operator will return text. This allows for

All charges paid for by a Visa

Charge.where("payload -> 'card' ->> 'brand' = 'Visa'")  

In the payload, card is a nested hash which contains the value we're querying. In order to query it, we combine the previous operator ->> with ->. This operator (->) returns the original JSON at which point, we can query brand and treat it as text.

This query can actually be simplified by using a different operator.

Charge.where("payload #>> '{card,brand}' = 'Visa'")  

The #>> operator will traverse card to brand and retrieve the value as text.

Number of charges grouped by card type

Charge.select("payload #>> '{card, brand}' as brand, count('brand')")  
      .group("brand")

Total charged by an individual user

user = User.first  
user.charges  
    .select("user_id, sum(CAST(payload ->> 'amount' AS integer)) AS total")
    .group("user_id").first

In this case, CAST is required as amount would otherwise come back as a string.

Other Operators

Our seeds file also included some dynamic records. This data is a bit more unstructured in the sense that the payload varies considerably.

Array contains value

Let's say we wanted to query for records where the payload contains a key 'x' contains the value '1'. This searches both records where 'x' contains an array or where 'x' has a value of '1'.

Data Set

Dynamic.create(payload: {x: ['1','2','3']})  
Dynamic.create(payload: {x: ['3','4','5']})  
Dynamic.create(payload: {x: '1'})  

Query

Dynamic.where("payload -> 'x' ? '1'")  

Payload contains all keys or any keys

We may want to query for a payload that contains all or any keys. We'll investigate with the following records.

Data Set

Dynamic.create(payload: {x: '1', y: '2'})  
Dynamic.create(payload: {x: '3', y: '4'})  
Dynamic.create(payload: {a: '1', b: '2'})  

All keys query

Dynamic.where("payload ?& array['x', 'y']")  

Payload contains any of the keys

Any key query

Dynamic.where("payload ?| array['x', 'a']")  

With these types of queries, you can search the more dynamic tables with ease. With Postgres 9.4 and Rails 4.2, you can bring a lot of the benefits found in NoSQL solutions into an RDBMS.

Sign up for our mailing list to find out when our next article comes out. We'll be covering how to index data stored in a jsonb field.

Bonus Content

Needing to update your json column to jsonb? Here's an example migration on how to do it.

class AlterJsonbToJsonAndBack < ActiveRecord::Migration  
    def up            
      change_column :dynamics, :payload, 'jsonb USING CAST(payload AS jsonb)'  
    end

    def down        
      change_column :dynamics, :payload, 'json USING CAST(payload AS json)'    
    end
  end      

Possible issues during setup

  1. If you can't create the database due to a libpq error, uninstall and reinstall the pg gem.
  2. If the migration doesn't run due to the jsonb fields, you didn't properly upgrade your Postgres installation. If you run rails dbconsole you should see the following.
psql (9.4.0)  
Type "help" for help.  
postgres_development=#  

If you see a reference to an earlier version of Postgres, the upgrade process was done incorrectly.