jamescook/squint

Search PostgreSQL jsonb and hstore columns

Search PostgreSQL jsonb and hstore columns.

Full database searching inside columns containing semi-structured data like json, jsonb and hstore. Compatible with the awesome storext gem.

Table of contents

• Status
• Quick start
• Performance
• Storext attributes
• Developing
• Contributors
• Credits

Status

Quick Start

Add to your Gemfile:

gem 'squint'

Include it in your models:

class Post < ActiveRecord::Base
  include Squint
  # ...
end

Assuming a table with the following structure:

                                           Table "public.posts"
       Column              |            Type             |                     Modifiers
---------------------------+-----------------------------+----------------------------------------------------
 id                        | integer                     | not null default nextval('posts_id_seq'::regclass)
 title                     | character varying           |
 body                      | character varying           |
 request_info              | jsonb                       |
 properties                | hstore                      |
 storext_jsonb_attributes  | jsonb                       |
 storext_hstore_attributes | jsonb                       |
 created_at                | timestamp without time zone | not null
 updated_at                | timestamp without time zone | not null
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)

In your code use queries like:

Post.where(properties: { referer: 'http://example.com/one' } )
# SELECT "posts".* FROM "posts" WHERE "posts"."properties"->'referer' = 'http://example.com/one'

Post.where(properties: { referer: nil } )
# SELECT "posts".* FROM "posts" WHERE "posts"."properties"->'referer' IS NULL

Post.where(properties: { referer: ['http://example.com/one',nil] } )
# SELECT "posts".* FROM "posts" WHERE ("posts"."properties"->'referer' = 'http://example.com/one'
#                                   OR "posts"."properties"->'referer' IS NULL)

Post.where(request_info: { referer: ['http://example.com/one',nil] } )
# SELECT "posts".* FROM "posts" WHERE ("posts"."request_info"->>'referer' = 'http://example.com/one'
#                                   OR "posts"."request_info"->>'referer' IS NULL)

Squint only operates on json, jsonb and hstore columns. ActiveRecord will throw a StatementInvalid exception like always if the column type is unsupported by Squint.

Post.where(title: { not_there: "any value will do" } )

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "title"
LINE 1: SELECT COUNT(*) FROM "posts" WHERE "title"."not_there" = 'an...
                                           ^
: SELECT COUNT(*) FROM "posts" WHERE "title"."not_there" = 'any value will do'

Performance

To get the most performance out searching jsonb/hstore attributes, add a GIN (preferred) or GIST index to those columns. Find out more here

TL;DR:

SQL: 'CREATE INDEX name ON table USING GIN (column);'

Rails Migration: add_index(:table, :column_name, using: 'gin')

Storext attributes

Assuming the database schema above and a model like so:

class Post < ActiveRecord::Base
  include Storext.model
  include Squint

  store_attribute :storext_jsonb_attributes, :zip_code, String, default: '90210'
  store_attribute :storext_jsonb_attributes, :friend_count, Integer, default: 0
end

Example using StoreXT with a default value:

Post.where(storext_jsonb_attributes: { zip_code: '90210' } )
# -- jsonb
# SELECT "posts".* FROM "posts" WHERE ("posts"."storext_jsonb_attributes"->>'zip_code' = '90210' OR
#                                     (("posts"."storext_jsonb_attributes" ? 'zip_code') IS NULL OR
#                                      ("posts"."storext_jsonb_attributes" ? 'zip_code') = FALSE))
# -- hstore
# SELECT "posts".* FROM "posts" WHERE ("posts"."storext_hstore_attributes"->'zip_code' = '90210' OR
#                                     ((exist("posts"."storext_hstore_attributes", 'zip_code') = FALSE) OR
#                                       exist("posts"."storext_hstore_attributes", 'zip_code') IS NULL))
#
#

If (as in the example above) the default value for the StoreXT attribute is specified, then extra checks for missing column ( ("posts"."storext_jsonb_attributes" ? 'zip_code') IS NULL ) or missing key ( ("posts"."storext_jsonb_attributes" ? 'zip_code') = FALSE) ) are added

When non-default storext values are specified, these extra checks won't be added.

The Postgres SQL for jsonb and hstore is different. No support for checking for missing json columns exists, so don't use those with StoreXT + Squint

Developing

1. Thank you!
2. Clone the repository
3. bundle
4. bundle exec rake --rakefile test/dummy/Rakefile db:setup # create the db for tests
5. bundle exec rake # run the tests
6. make your changes in a thoughtfully named branch
7. ensure good test coverage
8. submit a Pull Request

Contributors

Thanks goes to these wonderful people (emoji key):

Kevin Brown 🎨 👀	Andrew Fomera 👀	Ryan T. Hosford 💻	Matthew Jaeh 🎨 👀	Justin Licata 💻 🎨 📖 👀	Kyle Miracle 🐛 👀	David H. Wilkins 💬 🐛 💻 🎨 📖 💡 👀 ⚠️
Jay Wright 👀

This project follows the all-contributors specification. Contributions of any kind welcome!

Credits

Squint is maintained and funded by ProctorU.

ProctorU Engineering & Design

A simple online proctoring service that allows you to take exams or certification tests at home.