Map indexes

Overview

This page describes how to create a secondary index on bins where the data type is a map. You can index either map key or map value.

Indexing on Map elements

Similar to basic indexing, the indexable map data types are numeric, string, and GeoJSON.
You can index a Map at any depth. Prior to Database 6.1, map indexing was only on the top-level element, not nested elements.
When creating index, specify explicitly that map bins should be indexed, and what data type to index on.
When querying, specify that query should be applied on a CDT data type.
Similar to basic querying, equality, range (for numeric and string data type), points-within-region, and region-containing-points (for GeoJSON data type) are supported.

Examples

The following example uses asadm to create an index with source type mapkeys and a String data type:

Admin+> manage sindex create string foo_mapkey_idx in mapkeys ns test set demo bin foo

The following example uses asadm to create an index with source type mapvalues with a Numeric data type:

Admin+> manage sindex create numeric foo_mapval_idx in mapvalues ns test set demo bin foo

Use ‘show sindex’ to confirm foo_mapval_idx was created successfully.

Example output:

Admin+> show sindex
~~~~~~~Secondary Indexes (2023-08-31 16:01:35 UTC)~~~~~~~
    Index Name|Namespace| Set|Bin|    Bin|    Index|State
              |         |    |   |   Type|     Type|
foo_mapkey_idx|test     |demo|foo|string |mapkeys  |RW
foo_mapval_idx|test     |demo|foo|numeric|mapvalues|RW
Number of rows: 2

Elements of the indexed list are type checked, so a record whose foo bin contains { a:1, b:"2", c:3, d:[4], e:5, 666:"zzz" } results in the following indexing:

Index On	Key Type	Index Type	Eligible Secondary Index Key
foo	string	MAPKEYS	a, b, c, d, e
foo	numeric	MAPVALUES	1, 3, 5

Map index queries

This example illustrates use of map indexes using AQL scripts. The example creates an index over the bin shopping_carts, which is a Map of products and the state each was ordered from, and queries the record by product name or state.

aql> show indexes
+--------+-------+-------------+--------+-------+------------------+-------+-----------+
| ns     | bin   | indextype   | set    | state | indexname        | path  | type      |
+--------+-------+-------------+--------+-------+------------------+-------+-----------+
| "test" | "foo" | "MAPKEYS"   | "demo" | "RW"  | "foo_mapkey_idx" | "foo" | "STRING"  |
| "test" | "foo" | "MAPVALUES" | "demo" | "RW"  | "foo_mapval_idx" | "foo" | "NUMERIC" |
+--------+-------+-------------+--------+-------+------------------+-------+-----------+
[127.0.0.1:3000] 2 rows in set (0.001 secs)

OK

aql> INSERT INTO test.demo (PK, username, foo) VALUES ("u1", "Bob Roberts", JSON('{"a":1, "b":"2", "c":3, "d":[4]}'))
OK, 1 record affected.

aql> INSERT INTO test.demo (PK, username, foo) VALUES ("u2", "rocketbob", MAP('{"c":3, "e":5}'))
OK, 1 record affected.

aql> INSERT INTO test.demo (PK, username, foo) VALUES ("u3", "samunwise", JSON('{"x":{"z":26}, "y":"yyy"}'))
OK, 1 record affected.

aql> SELECT username FROM test.demo IN MAPVALUES WHERE foo = 2
0 rows in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPVALUES WHERE foo = "2"
0 rows in set (0.000 secs)

Error: (201) AEROSPIKE_ERR_INDEX_NOT_FOUND

aql> SELECT username FROM test.demo IN MAPVALUES WHERE foo = 1
+---------------+
| username      |
+---------------+
| "Bob Roberts" |
+---------------+
1 row in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPVALUES WHERE foo = 3
+---------------+
| username      |
+---------------+
| "Bob Roberts" |
| "rocketbob"   |
+---------------+
2 rows in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPVALUES WHERE foo BETWEEN 1 AND 5
+---------------+
| username      |
+---------------+
| "Bob Roberts" |
| "Bob Roberts" |
| "rocketbob"   |
| "rocketbob"   |
+---------------+
4 rows in set (0.001 secs)

aql> SELECT username FROM test.demo IN MAPKEYS WHERE foo = "y"
+-------------+
| username    |
+-------------+
| "samunwise" |
+-------------+
1 row in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPKEYS WHERE foo = "x"
+-------------+
| username    |
+-------------+
| "samunwise" |
+-------------+
1 row in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPKEYS WHERE foo = "c"
+---------------+
| username      |
+---------------+
| "Bob Roberts" |
| "rocketbob"   |
+---------------+
2 rows in set (0.001 secs)

OK

aql> SELECT username FROM test.demo IN MAPKEYS WHERE foo = "z"
0 rows in set (0.001 secs)

OK

Known limitations

When using range queries on maps, records can be returned multiple times if the map contains multiple values that fall within the range.