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