Learn about jsonb in Postgres & Rails

jsonb is a data type available in Postgres 9.4+.

It offers some useful additional operators for your database queries.

This project requires:

Ruby 2.3.7
Rails 5.2.1
Postgres 9.5.10

To get this project running on your machine:

  $ git clone git@github.com:louiseswift/learn_jsonb.git
  $ cd learn_jsonb

  # check your Ruby and Postgres versions and install the required versions,
  # if they aren't already on your machine
  $ ruby -v
  $ postgres --version

  $ gem install bundle # if you don't already have bundle installed
  $ bundle
  $ rake db:create && rake db:migrate
  $ rails s

To start working with jsonb:

Open the project's user migration file in ./db/migrate/ to see how a jsonb attribute is added to a model, and how to create records for that model
In your terminal, open a new terminal tab to run:
```
  $ rails console
```
And then once the console is running:
```
  User.first
  User.first.preferences
```
📝 Create a new user record in your database, with some different preferences data
Open up ./app/models/users.rb to see how to use store_accessor on a model to make its JSON attributes available as properties via dot notation, and how it can help keep validations tidy

To use these accessible properties, try the following in your console:

  User.first.languages
  User.first.life_goals

  User.first.update(languages: ['ruby', 'javascript', 'doggo'])

For further manipulation of jsonb values, it can be useful to write custom queries that are executed as follows:

  p = ActiveRecord::Base.establish_connection
  c = p.connection
  results = c.execute('SELECT * FROM users;')
  results.each { |result| puts result }

A query that will allow us to create or update values within a jsonb attribute is:

  jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])

We could use this to change our first user's email opt-in setting:

  p = ActiveRecord::Base.establish_connection
  c = p.connection
  sql = 'UPDATE users SET preferences = jsonb_set(preferences, \'{newsletter_opt_in}\', \'false\', FALSE) WHERE id = 1;'
  c.execute(sql)
  User.find(1).newsletter_opt_in # to confirm

Or we could delete the third language in their preferences:

p = ActiveRecord::Base.establish_connection
c = p.connection
sql = 'UPDATE users SET preferences = preferences #- \'{languages,2}\' WHERE id = 1;'
c.execute(sql)
User.find(1).languages # to confirm

To query records by values in a jsonb attribute the @> operator asks whether the left-hand JSON value contains the right-hand JSON value in its top level:
```
  language_json = { languages: [ 'javascript' ] }.to_json
  users = User.where('preferences @> ?', language_json)
```
📝 Add a new life goal to any user

Answer

goal_json = { "text": "Play the drums", "priority": 3, "complete": false }.to_json
sql = "UPDATE users SET preferences = jsonb_set(preferences, '{life_goals,2}', '#{goal_json}', TRUE) WHERE id = 1;"

📝 Find a user whose life_goals include 'Learn jsonb', and update that goal to be complete

Answer

life_goal_json = { life_goals: [ { text: 'Learn jsonb' } ] }.to_json
user = User.where('preferences @> ?', life_goal_json).first

updated_goals_json = user.life_goals.map do |goal|
  goal['text'] == 'Learn jsonb' ? goal.tap { |goal| goal['complete'] = true } : goal
end.to_json

sql = "UPDATE users SET preferences = jsonb_set(preferences, '{life_goals}', '#{updated_goal_json}', FALSE) WHERE id = #{user.id};"

fionnbharra/learn_jsonb

Learn about jsonb in Postgres & Rails

This project requires:

To get this project running on your machine:

To start working with jsonb: