Aerospike aql: How to fetch records from aerospike using predicate based on the Map field

Question

I've below class definition for data transfer object. I use spring-data-aerospike to persistence.

    import org.springframework.data.annotation.Id;
    public class User implements Serializable {

        @Id
        String uid;

        Map<String, Object> ext = new HashMap<String, Object>();

        // getters & setters    
    }

Sample data in the database is like -

select ext  from department.User;
+-------+-------------------------+----------------------------------+
| PK    | ext                     || @_class                         |
+-------+-------------------------+----------------------------------+
| "123" | MAP('{"idfa": "xyz"}')  | "com.tut.dto.User"               |
| "234" | MAP('{}')               | "com.tut.dto.User"               |
+-------+-------------------------+----------------------------------+

I need to query the database now that it should only return the records which have "idfa" key string in the ext field column.

I tried the following. But it didn't work

1.

 select * from test.UserRecord where ext.idfa is not null;
 Unsupported command format with token -  '.'
 Make sure string values are enclosed in quotes.
 Type " aql --help " from console or simply "help" from within the aql-prompt.
 ```
2. 

select * from test.UserRecord where ext contains 'idfa';
Unsupported command format with token -  ''idfa''
Make sure string values are enclosed in quotes.
Type " aql --help " from console or simply "help" from within the aql-prompt.

How can I make it work?

Answer 1

Try executing:

select * from department.user in mapkeys where ext = "idfa"

Based on the following structure:

SELECT <bins> FROM <ns>[.<set>] IN <indextype> WHERE <bin> = <value>

Assuming you've created an index with collection type of "MAPKEYS", you can create it using @Indexed annotation on the ext field in your User class.

If your using Spring Data Aerospike latest version it should look something like that:

@Indexed(name = "indexName", type = IndexType.STRING, collectionType = IndexCollectionType.MAPKEYS)
Map<String, Object> ext = new HashMap<String, Object>();

If you're interested in interacting with Aerospike List/Map bins through code (instead of AQL) you should read about CDT (Collection Data Type) operations.

CDT documentation:

https://docs.aerospike.com/docs/guide/cdt.html

Map Operation class (including methods documentation):

https://github.com/aerospike/aerospike-client-java/blob/master/client/src/com/aerospike/client/cdt/MapOperation.java

And some examples in the Aerospike Java Client:

https://github.com/aerospike/aerospike-client-java/blob/master/test/src/com/aerospike/test/sync/basic/TestOperateMap.java