Additional criteria query options for JSON data types

[bobbywarner]

It would be great to be able to use additional criteria query options. Most notably, ilike, to check for similar JSON values as opposed to just equals which is provided today.

[ilopmar]

Hi Bobby,

which of these operators do you think we should implement? http://www.postgresql.org/docs/9.4/static/functions-json.html

[Sabst]

Hi,
I'm unsure whether a new operator is required but here is an example of what I would like to express using criteria:
select data from TestMapJson WHERE data->>'name' ILIKE '%iv%';
(inspired from PgJsonEqualsIntegration test)

I tried the obvious:
ilike "data->>'name'", "%iv%"
But this fails.

Is there a way to do this now ? (I'm using 4.6.1... still stuck with Grails 2.x)

[ilopmar]

I don't know if this should work or not but maybe we need to create a new operator to do that, something like pgIlike "data-->'name', "%iv%"

[Sabst]

Ok, so no good surprise then.
Rather than having each time a new pg operator, do you see a more flexible way to handle this? Like using an advanced version of the current pgJsonHasFieldValue (with the operator provided as an argument)? Just wondering...

Now the immediate workaround is to bypass the plugin API... do you see a way to do this using criteria or do you think I have to turn the query into a SELECT?
What about defining my own operator outside of the pg-extensions plugin?

Thank you for your help.

[Sabst]

I added a pgJson criterion with the sql operator as an argument.
E.g. pgJson 'data', 'name', 'ilike', '%iv%'
The following will probably be clearer.
Am I missing anything? You already prepared everything for such a feature!

package net.kaleidos.hibernate.json

import spock.lang.Specification
import spock.lang.Unroll
import test.json.TestMapJson

class PgJsonIntegrationSpec extends Specification {

    def pgJsonTestSearchService

    @Unroll
    void 'Test equals finding value: #value with condition is ilike'() {
        setup:
            new TestMapJson(data: [name: 'Iván', lastName: 'López']).save(flush: true)
            new TestMapJson(data: [name: 'Alonso', lastName: 'Torres']).save(flush: true)
            new TestMapJson(data: [name: 'Iván', lastName: 'Pérez']).save(flush: true)

        when:
        def result = pgJsonTestSearchService.search('pgJson', 'data', 'name', value, 'ilike')

        then:
            result.size() == size
            result.every { it.data.name.matches "^(?i)${value.replace('%', '.*')}\$" }

        where:
            value  || size
            '%iv%' || 2
            'John' || 0
    }

    @Unroll
    void 'Test equals finding value: #value with condition equals'() {
        setup:
            new TestMapJson(data: [name: 'Iván', lastName: 'López']).save(flush: true)
            new TestMapJson(data: [name: 'Alonso', lastName: 'Torres']).save(flush: true)
            new TestMapJson(data: [name: 'Iván', lastName: 'Pérez']).save(flush: true)

        when:
        def result = pgJsonTestSearchService.search('pgJson', 'data', 'name', value, '=')

        then:
            result.size() == size
            result.every { it.data.name == value }

        where:
            value  || size
            'Iván' || 2
            'John' || 0
    }

    @Unroll
    void 'Test equals finding value: #value with condition does not equal'() {
        setup:
            new TestMapJson(data: [name: 'Iván', lastName: 'López']).save(flush: true)
            new TestMapJson(data: [name: 'Alonso', lastName: 'Torres']).save(flush: true)
            new TestMapJson(data: [name: 'Iván', lastName: 'Pérez']).save(flush: true)

        when:
        def result = pgJsonTestSearchService.search('pgJson', 'data', 'name', value, '<>')

        then:
            result.size() == size
            result.every { it.data.name != value }

        where:
            value  || size
            'Iván' || 1
            'John' || 3
    }

}

[ilopmar]

This looks good! If everything is working and you have tests please submit a PR.

I think in a couple of weeks I'll have some spare time. There are some PRs pending that I need to review and merge and I'm delaying this too much. Sorry about this.

Thanks for your contribution.

[Sabst]

Not really ready for a PR but I will consider it.
More step-back will be required to allow more PG Json/Jsonb/... options.
Ideally the plugin should provide access to all those great PG features!

[ilopmar]

PR #95 has been merged and ported also to Grails 3. I've released versions 4.6.2 for Grails 2 and 4.6.8 for Grails 3 with this new json criteria.

Closing this issue.