How to Query using both Mapkeys and Mapvalues?


#1

Hello,

I’m new to Aerospike. And would try it with our new feature in our company.

We have data like this

[
 attribues: map {
   age:19,
   gender: male,
 },
 atribute: map {
   age:19,
   gender female,
},
 attributes: map {
   gender: male,
 },
 attributes: map {
   age:19,
 }
]

Then we want to querying based on attributes. Example:

  1. We want to get data that has attributes: gender: male only, then it should return only one, but it sent 3 items.

    SELECT * FROM feed_test.newsfeed IN MAPKEYS WHERE attributes="gender" AND IN MAPVALUES WHERE attributes="male"

  2. We want to get data the has attributes: age:19 only, it should return only one, but it return 2 items.

    SELECT * FROM feed_test.newsfeed IN MAPKEYS WHERE attributes="age" AND IN MAPVALUES WHERE attributes=19

Am I doing something wrong ? I don’t find any documentation about querying like this, is this query supported?


#2

AQL is not a query language (like SQL), it’s a CLI tool you can use for admin of things such as indexes and UDF modules, or for basic data browsing. Keep that in mind when you try to create more complex queries.

Aerospike supports secondary-index queries of a limited nature. Because Aerospike does not have a schema, your secondary index needs to define not just the bin it’s being built on, but also declare the data type of the data (with integer, string, and geospatial supported) and the data ‘container’ (none for scalar data, list, map-keys or map-values). Only records that have data in the declared bin, with the declared data type and container will be indexed by the specified index. You can have multiple indexes on your records.

So, you should be able to build an INTEGER index on MAPVALUES, and use that to find users within a specified integer range. You can then do further processing on the application side. If you look closely at the queries link above, you’ll see that you can only use one predicate with a query. There is no support for the more complex WHERE clause you’re trying to use.

For complex predicates there is predicate filtering, which is very powerful way to build a multi-predicate filter that can look at both data (in the bins) and metadata (such as TTL, last-update-time, etc). Take a look at the predicate filtering examples in the Java client. You can use a predicate filter without a secondary index even (applied to a scan). AQL does not yet support predicate filtering in its syntax (but again, it’s a tool, not a query language).

There is another way to do what you’re looking for using stream UDFs, in case you’re using a language client that does not support predicate filtering. The ones that do are Java, C, C# and Go clients. There are plenty of examples on Stackoverflow.