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.

Java - Order By

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 ===");
  }

}

Java - Order By with Offset and Limit

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 ===");
  }

}

Node.js - Order By

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();
    });
  });

Node.js - Order By with Offset and Limit

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();
    });
  });

Python - Order By

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()

Python - Order By with Offset and Limit

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()

dbshell

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.

C# - Order By

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();
    }
}

C# - Order By with Offset and Limit

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();
    }
}

Go - Order By

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()
}

Go - Order By with Offset and Limit

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()
}
Parent topic: Examples: Querying JSON Documents