Sorting on json properties

[domiko]

Given the following schema

CREATE TABLE table_dummy
(
  id bigint NOT NULL,
  date_created timestamp with time zone NOT NULL,
  name character varying(16),
  stats jsonb,
  CONSTRAINT table_dummy_pkey PRIMARY KEY (id)
)

and the content of the stats field that can look like:

{
    "granularity": 78,
    "intensity" : 50,
    "spacing" : 48
}

How would you do the following through GORM (HQL/Criterias/dynamic_finders) with the grails-postgresql-extensions plugin?

SELECT * FROM table_dummy order by (stats->'intensity') desc

Thanks, Dominique.

[ilopmar]

I've found this post: http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/ and I've made a very quick test with that class and this code:

// Service method
List<TestMapJsonb> orderByJson() {
    TestMapJsonb.withCriteria {
        order sqlFormula("(data->'name') desc")
    }
}

and this test:

void 'Order by a json property'() {
    setup:
        new TestMapJsonb(data: [name: 'Iván', lastName: 'López']).save(flush: true)
        new TestMapJsonb(data: [name: 'Alonso', lastName: 'Torres']).save(flush: true)
        new TestMapJsonb(data: [name: 'Ernesto', lastName: 'Pérez']).save(flush: true)

    when:
        def result = pgJsonTestSearchService.orderByJson()

    then:
        result != null
        result.data.name == ['Iván', 'Ernesto', 'Alonso']
}

And it works :smile:

It's a little bit hacky because you need to write the correct order expression and it's just appended to the criteria. Does it sounds good to you?

[ilopmar]

BTW @domiko you can see all the changes here: https://github.com/kaleidos/grails-postgresql-extensions/compare/master...order_by

[domiko]

Hey @lmivan, Hacky or not, this is exactly what I needed :-) . Works like a charm.

Thanks for your assistance and very quick response.

[ilopmar]

Great. I'm going to try to fix another issue and then I'll release a new version with this new sorting and another "sort byRandom".

[ilopmar]

Included in version 4.6.0

[domiko]

Thanks @lmivan

[ilopmar]

:+1: