PostgreSQL JSON querying in Sequel (my presentation from our local Ruby meetup)

01-activerecord.rb

require "active_record"

ActiveRecord::Base.establish_connection('postgres:///testing')
ActiveRecord::Migration.verbose = false

ActiveRecord::Migration.class_eval do
  create_table :played_quizzes, force: true do |t|
    t.integer :player_ids, array: true
    t.json    :quiz_snapshot
  end
end



class PlayedQuiz < ActiveRecord::Base
end

PlayedQuiz.create(player_ids: [1, 2, 3], quiz_snapshot: {name: "Game of Thrones", questions: [{}]})

PlayedQuiz.first.player_ids # => [1, 2, 3]
PlayedQuiz.first.quiz_snapshot # => {"name"=>"Game of Thrones", "questions"=>[{}]}

#  id | player_ids |                quiz_snapshot
# ----+------------+---------------------------------------------
#   1 | {1,2,3}    | {"name":"Game of Thrones","questions":[{}]}


# ARRAY
PlayedQuiz.where("player_ids @> 2")
PlayedQuiz.select("player_ids[2]")
PlayedQuiz.where("any(player_ids)")
PlayedQuiz.select("array_length(player_ids, 1)")
PlayedQuiz.select("array_to_string(player_ids, ',', NULL)")
PlayedQuiz.select("array_remove(player_ids, 3)")


# JSON
PlayedQuiz.where("(quiz_snapshot ->> 'name') = 'Game of Thrones'")
PlayedQuiz.select("(quiz_snapshot #>> ARRAY['questions', 1, 'title'])")
PlayedQuiz.where("(quiz_snapshot ? 'name')")
PlayedQuiz.where("(quiz_snapshot ?& ARRAY['name', 'creator_id'])")
PlayedQuiz.where("(quiz_snapshot ?| ARRAY['name', 'creator_id'])")
PlayedQuiz.select("json_object_keys(quiz_snapshot)")
PlayedQuiz.select("json_array_elements(quiz_snapshot)")
PlayedQuiz.select("json_populate_record(quiz_snapshot)")

02-sequel.rb

require "sequel"

DB = Sequel.connect('postgres:///testing')

DB.create_table! :played_quizzes do
  primary_key :id
  column      :player_ids,    "integer[]"
  column      :quiz_snapshot, :jsonb
end

# Reading and writing
DB.extension :pg_array
DB.extension :pg_json

class PlayedQuiz < Sequel::Model
end

PlayedQuiz.create(player_ids: [1, 2, 3], quiz_snapshot: {name: "Game of Thrones", questions: [{}]})

PlayedQuiz.first.player_ids # => [1, 2, 3]
PlayedQuiz.first.quiz_snapshot # => {"name"=>"Game of Thrones", "questions"=>[{}]}

# Querying
Sequel.extension :pg_array_ops
Sequel.extension :pg_json_ops

# ARRAY
player_ids = Sequel.pg_array_op(:player_ids)
PlayedQuiz.where(player_ids.contains(2))
PlayedQuiz.select(player_ids[2])
PlayedQuiz.where(player_ids.any)
PlayedQuiz.where(player_ids.length)
PlayedQuiz.where(player_ids.join(','))
PlayedQuiz.where(player_ids.remove(3))

# JSON
quiz_snapshot = Sequel.pg_jsonb_op(:quiz_snapshot)
PlayedQuiz.where(quiz_snapshot.get_text('name') => 'Game of Thrones')
PlayedQuiz.select(quiz_snapshot.get_text(['questions', 1, 'title']))
PlayedQuiz.where(quiz_snapshot.has_key?('name'))
PlayedQuiz.where(quiz_snapshot.contain_all(['name', 'creator_id']))
PlayedQuiz.where(quiz_snapshot.contain_any(['name', 'creator_id']))
PlayedQuiz.select(quiz_snapshot.keys)
PlayedQuiz.select(quiz_snapshot.array_elements)
PlayedQuiz.select(quiz_snapshot.populate(['name']))