Skip to main content
Loading

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 OnKey TypeIndex TypeEligible Secondary Index Key
foostringMAPKEYSa, b, c, d, e
foonumericMAPVALUES1, 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.