No SQL query components

Apstrata developers can persist their data inside NoSQL key/value pair structures called documents, which are saved into their application store. To retrieve the data that you have persisted in your documents, you can define sophisticated queries to search for document matching your criteria, using the Query API.
While you can invoke the Query API from your client side and pass it all the required parameters to execute your query, you will most of the time need to reuse this same logic across clients or event across server-side components. Since Apstrata always has modularity and reusability in mind, it allows you to encapsulate your query logic into components, called "Saved Queries" using the SaveQuery API
Back to the map       Next station: Geospatial queries

Example 1: load my saved game

You are developing this cool game that runs on mobile devices. Although your game is addictive, your players will stop playing from time to time, therefore, you decide to persist the games as Apstrata documents. When your players get back to the game, you need to load its data back to the device, i.e. get the corresponding document using its document key (in our example, we assume our document has the following key F0BBBBEC7EE9EF59EAC220599BADFD08). 

The simplest way for you to create a "Saved Query" is to log in to the Apstrata workbench, click on "Manage App > Saved Queries > New". This opens a query editor within which a predefined template is already loaded. 

<query>
	<executeACL>creator</executeACL> <!-- This query only authorizes the retrieval of a document created by the caller of the query -->
	<store>DefaultStore</store> <!-- Put the name of your store here. "DefaultStore" is the default value -->
	<condition><![CDATA[ apsdb.documentKey<string> = {docKey}]]></condition> <!-- {docKey} is a mandatory parameter to pass to the query -->
	<returnedFields> <!-- The list of document fields we want to return -->
		<field>player</field>
		<field>level</field>
		<field>lives</field>
		<field>score</field>
	</returnedFields>
</query>

Try it!

From the "Saved Queries" editor, click "Save" to save your query then "Run". The workbench opens a form where you have to enter the "docKey" field with a value that matches one of your game document keys. Once done, click "Go" to execute the saved query.

Invoke the saved query

curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1419244026775
&apsws.authSig=00826da6btdd6514a14986s366d681cf
&apsws.responseType=json&apsws.authMode=simple
&apsdb.queryName=loadGame&docKey=F0BBBBEC7EE9EF59EAC220599BADFD08"
// We retrieve the value of the docKey parameter expected by the saved query, from the request
var docKey = request.parameters["docKey"];
var params = {
		        
	"apsdb.queryName": "loadGame", // we pass the name of the saved query
	"docKey": docKey
}

return apsdb.callApi("Query", params, null);		
try {
					
	String authKey = "O763A7F690"; // Replace with your Application key
    String baseURL = "https://varick.apstrata.com/apsdb/rest";
                   
    // Sign your call with a signature based on your application key and secret  to authenticate against Apstrata
    // Simple signature is used in this example
    String secret = "S4A0120A6E64952FE75285BD0XD243S2"; // Replace with your application secret
    Connection ownerConnection = new OwnerConnection(baseUrl, authKey, secret);
    Client client = new Client(baseURL, authKey, ownerConnection);
    Client.AuthMode mode = Client.AuthMode.SIMPLE;
                   
    // Prepare the parameters to send to the SaveDocument API                                 
    List<NameValuePair> parameters = new ArrayList<NameValuePair>();
	 
	// Prepare the parameters to send according to the requested Apstrata API									
	List<NameValuePair> parameters = new ArrayList<NameValuePair>();
	parameters.add(new BasicNameValuePair("apsdb.queryName","loadGame"));
	parameters.add(new BasicNameValuePair("docKey","F0BBBBEC7EE9EF59EAC220599BADFD08"));												
	// Invoke the API using the Client instance and signature mode 
	// note that since we're not passing files, we send "null" in place of the "files" parameter
	response = client.callAPIJson(Query, parameters, null, mode);
} catch (Exception e) {
	response = "Error: " + e.getMessage();
    e.printStackTrace();
} 

Example 2: get top 10 highest scores, in descending order, grouped by level

In your game, you would like to display the 10 best scores of all times, as well as the corresponding level player name. Using Apstrata, you can simply create a query that returns no more than 10 results and such as "score > 0", also specifying that the results should be sorted in descending order.

Once again, let us use the Apstrata workbench to create a saved query:

 <query>
	<executeACL>creator</executeACL><!-- This query only authorizes the retrieval of a document created by the caller of the query -->
	<store>DefaultStore</store>
	<condition><![CDATA[ score<numeric> > 0]]></condition><!-- we search for documents that have a strictly positive score -->
	<returnedFields> <!-- list of fields to return -->
	    <field>player</field>
		<field>level</field>
		<field>lives</field>
		<field>score</field>
	</returnedFields>
    <aggregate> 
        <expression>Max($score)</expression> <!-- we query by Max score -->
		<global>true</global> <!-- the aggregation is applied to all the documents in the store that match the condition -->
        <groupBy> <!-- group results by level -->
            <field>
                <name>level</name>
                <type>numeric</type>
            </field>
        </groupBy>
    </aggregate>
    <resultsPerPage>10</resultsPerPage> <!-- only return 10 results per page. since we do not mention a page number, we will get the top 10 scores -->
</query>

Invoke the saved query

curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1419263545113
&apsws.authSig=00826da6btdd6514a14986s366d681cf
&apsws.responseType=json
&apsws.authMode=simple
&apsdb.queryName=topScores"
// We retrieve the value of the docKey parameter expected by the saved query, from the request
var docKey = request.parameters["docKey"];
var params = {
	"apsdb.queryName": "topScores" // we pass the name of the saved query
}

return apsdb.callApi("Query", params, null);		
try {
					
	String authKey = "O763A7F690"; // Replace with your Application key
    String baseURL = "https://varick.apstrata.com/apsdb/rest";
                   
    // Sign your call with a signature based on your application key and secret  to authenticate against Apstrata
    // Simple signature is used in this example
    String secret = "S4A0120A6E64952FE75285BD0XD243S2"; // Replace with your application secret
    Connection ownerConnection = new OwnerConnection(baseUrl, authKey, secret);
    Client client = new Client(baseURL, authKey, ownerConnection);
    Client.AuthMode mode = Client.AuthMode.SIMPLE;
                   
    // Prepare the parameters to send to the SaveDocument API                                 
    List<NameValuePair> parameters = new ArrayList<NameValuePair>();               
    parameters.add(new BasicNameValuePair("apsdb.queryName","topScores"));
						
	// Invoke the API using the Client instance and signature mode 
	// note that since we're not passing files, we send "null" in place of the "files" parameter
	response = client.callAPIJson(Query, parameters, null, mode);
} catch (Exception e) {
	response = "Error: " + e.getMessage();
    e.printStackTrace();
} 

Dig deeper

Related tutorials