# Support for secondary index

Aerospike 6.0 EE introduces extensive support for secondary index. The Spark connector leverages the secondary index to reduce query latency.

**Prerequisites**

-   Aerospike database 6.0 or later
-   Spark connector 3.4.0 or later

### Notes on filter string `aerospike.sindex.filter`:

-   Connector supports `equal`, `range`, `contains` [filters](https://javadoc.io/doc/com.aerospike/aerospike-client-jdk8/latest/com/aerospike/client/query/Filter.html). These filters can be specified as a JSON string.
    
-   Filters support `NUMERIC` and `STRING` types. `NUMERIC` types can be `Long`, `Int`, `Short`, `Date` or `TimeStamp`. Filters do not support `Double` and `Float` data types. `null` values are not indexed by Aerospike database. If an implicitly-constructed filter contains `null` when `aerospike.sindex.filter` is not set, the query does not use the provided secondary index.
    
-   In the case of an incompatible combination of `aerospike.sindex` and `aerospike.sindex.filter`, the database will throw an error.
    
-   `equal` filter
    
    -   Only supports scalar types (collection data types not supported)
        
    -   | JSON Key | Description |
        | --- | :-: |
        | `name` | bin name |
        | `type` | datatype `NUMERIC` or `STRING` |
        | `value` | value of the bin |
        
    -   | Examples | Equivalent SQL |
        | --- | :-: |
        | `{ "name": "bin1", "type": "NUMERIC", "value": 10}` | `select * from namespace where col("bin1") == 10` |
        | `{ "name": "bin1", "type": "NUMERIC", "value": "2012-10-03"}` | `select * from namespace where col("bin1") == java.sql.date.valueOf("2012-10-03")` |
        | `{ "name": "bin1", "type": "NUMERIC", "value": "2020-12-12 01:24:23"}` | `select * from namespace where col("bin1") == java.sql.Timestamp.valueOf("2020-12-12 01:24:23")` |
        | `{ "name": "bin1", "type": "STRING", "value": "McDonalds"}` | `select * from namespace where col("bin1") == "McDonalds"` |
        
-   `range` filter
    
    -   Only supports `NUMERIC` scalar types (collection data types not supported).
        
    -   | JSON Key | Description | | ------------- |:-------------: | | `name` | bin name| | `type` | datatype `NUMERIC` or `STRING` | | `begin`| start of the range | | `end`| end of the range |
        
    -   | Examples | Equivalent SQL | | ------- |:------: | `{ "name": "bin1", "type": "NUMERIC", "begin": 10, "end" : 20}`| `select * from namespace where col("bin1") >= 10 and col("bin1") <= 20` | `{ "name": "bin1", "type": "NUMERIC", "begin": "2012-10-03", "end" : "2013-10-03" }`| `select * from namespace where col("bin1") >= java.sql.date.valueOf("2012-10-03") and col("bin1") <= java.sql.date.valueOf("2013-10-03")` | `{ "name": "bin1", "type": "NUMERIC", "value": "2020-12-12 01:24:23", "end" : "2021-12-12 01:24:23"}`| `select * from namespace where col("bin1") >= java.sql.Timestamp.valueOf("2020-12-12 01:24:23") and col("bin1") <= java.sql.Timestamp.valueOf("2020-12-12 01:24:23")`|
        
-   `contains` filter
    
    -   Supports scalar and collection data type of `NUMERIC` and `STRING` types.
        
    -   | JSON Key | Description |
        | --- | :-: |
        | `name` | Bin name |
        | `type` | Datatype `NUMERIC` or `STRING` |
        | `colType` | [column type](https://javadoc.io/doc/com.aerospike/aerospike-client-jdk8/latest/com/aerospike/client/query/IndexCollectionType.html) or its equivalent numerical value (0,1,2,3…) |
        | `value` | Value of the bin |
        
    -   | Examples | Index Type | (Not So) Equivalent SQL |
        | --- | :-: | :-: |
        | `{ "name": "bin1", "type": "NUMERIC", "colType": "DEFAULT", "value" : 1}` |  | `select * from namespace where col("bin1") ==1` |
        | `{ "name": "bin1", "type": "NUMERIC", "colType": 1, "value" : 1}` | Indexed list | Find all records whose bin1 (of `ArrayType`) containing `10` as one of the values. |
        | `{ "name": "bin1", "type": "NUMERIC", "colType": 2, "value" : 1}` | Indexed map keys | Find all records whose bin1 (of `MapType`) containing `10` as map keys. |
        | `{ "name": "bin1", "type": "NUMERIC", "colType": 3, "value" : 1}` | Indexed map values | Find all records whose bin1 (of `MapType`) containing `10` as map values. |