Query with --query

Syntax

find <table path> --query <keywords>

For example, the following command does not return your desired results:

find /tbl --f a --q {"$limit":2}

In addition, repetition of keywords in the --query option is not supported. You should not enter the same keyword twice:

// Incorrect
{"$select":"a","$select":"b"}

// Correct
{"$select":["a","b"]}

Keywords for the --query Option

The --query option supports the following keywords:

The following sections provide examples of each keyword. For more details, see OJAI Query Syntax.

Sample JSON Document

The examples in this topic use the following sample JSON document:

{
    "_id": "id1",
    "a": {
            "b": [{"boolean":false}, {"decimal": 123.456}],
            "c":{
                  "d":10,
                  "e":"Hello"
                 }
          },
    "m":"MapR wins"
}       

$select Syntax and Example

The $select keyword defines the field path projections to be displayed in the result set.

The following syntax shows single and multiple field path projections:

// Single field path projection syntax
find <table path> --q {"$select":"<fieldpath>"}
            
// Multiple field path projection syntax
find <table path> --q {"$select":["<fieldpath1>","<fieldpath2>","<fieldpath3>"]}

The following examples show single and multiple field path projections:

// Single field path projection example
find /tbl --q {"$select":"a.c.d"}
            
// Multiple field path projection example
find /tbl --q {"$select":["a.c.d","a.c.e","m"]}

See OJAI Query Projection for more information about $select.

$where Syntax and Example

When using the $where keyword, define the condition using OJAI Query Condition Syntax.

find <table path> --q {"$where":<condition>}

The following example performs a find operation with a projection and a condition:

find /tbl --q {"$select":"a.c.e",
            "$where":{
                     "$and":[
                             {"$eq":{"a.b[0].boolean":false}},
                             {"$or":[
                                     {"$ne":{"a.c.d":5}},
                                     {"$gt":{"a.b[1].decimal":1}},
                                     {"$lt":{"a.b[1].decimal":10}}
                                     ]
                              }
                             ]
                      }
               }

(a.b.[0].boolean == false && (a.c.d != 5 || a.b[1].decimal > 1 || a.b[1].decimal < 10))

$limit Syntax and Example

The $limit keyword sets the maximum number of documents to return. It only accepts positive integers. It throws an exception for negative or decimal values.

find <table path> --q {"$limit":<positive integer>}

The following example performs a find with a projection on the a.c.e and m fields and limits the result set to a max of 10 documents:

find /tbl --q {"$select":["a.c.e","m"],
               "$limit":10
              }

See OJAI Query Limit for more information about $limit.

$offset Syntax and Example

The $offset keyword skips the first n number of rows in the result. If n is greater than the total number of documents, no documents are returned. It only accepts positive integers.

find <table path> --q {"$offset":<positive integer>}

The following example performs a find operation with projection on the a.c.e and m fields and offsets the result set to skip first five documents:

find /tbl --q {"$select":["a.c.e","m"],
               "$offset":5
              }

See OJAI Query Offset for more information about $offset.

$orderby Syntax and Examples

The $orderby keyword sorts the result on the specified fields.

The following shows the syntax and example of sorting a single field in the default ascending order:

// Syntax for sorting a single field in the default ascending order
find <table path> --q {"$orderby":"<field path>"}
            
// Example sort on field path a.c.e in the default ascending order
find /tbl --q {"$orderby":"a.c.e"}  

The following show the syntax and examples of sorting a single field in ascending or descending order where <order> is ASC for ascending and DESC for descending:

// Syntax for sorting a single field in ASC/DESC order
find <table path> --q {"$orderby":{"<field path>":"<order>"}}
            
// Example sort on field path a.c.e in ascending order
find /tbl --q {"$orderby":{"a.c.e":"asc"}}
            
// Example sort on field path a.c.e in descending order
find /tbl --q {"$orderby":{"a.c.e":"desc"}}

The following shows the syntax and an example of sorting multiple fields in ascending and descending order:

// Syntax for sorting multiple field paths in ascending/descending order
find <table path> --q {"$orderby":[{"<field path1>":"<order>"},
                                   {"<field path2>":"<order>"},
                                   {"<field path3>":"<order>"}
                                  ]
                      }
            
// Example sort on field path a.c.d (in the default ascending order)
// and field path a.c.e in descending order
find /tbl --q {"$orderby":["a.c.d",{"a.c.e":"desc"}]}

See OJAI Query Order By for more information about $orderby.

$options Syntax and Example

The $options keyword enables you to influence a query's execution path. The general syntax is as follows:

find <table path> --q {"$options":{<option name>:<option value>}}

When specifying the <option name>, you must separate the components of the option name, replacing the dots with curly braces and colons and enclosing each component in quotes. The following example shows you how to do this for the ojai.mapr.query.hint-using-index option. The example forces the query to use a secondary index named colIndex:

find /apps/test --q {
    "$where":{"$eq":{"col":10}},
    "$options":{"ojai":{"mapr":{"query":{"hint-using-index":"colIndex"}}}}
    }

See OJAI Query Options for a complete list of available query options.