Previous Next JavaScript must be enabled to correctly display this content

## Indexing Maps

You can create an index on a map field ( or a field of a map nested inside another array or map). An index created on a map field inside another array or map is a multi key index.

An index is called a multikey index if for each row of data in the table, there are multiple entries created in the index. In a multikey index, there is at least one index path that uses `.keys(), .values()`. Any such index path will be called a multikey index path. In a multikey index, for each table row, index entries are created on entries in maps that are being indexed. If the evaluation returns an empty result, the special value EMPTY is used as the index entry. Any duplicate index entries are then eliminated.

To create the index, define the map as normal. Once the map is defined for the table, there are several different ways to index it:

- Based on the map's keys without regard to the actual key values.

- Based on the map's values, without regard to the actual key used.

- By a specific map key. To do this, you specify the name of the map field and the name of a map key using dot notation. If the map key is ever created using your client code, then it will be indexed.

- Based on the map's key and value without identifying a specific value (such as is required by the previous option in this list).

### Indexing by Map Keys

You can create indexes based on a map's keys without regard to the corresponding values.

Be aware that creating an index like this can potentially result in multiple index entries for each row, which can lead to very large indexes.

First create the table:

    CREATE TABLE myMapTable (
        uid INTEGER,
        testMap MAP(INTEGER),
        PRIMARY KEY(uid)
    )

Once the table has been added to the store, create the index using the `.keys()` path step:

    CREATE INDEX mapKeyIndex on myMapTable (testMap.keys())

Data is retrieved if the table row contains the identified map with the identified key. So, for example, if you create a series of table rows like this:

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myMapTable");

    Row row = myTable.createRow();
    row.put("uid", 12345);
    MapValue mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    mv.put("field3", 3);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 12);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 666);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field3", 4);
    tableH.put(row, null, null);

then you can retrieve any table rows that contain the map with any key currently in use by the map. For example, "field3".

To retrieve data using a map index, you first retrieve the index using its name, and create an instance of `IndexKey` that you will use to perform the index lookup:

    Index mapIndex = myTable.getIndex("mapKeyIndex");
    IndexKey indexKey = mapIndex.createIndexKey();

Next, you populate the `IndexKey` instance with the field name that you want to retrieve. Use the `keys()` path step to indicate that you want to retrieve using the field name without regard for the field value. When you perform the index lookup, the only records that will be returned will be those which have a map with the specified key name:

    indexKey.put("testMap.keys()", "field3");

After that, you retrieve the matching table rows, and iterate over them in the same way you would any other index type. For example:

    TableIterator<Row> iter = tableH.tableIterator(indexKey, null, null);
    System.out.println("Results for testMap field3: ");
    try {
        while (iter.hasNext()) {
            Row rowRet = iter.next();
            int uid = rowRet.get("uid").asInteger().get();
            System.out.println("uid: " + uid);
            MapValue mapRet = rowRet.get("testMap").asMap();
            System.out.println("testMap: " + mapRet.toString());
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

### Indexing by Map Values

You can create indexes based on the values contained in a map without regard to the keys in use.

Be aware that creating an index like this can potentially result in multiple index entries for each row, which can lead to very large indexes.

First create the table:

    CREATE TABLE myMapTable (
        uid INTEGER,
        testMap MAP(INTEGER),
        PRIMARY KEY(uid)
    )

Once the table has been added to the store, create the index using the `.values()` path step:

    CREATE INDEX mapElementIndex on myMapTable (testMap.values())

Data is retrieved if the table row contains the identified map with the identified value. So, for example, if you create a series of table rows like this:

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myMapTable");

    Row row = myTable.createRow();
    row.put("uid", 12345);
    MapValue mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    mv.put("field3", 3);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 12);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 666);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field3", 4);
    tableH.put(row, null, null);

then you can retrieve any table rows that contain the map with any value currently in use by the map. For example, a value of "2".

To retrieve data using a map index, you first retrieve the index using its name, and create an instance of `IndexKey` that you will use to perform the index lookup:

    Index mapIndex = myTable.getIndex("mapElementIndex");
    IndexKey indexKey = mapIndex.createIndexKey();

Next, you populate the `IndexKey` instance with the field value (`2`) that you want to retrieve. Use the `values()` path step with the field name to indicate that you want to retrieve entries based on the value only. When you perform the index lookup, the only records that will be returned will be those which have a map with a value of `2`.

    indexKey.put("testMap.values()", 2);

