Querying with Order By

The examples in this section query a document store and return specific fields from the documents, sorted in a specific order. One of the examples also uses offset and limit.

NOTE
You can improve the performance of order by queries by using secondary indexes. See Using Indexes to Optimize ORDER BY Queries for more information.

The following example shows how to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sorting the documents by _id. It uses the Query.select and Query.orderBy methods.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderby method call as follows:
orderBy("_id", SortOrder.DESC)
/**
 * Copyright (c) 2017 MapR, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 */
package com.mapr.ojai.examples;

import org.ojai.Document;
import org.ojai.DocumentStream;
import org.ojai.store.Connection;
import org.ojai.store.DocumentStore;
import org.ojai.store.DriverManager;
import org.ojai.store.Query;

public class OJAI_010_FindQueryWithOrderBy {

  public static void main(final String[] args) {

    System.out.println("==== Start Application ===");

    // Create an OJAI connection to MapR cluster
    final Connection connection = DriverManager.getConnection("ojai:mapr:");

    // Get an instance of OJAI DocumentStore
    final DocumentStore store = connection.getStore("/demo_table");

    // Build an OJAI query with an order by
    final Query query = connection.newQuery()
        .select("_id", "firstName", "lastName", "address.zipCode")
        .orderBy("_id")
        .build();

    // fetch all OJAI Documents from this store
    final DocumentStream stream = store.find(query);

    for (final Document userDocument : stream) {
      // Print the OJAI Document
      System.out.println(userDocument.asJsonString());
    }

    // Close this instance of OJAI DocumentStore
    store.close();

    // close the OJAI connection and release any resources held by the connection
    connection.close();

    System.out.println("==== End Application ===");
  }

}

The following example shows how to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sorting the documents by _id. It uses the Query.select and Query.orderBy methods. In addition, the returned documents are offset and limited by using the Query.offset and Query.limit methods.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderBy method call as follows:
orderBy("_id", SortOrder.DESC)
/**
 * Copyright (c) 2017 MapR, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 */
package com.mapr.ojai.examples;

import org.ojai.Document;
import org.ojai.DocumentStream;
import org.ojai.store.Connection;
import org.ojai.store.DocumentStore;
import org.ojai.store.DriverManager;
import org.ojai.store.Query;

public class OJAI_011_FindQueryWithOrderByLimitOffset {

  public static void main(final String[] args) {

    System.out.println("==== Start Application ===");

    // Create an OJAI connection to MapR cluster
    final Connection connection = DriverManager.getConnection("ojai:mapr:");

    // Get an instance of OJAI DocumentStore
    final DocumentStore store = connection.getStore("/demo_table");

    // Build an OJAI query with an order by, offset, and limit
    final Query query = connection.newQuery()
        .select("_id", "firstName", "lastName", "address.zipCode")
        .orderBy("_id")
        .offset(2)
        .limit(1)
        .build();

    // fetch all OJAI Documents from this store
    final DocumentStream stream = store.find(query);

    for (final Document userDocument : stream) {
      // Print the OJAI Document
      System.out.println(userDocument.asJsonString());
    }

    // Close this instance of OJAI DocumentStore
    store.close();

    // close the OJAI connection and release any resources held by the connection
    connection.close();

    System.out.println("==== End Application ===");
  }

}

