How to use sindex in aql queries on map bins
AQL is a tool written in C, using C client underneath to interact with the server. It has limited functionality. Not all features implemented in clients such as C or Java are available in AQL.
For sindex queries, it only supports finding matches in map keys or map values of top level map in a bin or for list values in a top level list.
Lets walk thru a code example. Let me insert a map type data in a bin using aql.
aql> insert into test.demo (PK, 'catalog') values ('rec1', json('{"id":101, "title":"How Birds Fly"}'))
OK, 1 record affected.
aql> insert into test.demo (PK, 'catalog') values ('rec2', json('{"id":101, "title":"How Birds Feed"}'))
OK, 1 record affected.
aql> insert into test.demo (PK, 'catalog') values ('rec3', json('{"id":102, "title":"How Planes Fly"}'))
OK, 1 record affected.
aql> insert into test.demo (PK, 'catalog') values ('rec4', json('{"id":102, "title":"How Planes Land"}'))
OK, 1 record affected.
aql> insert into test.demo (PK, 'catalog') values ('rec5', json('{"id":102, "title":"How Planes TakeOff"}'))
OK, 1 record affected.
Check inserted data:
aql> select * from test.demo
+--------+-------------------------------------------------+
| PK | catalog |
+--------+-------------------------------------------------+
| "rec1" | MAP('{"id":101, "title":"How Birds Fly"}') |
| "rec3" | MAP('{"id":102, "title":"How Planes Fly"}') |
| "rec4" | MAP('{"id":102, "title":"How Planes Land"}') |
| "rec2" | MAP('{"id":101, "title":"How Birds Feed"}') |
| "rec5" | MAP('{"id":102, "title":"How Planes TakeOff"}') |
+--------+-------------------------------------------------+
5 rows in set (0.033 secs)
OK
Using asadm, create secondary index on map values in catalog bin.
Admin> enable
Admin+> manage sindex create numeric idx_mapval ns test set demo bin catalog in mapvalues
Use 'show sindex' to confirm idx_mapval was created successfully.
Admin+>
Admin+> show sindex
~~~~~~~Secondary Indexes (2024-05-03 17:55:42 UTC)~~~~~~~
Index Name|Namespace| Set| Bin| Bin| Index|State
| | | | Type| Type|
idx_mapval|test |demo|catalog|numeric|mapvalues|RW
Number of rows: 1
Back to aql, run the query:
aql> select * from test.demo in MAPVALUES where catalog = 101
+--------+---------------------------------------------+
| PK | catalog |
+--------+---------------------------------------------+
| "rec1" | MAP('{"id":101, "title":"How Birds Fly"}') |
| "rec2" | MAP('{"id":101, "title":"How Birds Feed"}') |
+--------+---------------------------------------------+
2 rows in set (0.010 secs)
OK
and try different map value:
aql> select * from test.demo in MAPVALUES where catalog = 102
+--------+-------------------------------------------------+
| PK | catalog |
+--------+-------------------------------------------------+
| "rec5" | MAP('{"id":102, "title":"How Planes TakeOff"}') |
| "rec3" | MAP('{"id":102, "title":"How Planes Fly"}') |
| "rec4" | MAP('{"id":102, "title":"How Planes Land"}') |
+--------+-------------------------------------------------+
3 rows in set (0.007 secs)
OK
How do we do sindex on this map currently when i apply filter i dont get anything i get an error MAP(‘{“profile”:{“lut”:1714481010041, “meta”:{“provider”:“VI”}, “nlt”:1715085810041, “pct”:1714481010041, “status”:3}}’)
aql> SELECT * FROM USP.PROFILE_DATA IN MAPVALUES WHERE RCS=3 limit 10 Error: (-12) Max retries exceeded: 5 sub-errors: 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND 201 AEROSPIKE_ERR_INDEX_NOT_FOUND
info sindex shows as RCS | USP |PROFILE_DATA|172.21.67.31:8000 |RCS |numeric|Read-Write|59.510 M|178.531 M|3.000 | 3.000 |mem | 4.012 GB|[map_index(0)]
As I had mentioned earlier: For sindex queries, it only supports finding matches in map keys or map values of top level map in a bin or for list values in a top level list.
You have created a secondary index at map_index(0) context - so AQL cannot use it. Hence you are getting index not found error. Given your data structure, you can create it at the top level and that should work.
In my example, I created top level index as:
Admin> enable
Admin+> manage sindex create numeric idx_mapval ns test set demo bin catalog in mapvalues
Use 'show sindex' to confirm idx_mapval was created successfully.
Admin+>
Admin+> show sindex
~~~~~~~Secondary Indexes (2024-05-03 17:55:42 UTC)~~~~~~~
Index Name|Namespace| Set| Bin| Bin| Index|State
| | | | Type| Type|
idx_mapval|test |demo|catalog|numeric|mapvalues|RW
Number of rows: 1
Had I done with context, like below, and restrict to indexing only one map_key’s values in sub-context map_index(0) , AQL will not be able to use it. You can certainly use it via the Java Client. Just AQL has limited functionality. (Based on what you are trying to do, I would not change the sindex, rather write the Java application to run the query. But for quick verification purposes, you can create another sindex without any ctx and use AQL. The difference is you will unnecessarily sindex matching numeric values of nlt and pct keys also.)
Admin+> manage sindex create numeric idx_mapval_ctx ns test set demo bin catalog in mapvalues ctx map_index(0)
Use 'show sindex' to confirm idx_mapval_ctx was created successfully.
Admin+> info sindex
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Secondary Index Information (2024-05-05 06:18:39 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Index Name|Namespace| Set| Node| Bin| Bin| State| Keys|~~~~~~~~Entries~~~~~~~~|~~~~Storage~~~~| Context
| | | | | Type| | | Total|Avg Per|Avg Per| Type| Used|
| | | | | | | | | Rec|Bin Val| | |
idx_mapval |test |demo|ip-172-31-3-62.ec2.internal:3000|catalog|numeric|Read-Write|1.667 |5.000 |1.000 |3.000 |shmem|16.000 MB|--
idx_mapval_ctx|test |demo|ip-172-31-3-62.ec2.internal:3000|catalog|numeric|Read-Write|0.000 |0.000 |0.000 |0.000 |shmem|16.000 MB|[map_index(0)]
|test |demo| | | | | |5.000 | |3.000 | |32.000 MB|
Number of rows: 2
Note: map_index(0) in your output - that tells me that you created a sindex using a ctx qualifier.
If create the index without passing the ctx i dont see any thing getting indexed . it is showing as 0 for all values
Oh, becuase you don’t have a top level map in the bin that has string or int values. I missed that - you have the top level profile:{ } map and in it sub-maps with keys: lut, meta, nlt, pct and status. If you want to keep this data model, test with a client application. AQL is merely a tool to explore data.
If your data structure was: mapBin: { “lut”:1714481010041, “meta”:{“provider”:“VI”}, “nlt”:1715085810041, “pct”:1714481010041, “status”:3} … i.e. all the keys are now top level - the lut, nlt, pct and status values (since they are integers) will get indexed. meta won’t be indexed since it is a map type value.
AQL - needs a top level key-value pairs to use mapkey or mapvalues index.
Just write a Java client application and test. (Showed sample code on your other question.) With Java client you can go to any practically deep context level sindexes.
Can you help me out on how to index specific key : value say for example status in our case . How to specify the context for a satuts files inside profile
Here is a full solution to your problem. I did in Jupyter Notebook so I will share the pertinent cells. You should be able to adapt from here.
//Required Imports
import com.aerospike.client.AerospikeClient;
import com.aerospike.client.policy.WritePolicy;
import com.aerospike.client.Bin;
import com.aerospike.client.Key;
import com.aerospike.client.Record;
//Building map values
import com.aerospike.client.cdt.MapOperation;
import com.aerospike.client.cdt.MapPolicy;
import com.aerospike.client.cdt.MapOrder;
import com.aerospike.client.cdt.MapWriteFlags;
import com.aerospike.client.Value.MapValue;
import com.aerospike.client.Value;
import java.util.HashMap;
import java.util.Map;
//SI query related
import com.aerospike.client.query.Filter;
import com.aerospike.client.query.Statement;
import com.aerospike.client.cdt.CTX;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.IndexCollectionType;
AerospikeClient client = new AerospikeClient("127.0.0.1", 3000);
Create the secondary index using asadm:
manage sindex create numeric idx_mapval ns test set testset bin myMapBin1 ctx map_index(0) map_key(status)
Data Model:
{“profile”:{“lut”:1714481010041, “meta”:{“provider”:“VI”}, “nlt”:1715085810041, “pct”:1714481010041, “status”:3}}
Using the next Jupyter Notebook cell, add some records:
void addRecord(Integer keyIndex, String mapBinName, Integer statusVal, Long pctValue, Long nltValue, Long lutValue, String providerVal){
MapPolicy mPolicy = new MapPolicy(MapOrder.UNORDERED, MapWriteFlags.DEFAULT);
WritePolicy wPolicy = new WritePolicy();
wPolicy.sendKey = true; //Optional, if you want to inspect the record key
Key myRecKey = new Key("test", "testset", Value.get("key"+keyIndex));
HashMap <String, Value> profileObj = new HashMap <String, Value>();
profileObj.put("status", Value.get(statusVal));
profileObj.put("pct", Value.get(pctValue));
profileObj.put("nlt", Value.get(nltValue));
profileObj.put("lut", Value.get(lutValue));
HashMap <String, String> metaObj = new HashMap <String, String>();
metaObj.put("provider", providerVal);
profileObj.put("meta", new MapValue(metaObj));
client.operate(wPolicy, myRecKey,
MapOperation.put(mPolicy, mapBinName, Value.get("profile"), new MapValue(profileObj) )
);
System.out.println("Record added: "+ client.get(null, myRecKey));
}
//Add few records ...
String binName = "myMapBin1";
addRecord(1, binName, 3, 1714481010041L, 1715085810041L, 1714481010041L, "VI3");
addRecord(3, binName, 4, 1714481010041L, 1715085810041L, 1714481010041L, "VI4");
addRecord(4, binName, 6, 1714481010041L, 1715085810041L, 1714481010041L, "VI6");
addRecord(5, binName, 2, 1714481010041L, 1715085810041L, 1714481010041L, "VI2");
addRecord(6, binName, 3, 1714481010041L, 1715085810041L, 1714481010041L, "VI3");
Output:
Record added: (gen:1),(exp:453148483),(bins:(myMapBin1:{profile={pct=1714481010041, lut=1714481010041, meta={provider=VI3}, nlt=1715085810041, status=3}}))
Record added: (gen:1),(exp:453148483),(bins:(myMapBin1:{profile={pct=1714481010041, lut=1714481010041, meta={provider=VI4}, nlt=1715085810041, status=4}}))
Record added: (gen:1),(exp:453148483),(bins:(myMapBin1:{profile={pct=1714481010041, lut=1714481010041, meta={provider=VI6}, nlt=1715085810041, status=6}}))
Record added: (gen:1),(exp:453148483),(bins:(myMapBin1:{profile={pct=1714481010041, lut=1714481010041, meta={provider=VI2}, nlt=1715085810041, status=2}}))
Record added: (gen:1),(exp:453148483),(bins:(myMapBin1:{profile={pct=1714481010041, lut=1714481010041, meta={provider=VI3}, nlt=1715085810041, status=3}}))
Next cell, run the SI query:
//Run SI query
Filter filter = Filter.equal("myMapBin1", 3,
CTX.mapIndex(0), CTX.mapKey(Value.get("status")));
//Note: Use same CTX construct as the SI declaration
// While CTX.mapKey("profile"), CTX.mapKey("status")
// will point to same value, it will result in sindex not found error.
Statement stmt = new Statement();
stmt.setNamespace("test");
stmt.setSetName("testset");
stmt.setFilter(filter);
RecordSet rs = client.query(null, stmt);
while (rs.next()) {
Key key = rs.getKey();
Record record = rs.getRecord();
System.out.format("key=%s bins=%s\n", key.userKey, record.bins);
}
rs.close();
Output:
key=key6 bins={myMapBin1={profile={pct=1714481010041, lut=1714481010041, meta={provider=VI3}, nlt=1715085810041, status=3}}}
key=key1 bins={myMapBin1={profile={pct=1714481010041, lut=1714481010041, meta={provider=VI3}, nlt=1715085810041, status=3}}}
How do i create an index for MAP(‘{“90011”:{“OPTIN”:[{“Channel”:“ALL”, “GroupID”:182, “Mode”:“Website”, “Timestamp”:1693310935839}], “OPTIN_GROUP”:[182], “OPTOUT”:[{“Channel”:“ALL”, “GroupID”:184, “Mode”:“Website”, “Timestamp”:1693310935839}], “OPTOUT_GROUP”:[184]}}’
These List elements
“OPTIN_GROUP”:[182]
“OPTOUT_GROUP”:[184]
iam using this asadm command manage sindex create numeric OPTIN_Groups_Idx ns wiselyUserProfile set PROFILE_DATA bin Groups ctx map_key(OPTIN_GROUP)
it is not creating any index with this
Delete the current secondary index using asadm and create the new secondary index. The existing data will be automatically re-indexed.
I am not sure what you named your current index… RCS? And your bin is also RCS?
asadm
Admin>enable
Admin+>manage sindex delete RCS ns USP set PROFILE_DATA
Admin+>manage sindex create numeric idx_RCS ns USP set PROFILE_DATA bin RCS ctx map_index(0) map_key(status)
Admin+> info sindex
It should show number of records indexed … under “Keys” in the output
Here is my example output - showing 5 records were indexed. (I named my index idx_mapval)
Admin+> info sindex
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Secondary Index Information (2024-05-07 04:54:33 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Index Name|Namespace| Set| Node| Bin| Bin| State| Keys|~~~~~~~~Entries~~~~~~~~|~~~~Storage~~~~| Context
| | | | | Type| | | Total|Avg Per|Avg Per| Type| Used|
| | | | | | | | | Rec|Bin Val| | |
idx_mapval|test |testset|ip-172-31-3-62.ec2.internal:3000|myMapBin1|numeric|Read-Write|0.000 |5.000 |0.000 |0.000 |shmem|16.000 MB|[map_index(0), map_key(<string#6>)]
|test |testset| | | | | |5.000 | |0.000 | |16.000 MB|
Oh, you edited your question. That is a different data model. OPTIN_GROUP is the key, value is a list, currently only shows one element, but could have more than 1 integer in the list I assume? Likewise for OPTOUT_GROUP? so could be (?):
"OPTIN_GROUP": [ 182, 189, 190]
yes for status i was able to create now
but for this model iam not able to create
MAP(‘{“90011”:{“OPTIN”:[{“Channel”:“ALL”, “GroupID”:182, “Mode”:“Website”, “Timestamp”:1693310935839}], “OPTIN_GROUP”:[182], “OPTOUT”:[{“Channel”:“ALL”, “GroupID”:184, “Mode”:“Website”, “Timestamp”:1693310935839}], “OPTOUT_GROUP”:[184]}}’
These List elements
“OPTIN_GROUP”:[182]
“OPTOUT_GROUP”:[184]
iam using this asadm command manage sindex create numeric OPTIN_Groups_Idx ns wiselyUserProfile set PROFILE_DATA bin Groups ctx map_key(OPTIN_GROUP)
it is not creating any index with this
Yes we can have more values as you have stated
You ctx is map_key(90011) map_key(OPTIN_GROUP) and your data is list values - so you will need in list
Try:
manage sindex create numeric OPTIN_Groups_Idx ns wiselyUserProfile set PROFILE_DATA bin Groups in list ctx map_key(90011) map_key(OPTIN_GROUP)
map_key(90011) or map_index(0) …either would work in this model, just refer the same combination in the SI query ctx definition. i.e. whatever combo you use to declare to sindex should be used in the query.
But Here in this case map_key(90011) is dynamic in nature it is not static key
so you can use map_index(0) - I assume there is always some key in that model position - one record may be 90011, other may be 90032 … right?
we will have some thing like this { “9001”:{}, “9002”:{}, “9003”:{}, . . . }
You can only declare a secondary index to one and only one specific navigable context. You cannot have one SI definition index multiple locations in a map bin.
i.e. if you have
mapBin = { “9001”:{ "OG":[182,183] }, “9002”:{ "OG:: [192,193] }, “9003”:{ "OG": [200,201]}, . . . }
What you cannot do is declare one sindex on all “OG” list values and expect to find a match in any of the above 3 lists.
What you can do is declare 3 sindexes - 1st idx1 to: 9001->OG 2nd idx2 to 9002->OG and 3rd idx3 to 9003->OG … then you have to run 3 separate queries - depending on which OG you want to search for. using idx1 you may want to match for 182 or using idx2 find match for 193 or using idx3 find match for 201.
Aerospike will not iterate (i.e. find me a match in either of idx1, idx2 and idx3) inside a CDT - can’t do. That is a current design limitation.
If you broke these into separate records like so:
rec1: mapBin = { “9001”:{ "OG":[182,183] }}
rec2: mapBin = { “9002”:{ "OG":[192,193] }}
rec3: mapBin = { “9003”:{ "OG":[202,183] }}
then you can have a single sindex with map_index(0) map_key(OG) context and find the matching records.
BTW - are you using Community Edition or Enterprise Edition of Aerospike?
Yes community edition only