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

Parameters Description

*, --t, --table

(Required)

Path of the JSON table

--indexname, --indexName

(Required)

Name of the secondary index
--limit Maximum number of documents to return
--withtags, --withTags

Enables or disables printing with extended JSON type tags

Values: true|false

--pretty

Enables or disables pretty printing of documents

Values: true|false

--mode

Enables display of the error information for the index

Value: err

If you specify --mode err, the command scans only rows with errors and prints the _id and $ERROR fields. If you do not specify --mode, the command prints the _id, indexed, and included fields of rows that do not have errors.

The following lists the types of errors:
  • KEY_TOO_LONG
  • INVALID_CAST
--c, --where

Condition, in JSON format, that filters the rows returned

See OJAI Query Condition Syntax for a description of the syntax.

--f, --fields

Fields from the index to return

See JSON Document Field Paths for details about how to specify field paths.

--decodeindexedfields

Enables display of values for indexed fields that are nested documents or arrays

Value: true

Note: This parameter ignores all other values, including specifying no value.

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:

  • List the contents of an index.
  • Resolve encoding errors encountered inserting into an index.

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