palkan/pgrel

ActiveRecord extension for querying hstore and jsonb

Pgrel

ActiveRecord extension for querying hstore, array and jsonb.

Compatible with Rails >= 4.2 (including Rails 6).

Install

In your Gemfile:

gem "pgrel", "~> 0.3"

HStore

Querying

The functionality is based on ActiveRecord WhereChain. To start querying call where(:store_name) and chain it with store-specific call (see below).

Query by key value:

Hstore.where.store(:tags, a: 1, b: 2)
#=> select * from hstores where tags @> '"a"=>"1","b"=>"2"'

Hstore.where.store(:tags, a: [1, 2])
#=> select * from hstores where (tags @> '"a"=>"1"' or tags @> '"a"=>"2"')

Hstore.where.store(:tags, :a)
#=> select * from hstores where (tags @> '"a"=>NULL')

Hstore.where.store(:tags, { a: 1 }, { b: 2 })
#=> select * from hstores where (tags @> '"a"=>"1" or tags @> "b"=>"2"')

Keys existence:

# Retrieve items that have key 'a' in 'tags'::hstore
Hstore.where.store(:tags).key(:a)
#=> select * from hstores where tags ? 'a'

# Retrieve items that have both keys 'a' and 'b' in 'tags'::hstore
Hstore.where.store(:tags).keys('a', 'b')
#=> select * from hstores where tags ?& array['a', 'b']

# Retrieve items that have either key 'a' or 'b' in 'tags'::hstore
Hstore.where.store(:tags).any('a', 'b')
#=> select * from hstores where tags ?| array['a', 'b']

Values existence:

# Retrieve items that have value '1' OR '2'
Hstore.where.store(:tags).overlap_values(1, 2)
#=> select * from hstores where (avals(tags) && ARRAY['1', '2'])

# Retrieve items that have values '1' AND '2'
Hstore.where.store(:tags).contains_values(1, 2)
#=> select * from hstores where (avals(tags) @> ARRAY['1', '2'])

Containment:

Hstore.where.store(:tags).contains(a: 1, b: 2)
#=> select * from hstores where tags @> '\"a\"=>\"1\", \"b\"=>\"2\"'

Hstore.where.store(:tags).contained(a: 1, b: 2)
#=> select * from hstores where tags <@ '\"a\"=>\"1\", \"b\"=>\"2\"'

Update

Is implemented through ActiveRecord::Store::FlexibleHstore and ActiveRecord::Store::FlexibleJsonb objects. You can get them by sending update_store(store_name) to relation or class.

Add key, value pairs:

Hstore.update_store(:tags).merge(new_key: 1, one_more: 2)
Hstore.update_store(:tags).merge([[:new_key, 1], [:one_more, 2]])

Delete keys:

Hstore.update_store(:tags).delete_keys(:a, :b)

Delete key, value pairs:

Hstore.update_store(:tags).delete_pairs(a: 1, b: 2)

JSONB

All queries and updates for Hstore also available for JSONB.

NOTE. Querying by array value always resolves to (... or ...) statement. Thus it's impossible to query json array value, e.g.:

Model.create!(tags: {main: ['a', 'b']})

Model.where.store(:tags, main: ['a', 'b']).empty? == true
#=> select * from models where  (tags @> '{\"main\":\"a\"}' or tags @> '{\"main\":\"b\"}')

Path query:

Model.create!(tags: {main: ['a', 'b'], user: { name: 'john' } })

# You can use object to query by simple value
Model.where.store(:tags).path(user: { name: 'john' })
#=> select * from hstores where tags#>>'{\"user\",\"name\"}' = 'john'
# or passing path parts as args one by one with value at the end
Model.where.store(:tags).path(:user, :name, 'john')

# Match by complex value (array or object)
Model.where.store(:tags).path(:main, ['a', 'b'])
#=> select * from hstores where tags#>'{\"main\"}' = '[\"a\",\"b\"]'

Array

Array stores support containment queries (just like Hstore and JSONB) and also overlap operator.

NOTE. There are some other array operators ('ANY', 'ALL', querying by index - value) which I'm not going to implement – PRs are welcomed!

Overlap:

Model.where.store(:tags).overlap('a', 'b')
#=> select * from hstores where tags && '{\"a\",\"b\"}'

Negation

Use not before operator to constuct negation or pass arguments to not to run key-value query.

Model.where.store(:tags).not.overlap('a', 'b')
#=> select * from hstores where not (tags && '{\"a\",\"b\"}')

Hstore.where.store(:tags).not(a: 1)
#=> select * from hstores where tags->'a' != '1'

pgrel's Issues

Add model name to query

Hello, @palkan !

I have several models with "data" columns in jsonb. When I try to generate complex query with joins or includes, I receive "ambiguous" sql error.

I suggest to add model name to query, to specify what column sql should be used.

Example:

Event.where.store(:data, additional_projects: true)

SQL:

SELECT "events".* FROM "events" WHERE "events"."deleted_at" IS NULL AND (events.data @> '{"additional_projects":true}')

How to check for existence inside an array in JSONB

Say you have this JSON stored in a field called metadata on the Profile table:

{
   allergies: ['latex','treenuts'],
   address: {
       state: 'CA'
   }
}

How would you search all Profile records for someone who lives in California and is allergic to latex?

In plain AR I believe I would do this:

Profile.where("metadata -> 'allergies' ? :allergy",{allergy: 'latex'}).where("metadata -> 'address' ->> 'state' = :state",{state: 'CA'})

