Unexpected query token 'KEY'

I am trying to select elements which contain a specific value inside of a map.

This should be possible with JPA's Map<KEY, VALUE>.

If I try to use them like this:

SELECT td FROM ToDo td JOIN td.customProperties cp
WHERE KEY(cp) = :propertyName AND VALUE(cp) = :propertyValue
ORDER BY td.createdTimeMillis

I get the "Unexpected query token 'KEY'" error.

Is there another way to do this and I am just using the wrong syntax?

Unfortunately support of maps in queries is currently incomplete. See and subscribe to this issue for updates.

A recommended workaround is to replace the map with a collection of embedded objects. This will also enable defining indexes on the keys/values (which will be ordinary fields in the embedded objects).

You can still JOIN the map values. The Java containsKey and containsValue methods on maps in queries can also be used, but this will probably not solve your specific need of searching for a combination of a key and a value.

The Java get method on maps in queries should also work, so you may try:

SELECT td FROM ToDo td
WHERE td.customProperties.get(:propertyName) = :propertyValue
ORDER BY td.createdTimeMillis

Thanks for the get method version, it is about 5 times faster than our load everything and manually searching it.

The linked issue was last updated 2013 so I won't subscribe.

I guess this feature will never be implemented, it seems feature completion to the JPA 2 spec was never a goal for ObjectDB.