Rails: Save and Query JSON using PostgreSQL (Examples)

JSON is a native data type in PostgreSQL since 9.2 and now also a first class citizen in Rails!

In this article we will learn how to persist and save object data more easily.

1. Create your Migration

class Migration0001
  def change
    add_column :users, :settings, :json, default: {}
  end
end

I can confirm that PostgreSQL has native support, check your database documentation for other support questions!

2. Saving JSON data in your database

Top Secret Tip: Its EXACTLY the same as saving regular attributes!

user.update(settings: {
  email_newsletter: 'confirmed',
  cheese: {favorite: 'blue', worst: 'cheddar'}
})

user.settings["email_newsletter"] == 'confirmed'
#=> true

REMEMBER that symbols in your hashes will be converted to strings!

JSON does not have knowledge of ruby symbols!

3. Can we Query JSON attributes!?!

HECK YEAAAA

Lets find all users that have confirmed the newsletter and their favorite cheese is blue.

The final query will look like this:

SELECT * FROM "users"
WHERE settings::json ->> 'email_newsletter' = 'confirmed'
AND settings::json #>> '{cheese,favorite}' = 'blue'

Lets look at the first WHERE clause:

WHERE settings::json ->> 'email_newsletter' = 'confirmed'

typing ::json will make sure your data is a JSON type.

->> is used for top level text and integers.

Now lets query against the NESTED JSON data:

AND settings::json #>> '{cheese,favorite}' = 'blue'

the #>> is used to Get JSON objects at a specified path!

YOU DID IT!

Now you can query against your JSON Data is Rails with PostgreSQL!

Official PostgreSQL Documentation