You can create an index on a map field so long as the map contains scalar data, or contains a record with scalar fields.
You cannot index a map or array that is nested beneath another map or array. This is not allowed because of the potential for an excessively large number of index entries.
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).
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. In addition, the same row can appear in a result set, so the duplicate entries must be handled by your application.
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 KEYOF statement:
CREATE INDEX mapKeyIndex on myMapTable (KEYOF(testMap))
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:
function writeRow(store, uid, map) {
var row = {
uid: uid,
testMap: map
};
store.put('myMapTable', row,
function (err) {
if (err)
throw err;
else {
console.log("Row inserted.");
}
});
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
writeRow(store, 12345, {field1: 1, field2: 2, field3: 3});
writeRow(store, 12, {field1: 1, field2: 2});
writeRow(store, 666, {field1: 1, field3: 4});
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
then you can retrieve any table rows that contain the map with any key currently in use by the map. For example, "field3".
Note that we use a simple Javascript object to represent the map.
Because the index we are using is based on the map's key, we
can provide any value we want for the map's key value. Here we
just use null, but any other value would
provide identical results.
function getIndexByFieldName(store, fieldName) {
var obj = {}
obj[fieldName] = null;
var indexKey1 = {testMap: obj}
store.indexIterator('myMapTable', 'mapKeyIndex',
{indexKey: indexKey1},
function (err, iterator) {
iterator.forEach(function (err, currentRow) {
console.log(currentRow.row);
});
}
);
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
getIndexByFieldName(store, "field3");
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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. In addition, the same row can appear in a result set, so the duplicate entries must be handled by your application.
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 ELEMENTOF statement:
CREATE INDEX mapElementIndex on myMapTable (ELEMENTOF(testMap))
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:
function writeRow(store, uid, map) {
var row = {
uid: uid,
testMap: map
};
store.put('myMapTable', row,
function (err) {
if (err)
throw err;
else {
console.log("Row inserted.");
}
});
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
writeRow(store, 12345, {field1: 1, field2: 2, field3: 3});
writeRow(store, 12, {field1: 1, field2: 2});
writeRow(store, 666, {field1: 1, field3: 4});
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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".
Notice in the following example that we use the special string "[]" for the index key's field value. The field name must be that string or we will not access the proper index.
function getIndexByMapValue(store, mv) {
var obj = {};
obj["[]"] = mv;
var indexKey1 = {testMap: obj };
store.indexIterator('myMapTable', 'mapElementIndex',
{indexKey: indexKey1},
function (err, iterator) {
iterator.forEach(function (err, currentRow) {
console.log(currentRow.row);
});
}
);
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
getIndexByMapValue(store, 2);
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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:
function writeRow(store, uid, map) {
var row = {
uid: uid,
testMap: map
};
store.put('myMapTable', row,
function (err) {
if (err)
throw err;
else {
console.log("Row inserted.");
}
});
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
writeRow(store, 12345, {field1: 1, field2: 2, field3: 3});
writeRow(store, 12, {field1: 1, field2: 2});
writeRow(store, 666, {field1: 1, field3: 4});
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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".
function getIndexByField3KeyName(store, mv) {
var indexKey1 = {testMap: {field3: mv}};
store.indexIterator('myMapTable', 'mapField3Index',
{indexKey: indexKey1},
function (err, iterator) {
iterator.forEach(function (err, currentRow) {
console.log(currentRow.row);
});
}
);
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
getIndexByField3KeyName(store, 3);
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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 both the KEYOF and
ELEMENTOF keywords:
CREATE INDEX mapKeyValueIndex on myMapTable \ (KEYOF(testMap),ELEMENTOF(testmap))
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:
function writeRow(store, uid, map) {
var row = {
uid: uid,
testMap: map
};
store.put('myMapTable', row,
function (err) {
if (err)
throw err;
else {
console.log("Row inserted.");
}
});
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
writeRow(store, 12345, {field1: 1, field2: 2, field3: 3});
writeRow(store, 12, {field1: 1, field2: 2});
writeRow(store, 666, {field1: 1, field3: 4});
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();
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 based on this kind of an index, you must provide:
the special string '[]' with the desired map value; and
the field name with
null
for a map value.
You do this in node.js in the following way:
function getIndexByKeyAndValue(store, fn, mv) {
// declare and initialize the object
var indexKey1 = {};
indexKey1["testMap"] = {};
// Now set the information to the object so that the
// proper index is used, and data is retrieved by our
// desired values.
indexKey1.testMap["[]"] = mv; // set the map value
indexKey1.testMap[fn] = null; // set the field name
store.indexIterator('myMapTable', 'mapKeyValueIndex',
{indexKey: indexKey1},
function (err, iterator) {
iterator.forEach(function (err, currentRow) {
console.log(currentRow.row);
});
}
);
}
...
// Store handle configuration and open skipped for brevity
...
store.on('open', function () {
console.log('Store opened');
getIndexByKeyAndValue(store, "field3", 3);
store.close();
}).on('close', function() {
console.log('Store closed.');
}).on('error', function(error) {
console.log(error);
});
store.open();