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