Querying not working in Rails 6.1

After upgrading to Rails 6.1, calling store will raise a NoMethodError for where_clause_factory. To reproduce, you can run the following type of query:

User.where.store(:some_data_column, foo: 'bar')

#=> NoMethodError: undefined method `where_clause_factory' for #<User::ActiveRecord_Relation:0x00007fe1cdb66230>

It looks like where_clause_factory was recently removed in favor of build_where_clause.

How to query a json field in a joint model

Suppose two models

class Category
  has_many :products
end

class Product
  belongs_to :cart
end

where Product and Category would also have a jsonb field attributes (for example)

How would one go about doing something like this using this gem (if at all possible)

SELECT categories.*
INNER JOIN products
  ON products.category_id = categories.id
WHERE (products.attributes->>'price'): numeric > 12.5

as a quick TL;DR how would one go to:

• handle querying the json field inside a joined table
• how to handle column name collision (how to specidy the table_name for the store query ?)

I think the solution to the second one could at least manually solve the first question too

path doesn't handle nil value

Tell us about your environment

Ruby Version: ruby 2.7.4p191 (2021-07-07 revision a21a3b7d23) [x86_64-darwin19]

Rails Version: rails (6.1.4.1)

PostgreSQL Version: 11.13

PgRel Version: pgrel (0.3.1)

What did you do?

Jsonb.where.store(:tags).path(:p, nil).pluck(:name)

Assuming:

Jsonb.create!(name: "a")
Jsonb.create!(name: "b", tags: {a: 1, d: {e: 2}})
Jsonb.create!(name: "c", tags: {a: 2, b: %w[c d]})
Jsonb.create!(name: "d", tags: {a: 1, b: {c: "d", e: true}})
Jsonb.create!(name: "e", tags: {b: 2, c: "e"})
Jsonb.create!(name: "f", tags: {d: {e: 1, f: {h: {k: "a", s: 2}}}})
Jsonb.create!(name: "g", tags: {f: false, g: {a: 1, b: "1"}, h: [1, "1"]})
Jsonb.create!(name: "z", tags: {z: nil})
Jsonb.create!(name: "p_nil", tags: {p: nil})
Jsonb.create!(name: "p_empty", tags: {p: ''})
Jsonb.create!(name: "p_value", tags: {p: 'hello'})

What did you expect to happen?

I expected it to return: ["p_nil"]

What actually happened?

It actually returned: ["p_empty"]

ActiveRecord::QueryMethods::JsonbChain#path converts a nil to a string value ... which results in a value of ''. This generates the following sql for the example above:

SELECT "jsonbs"."name" FROM "jsonbs" WHERE "jsonbs"."tags"#>>'{p}' = '';

I think there are a few different ways to handle this depending on what the expected behavior of path with a nil value should be:

• should it only match when value is null in JSON or,
• should it match when value is null AND when the path doesn't exists

If ONLY when value is null:

the case stmt in ActiveRecord::QueryMethods::JsonbChain#path can be updated to:

        case val
        when Hash
          op = "#>"
          val = ::ActiveSupport::JSON.encode(val)
        when Array
          op = "#>>"
          val = val.map(&:to_s)
        # only null value in json
        when NilClass
          op = "#>"
          val = "null"
        else
          op = "#>>"
          val = val.to_s
        end

If when value is null AND when the path doesn't exists

the case stmt in ActiveRecord::QueryMethods::JsonbChain#path can be updated to:

        case val
        when Hash
          op = "#>"
          val = ::ActiveSupport::JSON.encode(val)
        when Array
          op = "#>>"
          val = val.map(&:to_s)
        # null value and missing key in json
        when NilClass
          op = "#>>"
        else
          op = "#>>"
          val = val.to_s
        end

Add store update methods

Examples:

1. Delete hstore keys:

# before
Post.update_all(["labels = delete(labels, ARRAY[?])", id.to_s])

# after
Post.update_store(:labels).delete_keys(id)

2. Merge hstore

# before
Post.update_all(["labels = hstore(labels) || hstore(ARRAY[:keys])",
                   keys: labels.map { |g, l| [g.to_s, l.to_s] }.flatten])

# after
Post.update_store(:labels).merge(labels)

3. Delete pairs

Post.update_store(:labels).delete_pairs(labels)

[feature request] Add support for operators in JSONB path query

Hi @palkan, thanks for this gem. It seems that it is not possible to define operators when doing a JSONB path query. The operator is guessed from the value provided and it uses = or IN. Is this correct?

I was thinking of adding support using one of the examples below:

# Convert path to chain
Model.store(:store).path.like(:name, '%test%')

# Pass operator as option
Model.store(:store).path(:name, '%test%', operator: 'ILIKE')

# Pass arel node as first argument
Model.store(:store).path(Arel::Nodes::Matches, :name, '%test%')

What do you think? I've implemented the first example in my application and if you agree I can make a PR.

Improve select by values

Now:

Model.where.store(:data, a: 1, b: 2) 
#=> where data->'a' = '1' and data->'b' = '2'

But this query doesn't use index!

We should use hstore/jsonb specific operators:

Model.where.store(:data, a: 1, b: 2)
#=> where data @> "'a'=>'1','b'=>'2'":hstore

undefined method `bind' for #<ActiveRecord::Relation::WhereClause>

The instances variable binds has been removed in AR v5.2.
rails/rails@213796f#diff-c9d167bac00ff2f45c5b5e035e8a80e8