The following example uses an OJAI query to return the _id and name fields from documents in a store and to sort the documents by _id.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderby specification as follows:
order_by('_id', desc)
/*
 * Copyright (c) 2018 MapR, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

const { ConnectionManager } = require('node-maprdb');

const connectionString = 'localhost:5678?' +
  'auth=basic;' +
  'user=mapr;' +
  'password=mapr;' +
  'ssl=true;' +
  'sslCA=/opt/mapr/conf/ssl_truststore.pem;' +
  'sslTargetNameOverride=node1.mapr.com';

let connection;

// Create a connection to data access server
ConnectionManager.getConnection(connectionString)
  .then((conn) => {
    connection = conn;
    // Get a store
    return connection.getStore('/demo_table');
  })
  .then((store) => {
    // Create an OJAI query
    const query = {"$select": ["_id", "name"], "$orderby": {"_id": "asc"}};
    // fetch OJAI Documents by query
    return store.find(query);
  })
  .then((queryResult) => {
    queryResult.on('data', (document) => {
      // Print OJAI Documents from document stream
      console.log(document);
    });
    queryResult.on('end', () => {
      // close the OJAI connection
      connection.close();
    });
  });

The following example uses an OJAI query to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sort the documents by _id, offset the result by two documents, and limit the result to a single document.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderby specification as follows: 
"$orderby": {"_id": "desc"}
/*
 * Copyright (c) 2018 MapR, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

const { ConnectionManager } = require('node-maprdb');

const connectionString = 'localhost:5678?' +
  'auth=basic;' +
  'user=mapr;' +
  'password=mapr;' +
  'ssl=true;' +
  'sslCA=/opt/mapr/conf/ssl_truststore.pem;' +
  'sslTargetNameOverride=node1.mapr.com';

let connection;

// Create a connection to data access server
ConnectionManager.getConnection(connectionString)
  .then((conn) => {
    connection = conn;
    // Get a store
    return connection.getStore('/demo_table');
  })
  .then((store) => {
    // Create an OJAI query
    const query = {"$offset": 2,
      "$select": ["_id",
        "firstName",
        "lastName",
        "address.zipCode"],
      "$limit": 1,
      "$orderby": {"_id": "asc"}};
    // fetch OJAI Documents by query
    return store.find(query);
  })
  .then((queryResult) => {
    queryResult.on('data', (document) => {
      // Print OJAI Documents from document stream
      console.log(document);
    });
    queryResult.on('end', () => {
      // close the OJAI connection
      connection.close();
    });
  });

The following example uses an OJAI query to return the _id and name fields from documents in a store and to sort the documents by _id.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderby specification as follows:
"$orderby": {"_id": "desc"}
from mapr.ojai.storage.ConnectionFactory import ConnectionFactory

# Create a connection to data access server
connection_str = "localhost:5678?auth=basic;user=mapr;password=mapr;" \
          "ssl=true;" \
          "sslCA=/opt/mapr/conf/ssl_truststore.pem;" \
          "sslTargetNameOverride=node1.mapr.com"
connection = ConnectionFactory.get_connection(connection_str=connection_str)

# Get a store and assign it as a DocumentStore object
store = connection.get_store('/demo_table')

# Create an OJAI query
query = {"$select": ["_id", "name"], "$orderby": {"_id": "asc"}}

# fetch OJAI Documents by query
query_result = store.find(query)

# Print OJAI Documents from document stream
for doc in query_result:
    print(doc)

# close the OJAI connection
connection.close()

The following example uses an OJAI query to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sort the documents by _id, offset the result by two documents, and limit the result to a single document.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the orderby method call as follows: 
order_by('_id', desc)
from mapr.ojai.storage.ConnectionFactory import ConnectionFactory

# Create a connection to data access server
connection_str = "localhost:5678?auth=basic;user=mapr;password=mapr;" \
          "ssl=true;" \
          "sslCA=/opt/mapr/conf/ssl_truststore.pem;" \
          "sslTargetNameOverride=node1.mapr.com"
connection = ConnectionFactory.get_connection(connection_str=connection_str)

# Get a store and assign it as a DocumentStore object
store = connection.get_store('/demo_table')

# Create an OJAI query
query = {"$offset": 2,
         "$select": ["_id",
                     "firstName",
                     "lastName",
                     "address.zipCode"],
         "$limit": 1,
         "$orderby": {"_id": "asc"}}

# options for find request
options = {
    'ojai.mapr.query.result-as-document': True
    }

# fetch OJAI Documents by query
query_result = store.find(query, options=options)

# Print OJAI Documents from document stream
for doc in query_result:
    print(doc.as_dictionary())

# close the OJAI connection
connection.close()

The following dbshell commands are equivalent to the code examples. See dbshell find or findbyid for more details about the syntax dbshell provides.

find /demo_table --query {
  "$select":["_id","firstName","lastName","address.zipCode"],
  "$orderby":"_id"
}

find /demo_table 
   --fields _id,firstName,lastName,address.zipCode
   --orderby _id

find /demo_table --query {
  "$select":["_id","firstName","lastName","address.zipCode"],
  "$orderby":"_id",
  "$offset":2,
  "$limit":1
}

find /demo_table 
   --fields _id,firstName,lastName,address.zipCode
   --orderby _id
   --offset 2
   --limit 1
NOTE
The commands are shown split across multiple lines for readability. When using dbshell, you must enter them in a single line.

The following example uses an OJAI query to return the _id and name fields from documents in a store and to sort the documents by _id.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the OrderBy specification as follows:
.OrderBy("_id", SortOrder.DESC)
using System;
using MapRDB.Driver;
using MapRDB.Driver.Ojai;

public class FindQueryWithOrderBy
{
	public async void FindQueryWithOrderBy()
	{
        // Create a connection to data access server
        var connectionStr = $"localhost:5678?auth=basic;" +
            $"user=mapr;" +
            $"password=mapr;" +
            $"ssl=true;" +
            $"sslCA=/opt/mapr/conf/ssl_truststore.pem;" +
            $"sslTargetNameOverride=node1.mapr.com";
        var connection = ConnectionFactory.CreateConnection(connectionStr);

        // Get a store and assign it as a DocumentStore object
        var store = connection.GetStore("/demo_table");

        // Create an OJAI query
        var query = connection
            .NewQuery()
                .Select("_id", "name")
                .OrderBy("_id", SortOrder.ASC)
            .Build();

        // Fetch OJAI Documents by query
        var queryResult = store.Find(query);

        var documentStream = await queryResult.GetDocumentAsyncStream().GetAllDocuments();
        // Print OJAI Documents from document stream
        foreach (var document in documentStream)
        {
            Console.WriteLine(document);
        }

        // Close the OJAI connection
        connection.Close();
    }
}

The following example uses an OJAI query to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sort the documents by _id, offset the result by two documents, and limit the result to a single document.

NOTE
The example sorts in the default ascending order. To sort in descending order, modify the OrderBy method call as follows: 
.OrderBy("_id", SortOrder.DESC)
using System;
using MapRDB.Driver;
using MapRDB.Driver.Ojai;

public class FindQueryWithOrderByLimitOffset
{
	public async void FindQueryWithOrderByLimitOffset()
	{
        // Create a connection to data access server
        var connectionStr = $"localhost:5678?auth=basic;" +
            $"user=mapr;" +
            $"password=mapr;" +
            $"ssl=true;" +
            $"sslCA=/opt/mapr/conf/ssl_truststore.pem;" +
            $"sslTargetNameOverride=node1.mapr.com";
        var connection = ConnectionFactory.CreateConnection(connectionStr);

        // Get a store and assign it as a DocumentStore object
        var store = connection.GetStore("/demo_table");

        // Create an OJAI query
        var query = connection
            .NewQuery()
                .Select("_id", "firstName", "lastName", "address.zipCode")
                .Offset(2)
                .Limit(1)
                .OrderBy("_id", SortOrder.ASC)
            .Build();

        // Fetch OJAI Documents by query
        var queryResult = store.Find(query);

        var documentStream = await queryResult.GetDocumentAsyncStream().GetAllDocuments();
        // Print OJAI Documents from document stream
        foreach (var document in documentStream)
        {
            Console.WriteLine(document.ToJsonString());
        }

        // Close the OJAI connection
        connection.Close();
    }
}

The following example uses an OJAI query to return the _id and firstName fields from documents in a store and to sort the documents by _id.

The example sorts in the default ascending order. To sort in descending order, modify the orderby specification as follows:
"$orderby": map[string]interface{}{"_id": "desc"}}
package main

import (
	"fmt"
	client "github.com/mapr/private-maprdb-go-client"
)

func main() {
	// Create connection string
	connectionString := "192.168.33.11:5678?" +
		"auth=basic;" +
		"user=mapr;" +
		"password=mapr;" +
		"ssl=true;" +
		"sslCA=/opt/mapr/conf/ssl_truststore.pem;" +
		"sslTargetNameOverride=node1.cluster.com"

	storeName := "/demo_table"

	// Create a connection to DAG
	connection, err := client.MakeConnection(connectionString)
	if err != nil {
		panic(err)
	}

	// Get a store and assign it as a DocumentStore struct
	store, err := connection.GetStore(storeName)
	if err != nil {
		panic(err)
	}

	// Options for find request
	options := &client.FindOptions{ResultAsDocument: true}

	// Create an OJAI query
	query := map[string]interface{}{"$select": []interface{}{"_id", "firstName"},
		"$orderby": map[string]interface{}{"_id": "asc"}}

	// Fetch all OJAI Documents from table
	findResult, err := store.FindQueryMap(query, options)

	// Print OJAI Documents from document stream
	for _, doc := range findResult.DocumentList() {
		fmt.Println(doc)
	}

	// Close connection
	connection.Close()
}

The following example uses an OJAI query to return the _id, firstName, lastName, and address.zipCode fields from documents in a store, sort the documents by _id, offset the result by two documents, and limit the result to a single document.

The example sorts in the default ascending order. To sort in descending order, modify the orderby function call as follows: 
"$orderby": map[string]interface{}{"_id": "desc"}}
package main

import (
	"fmt"
	client "github.com/mapr/private-maprdb-go-client"
)

func main() {
	// Create connection string
	connectionString := "192.168.33.11:5678?" +
		"auth=basic;" +
		"user=mapr;" +
		"password=mapr;" +
		"ssl=true;" +
		"sslCA=/opt/mapr/conf/ssl_truststore.pem;" +
		"sslTargetNameOverride=node1.cluster.com"

	storeName := "/demo_table"

	// Create a connection to DAG
	connection, err := client.MakeConnection(connectionString)
	if err != nil {
		panic(err)
	}

	// Get a store and assign it as a DocumentStore struct
	store, err := connection.GetStore(storeName)
	if err != nil {
		panic(err)
	}

	// Options for find request
	options := &client.FindOptions{ResultAsDocument: true}

	// Create an OJAI query
	query := map[string]interface{}{"$select": []interface{}{"_id", "firstName", "lastName", "address.zipCode"},
		"$offset":  2,
		"$limit":   1,
		"$orderby": map[string]interface{}{"_id": "asc"}}

	// Fetch all OJAI Documents from table
	findResult, err := store.FindQueryMap(query, options)

	// Print OJAI Documents from document stream
	for _, doc := range findResult.DocumentList() {
		fmt.Println(doc)
	}

	// Close connection
	connection.Close()
}