Index and Query Maps

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

note

The aql tool doesn't allow for numeric map keys, so those aren't used in this example. However, that is not a limitation of the Aerospike server, or of the Aerospike clients for Java, Python, Go, etc.

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.