A simple index is a secondary index that has only one indexed field and
zero or more included fields. Simple indexes enable you to optimize queries that filter and
sort on a single field. If all fields referenced in a query are either indexed or included
fields in a simple index, then you can process the query by reading only the index.
Sort Order
HPE Ezmeral Data Fabric Database sorts simple indexes on the single indexed field. HPE Ezmeral Data Fabric Database sorts the indexed field
values in ascending order by default, although you can specify a descending order when you
create the index. Sorting indexes benefits your ORDER BY queries because the index
eliminates the need for a SORT operator in the query plan.
Simple Index Examples
The following CLI
commands demonstrate how you can create various types of simple indexes. For these
examples, assume that you have a HPE Ezmeral Data Fabric Database JSON table with the
following sample data:
{
"_id": "10000",
"FullName": {
"LastName": "Smith",
"FirstName": "John"
},
"Address": {
"Street": "123 SE 22nd St.",
"City": "Oakland",
"State": "CA",
"Zipcode": "94601-1001"
},
"Gender": "M",
"AccountBalance": 999.99,
"Email": "john.smith@company.com",
"Phones": [
{"Type": "Home", "Number": "555-555-1234"},
{"Type": "Mobile", "Number": "555-555-5678"},
{"Type": "Work", "Number": "555-555-9012"}
],
"Hobbies": ["Baseball", "Cooking", "Reading"],
"DateOfBirth": "10/1/1985"
}
CLI Command |
Description |
maprcli table index add -path /people \
-index emailIdx \
-indexedfields Email
|
- Creates a simple index on the
Email field, with no included
fields
- Enables you to filter on the
Email field
|
maprcli table index add -path /people \
-index dobIdx \
-indexedfields DateOfBirth \
-includedfields FullName
|
- Creates a simple index, with an included field
- Enables you to filter on
DateOfBirth and project on
FullName
|
maprcli table index add -path /people \
-index LastNameIdx \
-indexedfields FullName.LastName:-1
|
- Creates a simple index on the
FullName.LastName subfield, as
a descending sort key
- Allows you to filter on
FullName.LastName and sort on the
subfield in descending order
|
maprcli table index add -path /people \
-index fullNameIdx \
-indexedfields FullName
|
- Creates a simple index on the nested document field
FullName
- Allows you to perform equality lookups on both the
LastName
and FirstName subfields of FullName
|
maprcli table index add -path /people \
-index hobbiesIdx \
-indexedfields Hobbies
|
- Creates a simple index on the
Hobbies array field
- Allows you to filter for a specific list of hobbies
|
maprcli table index add -path /people \
-index hobbyIdx \
-indexedfields Hobbies[]
|
- Creates a simple index using the container field path
Hobbies[]
- Allows you to filter for a specific hobby
|
maprcli table index add -path /people \
-index phoneNumberIdx \
-indexedfields Phones[].Number
|
- Creates a simple index on the container field path
Phones[].Number
- Allows you to filter for a phone number, regardless of whether it is a home,
work, or cell phone
|