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

[jonian]

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.

[palkan]

What about:

Model.store(:store).path(:name).like('%test%')

?

And I like this option, too:

Model.store(:store).path(:name, '%test%', operator: 'ILIKE')

# or
Model.store(:store).path(:name).op("ILIKE", '%test%')

It could be used for arbitrary operators.

[jonian]

Thanks for the response, I'm ok with any of them. I think the best are:

# will also need methods for unlike, gt, gte, lt, lte
Model.store(:store).path(:name).like('%test%')

Model.store(:store).path(:name).op("ILIKE", '%test%')

Both can be implemented to give more options and avoid SQL operators for the common cases that are covered by the first option.

As I said in my previous comment, I can make a PR for this when you decide what to implement.

[jonian]

After thinking about this a little bit more, I think that converting path to a chain, will require to change the way that path works now, so the best way to do it seems to add the operators in the store chain.

Convert path to chain

# using 
Model.store(:store).path(:name).like('%test%')
Model.store(:store).path(:name).op("ILIKE", '%test%')

# will require to check for `=` with
Model.store(:store).path(:name).eq('test')
Model.store(:store).path(:name).op("=", 'test')

Add operator methods to store chain

# current usage
Model.store(:store).path(:name, 'test')
Model.store(:store).not.path(:name, 'test')

# usage with specific operators
Model.store(:store).like.path(:name, '%test%')
Model.store(:store).not.like.path(:name, '%test%')

# usage with arbitary operator
Model.store(:store).op("LIKE").path(:name, '%test%')
Model.store(:store).not.op("LIKE").path(:name, '%test%')

What do you think?

[palkan]

path(...).like(...) looks more natural than like(...).path(...)

Though converting path into a chain would be hard since we allow arbitrary number of args 🤔

Maybe, we can introduce a new method, say, #at_path:

Model.store(:store).at_path(:name).like('%test%')

[jonian]

To summarize, the available options that are easier to implement and do not require changes in how the gem already works are:

# New operators are always a chain
Model.store(:store).like.path(:name, '%test%')

# Path is a chain when no arguments given
Model.store(:store).path.like(:name, '%test%')

# New method for path chain
Model.store(:store).at_path(:name).like('%test%')

I think all options are good and the decision is up to you @palkan.

[palkan]

Let's continue with the last one: #at_path.

[jonian]

Great! I will start working on it in the next days.