After that, you retrieve the matching table rows, and iterate over them in the same way you would any other index type. For example:

    TableIterator<Row> iter = tableH.tableIterator(indexKey, null, null);
    System.out.println("Results for testMap value 2: ");
    try {
        while (iter.hasNext()) {
            Row rowRet = iter.next();
            int uid = rowRet.get("uid").asInteger().get();
            System.out.println("uid: " + uid);
            MapValue mapRet = rowRet.get("testMap").asMap();
            System.out.println("testMap: " + mapRet.toString());
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

### Indexing by a Specific Map Key Name

You can create an index based on a specified map key name. Any map entries containing the specified key name are indexed. This can create a small and very efficient index because the index does not contain every key/value pair contained by the map fields. Instead, it just contains those map entries using the identified key, which results in at most a single index entry per row.

To create the index, first create the table:

    CREATE TABLE myMapTable (
        uid INTEGER,
        testMap MAP(INTEGER),
        PRIMARY KEY(uid)
    )

Once the table has been added to the store, create the index by specifying the key name you want indexed using dot notation. In this example, we will index the key name of "field3":

    CREATE INDEX mapField3Index on myMapTable (testMap.field3)

Data is retrieved if the table row contains the identified map with the indexed key and a specified value. So, for example, if you create a series of table rows like this:

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myMapTable");

    Row row = myTable.createRow();
    row.put("uid", 12345);
    MapValue mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    mv.put("field3", 3);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 12);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 666);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field3", 4);
    tableH.put(row, null, null);

then you can retrieve any table rows that contain the map with key "field3" (because that is what you indexed) when "field3" maps to a specified value — such as "3". If you try to do an index lookup on, for example, "field2" then that will fail because you did not index "field2".

To retrieve data using a map index, you first retrieve the index using its name and create an instance of `IndexKey` that you will use to perform the index lookup:

    Index mapIndex = myTable.getIndex("mapField3Index");
    IndexKey indexKey = mapIndex.createIndexKey();

Then you populate the map field name (using dot notation) and the desired value using `IndexKey.put()`. When you perform the index lookup, the only records that will be returned will be those which have a map with the matching key name and corresponding value.

    indexKey.put("testMap.field3", 3);

After that, you retrieve the matching table rows, and iterate over them in the same way you would any other index type. For example:

    TableIterator<Row> iter = tableH.tableIterator(indexKey, null, null);
    System.out.println("Results for testMap field3, value 3: ");
    try {
        while (iter.hasNext()) {
            Row rowRet = iter.next();
            int uid = rowRet.get("uid").asInteger().get();
            System.out.println("uid: " + uid);
            MapValue mapRet = rowRet.get("testMap").asMap();
            System.out.println("testMap: " + mapRet.toString());
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

### Indexing by Map Key and Value

In the previous section, we showed how to create a map index by specifying a pre-determined key name. This allows you to perform map index look ups by providing both key and value, but the index lookup will only be successful if the specified key is the key that you indexed.

You can do the same thing in a generic way by indexing every key/value pair in your map. The result is a more flexible index, but also an index that is potentially much larger than the previously described method. It is likely to result in multiple index entries per row.

To create an index based on every key/value pair used by the map field, first create the table:

    CREATE TABLE myMapTable (
        uid INTEGER,
        testMap MAP(INTEGER),
        PRIMARY KEY(uid)
    )

Once the table has been added to the store, create the index by using the `.keys()` and `.values()` path steps:

    CREATE INDEX mapKeyValueIndex on myMapTable
    (testMap.keys(),testMap.values())

Data is retrieved if the table row contains the identified map with the identified key and the identified value. So, for example, if you create a series of table rows like this:

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myMapTable");

    Row row = myTable.createRow();
    row.put("uid", 12345);
    MapValue mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    mv.put("field3", 3);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 12);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field2", 2);
    tableH.put(row, null, null);

    row = myTable.createRow();
    row.put("uid", 666);
    mv = row.putMap("testMap");
    mv.put("field1", 1);
    mv.put("field3", 4);
    tableH.put(row, null, null);

then you can retrieve any table rows that contain the map with specified key/value pairs — for example, key "field3" and value "3".

To retrieve data using a map index, you first retrieve the index using its name and create an instance of `IndexKey` that you will use to perform the index lookup:

    Index mapIndex = myTable.getIndex("mapKeyValueIndex");
    IndexKey indexKey = mapIndex.createIndexKey();

Next, you populate the `IndexKey` class instance with the field name and value you want to retrieve. In this case, you must specify two sets of information, using two calls to `IndexKey.put()`:

- The name of the field. Here, use the `keys()` path step with the field name.

- The field value you want to retrieve. Here, use the `values()` path step the field name.

For example:

    indexKey.put("testMap.keys()", "field3");
    indexKey.put("testMap.values()", 3);

When you perform the index lookup, the only records that will be returned will be those which have a map with the matching key/value pair. Once you have performed the index lookup, you retrieve the matching table rows, and iterate over them in the same way you would any other index type. For example:

    TableIterator<Row> iter = tableH.tableIterator(indexKey, null, null);
    System.out.println("Results for testMap field3, value 3: ");
    try {
        while (iter.hasNext()) {
            Row rowRet = iter.next();
            int uid = rowRet.get("uid").asInteger().get();
            System.out.println("uid: " + uid);
            MapValue mapRet = rowRet.get("testMap").asMap();
            System.out.println("testMap: " + mapRet.toString());
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }