# How to sindex in aql queries on map bins

**URL:** <https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058>\
**Category:** AQL\
**Created:** [May 3, 2024, 2:02pm UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058 "2024-05-03T14:02:23Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 3, 2024, 2:02pm UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/1 "2024-05-03T14:02:23Z")

</div>

How to use sindex in aql queries on map bins

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 3, 2024, 5:59pm UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/2 "2024-05-03T17:59:22Z")

</div>

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.

```auto
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:

```auto
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.

```auto
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:

```auto
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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 5, 2024, 4:57am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/3 "2024-05-05T04:57:21Z")

</div>

> [@pgupta](#):
>
> `in MAPVALUES where`

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)]

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 5, 2024, 6:23am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/4 "2024-05-05T06:23:19Z")

</div>

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:

```auto
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.)

```auto
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.**

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 6, 2024, 4:13am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/5 "2024-05-06T04:13:22Z")

</div>

If create the index without passing the ctx i dont see any thing getting indexed . it is showing as 0 for all values

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 6, 2024, 4:24am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/6 "2024-05-06T04:24:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 6, 2024, 6:16am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/7 "2024-05-06T06:16:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 6, 2024, 6:40pm UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/8 "2024-05-06T18:40:03Z")

</div>

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.

```auto
//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:

```auto
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:

```auto
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:

```auto
//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:

```auto
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}}}

```

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 4:34am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/9 "2024-05-07T04:34:54Z")

</div>

> [@pgupta](#):
>
> myMapBin1

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

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 4:55am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/10 "2024-05-07T04:55:09Z")

</div>

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?

```auto
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)

```auto
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|                            

```

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 4:58am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/11 "2024-05-07T04:58:18Z")

</div>

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 (?):

```auto
"OPTIN_GROUP": [182, 189, 190] 

```

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 4:59am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/12 "2024-05-07T04:59:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 5:02am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/13 "2024-05-07T05:02:27Z")

</div>

Yes we can have more values as you have stated

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 5:09am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/14 "2024-05-07T05:09:32Z")

</div>

> [@PKuchibhatla](#):
>
> 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] }
> 
> }’

You ctx is map\_key(90011) map\_key(OPTIN\_GROUP) and your data is list values - so you will need `in list`

Try:

```auto
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.

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 5:13am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/15 "2024-05-07T05:13:08Z")

</div>

> [@pgupta](#):
>
> `map_key(90011)`

But Here in this case map\_key(90011) is dynamic in nature it is not static key

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 5:13am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/16 "2024-05-07T05:13:38Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 5:15am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/17 "2024-05-07T05:15:59Z")

</div>

we will have some thing like this { “9001”:{}, “9002”:{}, “9003”:{}, . . . }

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 5:23am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/18 "2024-05-07T05:23:56Z")

</div>

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

```auto
 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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![pgupta](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pgupta/32/2351_2.png) [@pgupta](https://discuss.aerospike.com/u/pgupta)\
**Post date:** [May 7, 2024, 5:26am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/19 "2024-05-07T05:26:29Z")

</div>

BTW - are you using Community Edition or Enterprise Edition of Aerospike?

---

<div class="post-metadata">

**Author:** ![PKuchibhatla](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/pkuchibhatla/32/2284_2.png) [@PKuchibhatla](https://discuss.aerospike.com/u/PKuchibhatla)\
**Post date:** [May 7, 2024, 5:35am UTC](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058/20 "2024-05-07T05:35:27Z")

</div>

Yes community edition only

[Next page](https://discuss.aerospike.com/t/how-to-sindex-in-aql-queries-on-map-bins/11058.md?page=2)