Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL JSON querying in Sequel (my presentation from our local Ruby meetup)
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)")
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']))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment