dbshell indexscan

Description

The indexscan command scans secondary indexes and returns the document ID and the values of the indexed and included fields. This includes displaying information about errors encountered inserting into the index.

Syntax

maprdb root:> indexscan 
        <table path>
        --indexname <index name>
        --limit
        --withtags
        --pretty
        --mode
        --where
        --fields
        --decodeindexedfields    

Parameters

Example: Simple Index

The following example uses a simple index where index1 is on table1, field a.

// Insert one document
maprdb root:> insert /table1 --id 1 --value '{"a":7}'
Document with id: "1" inserted.

// Create index1 on table1 and index field a
# maprcli table index add -path /table1 -index index1 -indexedfields a
        
// Perform a normal indexscan; the _id field and the indexed field for the document is displayed
maprdb root:> indexscan /table1 --indexname index1
{"_id":"1","a":7}
1 document(s) found.
        
// Insert another document with _id value as 2 with field a as a map 
maprdb root:> insert /table1 --id 2 --value '{"a":[1,2,3]}'
        
// Perform a normal indexscan; the document that does not have the error is displayed
maprdb root:> indexscan /table1 --indexname index1
{"_id":"2","a":[1,2,3]}
{"_id":"1","a":7}
2 document(s) found.

// Perform an indexscan with error mode; no errors are displayed because MapR-DB 6.1 allows
// you to create indexes on array fields
maprdb root:> indexscan /table1 --indexname index1 --mode err
0 document(s) found.  

Example: Composite Index

The following example uses a composite index with included fields, in which index2 is on table table1, with indexed fields a and b and included field c.

// Insert a document with fields 'a', 'b' and 'c'. 
maprdb root:> insert /table1 --id 2 --value '{"a":7,"b":"mapr","c":"db"}'
Document with id: "2" inserted.

// Create index2 on table1 withindexed fields a and b, and included field c
# maprcli table index add -path /table1 -index index2 -indexedfields a,b -includedfields c
        
// Perform an indexscan
maprdb root:> indexscan /table1 --indexname index2
{"_id":"2","c":"db","a":7,"b":"mapr"}
1 document(s) found.
        
// Insert a document that has field a as a map
maprdb root:> insert /table1 --id 1 --value '{"a":{"m":4},"b":"mapr","c":"db"}'
Document with id: "1" inserted.
        
// Perform a normal indexscan
maprdb root:> indexscan /table1 --indexname index2
{"_id":"1","c":"db","a":{"m":4},"b":"mapr"}
{"_id":"2","c":"db","a":7,"b":"mapr"}
2 document(s) found.
        
// Perform an indexscan with error mode; no errors are displayed because MapR-DB 6.1 allows
// you to create indexes on array fields
maprdb root:> indexscan /table1 --indexname index2 --mode err
0 document(s) found.

Example: Index on Container Field Paths

Assume you have a table in the path /apps/indexExample with the following document:

{
   "_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"
}  

The following example creates a composite index on the Type and Number subfields in the nested documents in the Phones array:

// Create idx3 on the table with indexed fields Phones[].Type and Phones[].Number
# maprcli table index add -path /apps/indexExample -index idx3 \
    -indexedfields Phones[].Type,Phones[].Number
        
// Perform an indexscan WITHOUT the decodeindexedfields parameter.
// Three rows are returned, one for each element in the Phones[] array.
// The output contains no values for the indexed fields.
maprdb root:> indexscan /apps/indexExample --indexname idx3
{"_id":"10000"}
{"_id":"10000"}
{"_id":"10000"}
3 document(s) found
        
// Perform an indexscan WITH the decodeindexedfields parameter set to true.
// The output includes the values in the indexed fields.
maprdb mapr:> indexscan /apps/indexExample --indexname idx3 --decodeindexedfields true
{"_id":"10000","$idx":["Home","555-555-1234"]}
{"_id":"10000","$idx":["Mobile","555-555-5678"]}
{"_id":"10000","$idx":["Work","555-555-9012"]}
3 document(s) found.

Troubleshooting Use Cases

Situations where you can use this command are as follows:

See Troubleshooting Secondary Indexes for more information on these use cases.