Grails: query or criteria against a string/value pairs map property

Question

Grails gives the possibility of creating simple string/value map properties section "Maps of Objects", first paragraph.

I was wondering, is there a way to later query the domain class (using Gorm dynamic finders, criterias or HQL) using the map property as part of the query (i.e adding a condition for the key X to have the value Y)?

Answer 1

After playing with it a bit and almost give up, I discovered the map syntax to (surprisingly) work in HQL. Assuming the class looks like:

class SomeClass {
   Map pairKeyProperty
}

You can build queries that look like the following:

select * from SomeClass sc where sc.pairKeyProperty['someKey'] = 'someValue' and sc.pairKeyProperty['someOtherKey'] = 'someOtherValue'

Pretty neat! I still would prefer to use criterias as they are much cleaner to compose, but they seem to not support the same syntax (or I couldn't find it).

I created a sample app in GitHub: https://github.com/deigote/grails-simple-map-of-string-value-pairs

It can be visisted at: http://grails-map-of-string-pairs.herokuapp.com/

Answer 2

The form above uses a cross join. To enforce an inner join use

join sc.pairKeyProperty pk1 on index(pk1) = 'someKey'
where 'someValue' in elements(pk1)