Child pages
  • Query your content
Skip to end of metadata
Go to start of metadata

No SQL queries

Apstrata relieves you from having to create a relational database, defining tables or manipulating DDL or SQL. You are provided with a ready to use store, within which you create documents. Documents have a key/value pairs structure, where the key is a field name and the value can have one of the following types: string, numeric, text, date, file or geospatial.
To retrieve the data that you have persisted in your documents, you can either:
  • Retrieve the whole document using the document key (unique identifier of a document), by invoking Query API
  • Define sophisticated queries to search for document matching your criteria, using the Query API as well

Back to the map       Next station: Apply validation

Example 1: load my saved game

So you are implementing a cool game that runs on mobile devices, such as smartphones or tablets. Since game data has to be accessible from any device owned by the user, you decided to persist it as an Apstrata document. What you need now is to load the game data back to the device, i.e. get the corresponding document using its document key (we assume our document has the following key 1EB1FFB956C9D0D7155C7422749F485A).

curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1417700771054
&apsws.authSig=bc7f28a7061ba5b1916f4efa7741d7ac&apsws.responseType=json
&apsws.authMode=simple&apsdb.store=cabstore
&apsdb.query=apsdb.documentKey"%"20"%"3D"%"20"%"1EB1FFB956C9D0D7155C7422749F485A"%"22
&apsdb.queryFields=score"%"2C"%"20level"%"2C"%"20lives"%"2C"%"20player"
 var params = {
			    
	"apdsb.store": "DefaultStore", // "DefaultStore" is the name by default. If you did not rename your store, this line is optional
	"apsdb.query": "apsdb.documentKey=\"1EB1FFB956C9D0D7155C7422749F485A\"", // Notice that we use the "apsdb.query" field to define our query
	"apsdb.fields": "player, score, level, lives" // We use "apsdb.fields" to specify the document fields to return
}
			
// We invoke the "Query" API through the native "apsdb" object. 
// Since we are not uploading any file, the third parameter is null
return apsdb.callApi("Query", params, null);
String response = "no response yet";
try {
					
	String authKey = "O763A7F690"; // Replace with your Application key
    String baseURL = "https://sandbox.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.store","DefaultStore")); // "DefaultStore" is the name by default. If you did not rename your store, this line is optional
	parameters.add(new BasicNameValuePair("apsdb.query","apsdb.documentKey = \"1EB1FFB956C9D0D7155C7422749F485A\"")); // Notice that we use the "apsdb.query" field to define our query
	parameters.add(new BasicNameValuePair("apsdb.queryFields","score, level, lives, player")); We use "apsdb.fields" to specify the document fields to return
										
	// 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, files, mode);					
} catch (Exception e) {
	response = "Error: " + e.getMessage();
	e.printStackTrace();
}
Try it!

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

Imagine you need to add a "hall of fame" section in your application, where the player can see the 10 best scores, the corresponding level and the name of the player. All you have to do is to create a query which returns no more than 10 results and such as "score > 0". You also should specify the sort order (descending). Let us see how easy this is to implement with the Apstrata Query API:

curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1417704302580&apsws.authSig=09e9c225c42a6893fed9da9f5168f585
&apsws.responseType=json&apsws.authMode=simple&apsdb.store=cabstore
&apsdb.query=score"%"3Cnumeric"%"3E"%"20"%"3E"%"200
&apsdb.queryFields=score"%"2C"%"20level"%"2C"%"20player
&apsdb.sort=score"%"3Cnumeric"%"3ADESC"%"3E
&apsdb.resultsPerPage=10"
var params = {
		        
	"apsdb.store": "DefaultStore",
	"apsdb.query": "score<numeric> > 0", // Notice that we specify the type of the score field in order to use the comparison operator
	"apsdb.queryFields": "score, level, player", // We ask to return the score, level and player fields
	"apsdb.sort": "score<numeric:DESC>", // We specify that the sort order is DESC -  descending (use ASC for ascending) on the score field
	"apsdb.resultsPerPage":"10" // We specify to return no more than 10 results per request
}
		    
return apsdb.callApi("Query", params, null);
String response = "no response yet";
try {
					
	String authKey = "O763A7F690"; // Replace with your Application key
    String baseURL = "https://sandbox.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.store","DefaultStore"));
	parameters.add(new BasicNameValuePair("apsdb.query","score<numeric> > 0")); // Notice that we specify the type of the score field in order to use the comparison operator
	parameters.add(new BasicNameValuePair("apsdb.queryFields","score, level, player")); // We ask to return the score, level and player fields
	parameters.add(new BasicNameValuePair("apsdb.sort","score<numeric:DESC>")); // We specify that the sort order is DESC -  descending (use ASC for ascending)
	parameters.add(new BasicNameValuePair("apsdb.resultsPerPage","10")); // We specify to return no more than 10 results per request
											
	// 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, files, mode);
} catch (Exception e) {
	response = "Error: " + e.getMessage();
	e.printStackTrace();
}
Try it!

Example 3: get highest scores grouped by level

Now let us say that you would like to know what are the highest scores per game level. What you need is to group the results of your query by the "level" field. This is easily done using the "apsdb.aggregateGroupBy" parameter of the Query API.

 

curl "https://varick.apstrata.com/apsdb/rest/O763A7F690/Query?apsws.time=1417707644199&apsws.authSig=bfe7bf3814e8e6908e9369967bcf48d0
&apsws.responseType=json&apsws.authMode=simple
&apsdb.store=DefautStore
&apsdb.query=score"%"3Cnumeric"%"3E"%"20"%"3E"%"200
&apsdb.queryFields=score"%"2C"%"20level"%"2C"%"20player
&apsdb.sort=score"%"3Cnumeric"%"3ADESC"%"3E
&apsdb.aggregateExpression=Max("%"24score)
&apsdb.aggregateGlobal=true
&apsdb.aggregateGroupBy=level"%"3Cnumeric"%"3E"
 var params = {
		        
	"apsdb.store": "DefaultStore",
	"apsdb.query": "score<numeric> > 0", // Notice that we specify the type of the score field in order to use the comparison operator
	"apsdb.queryFields": "score, level, player", // We ask to return the score, level and player fields
	"apsdb.aggregateExpression": "Max($score)", // Aggregate by max score value
	"apsdb.aggregateGlobal":"true", // Aggregation concerns all documents in the store that match the query
	"apsdb.aggregateGroupBy": "level<numeric>", // We specify the aggregation statement and field(s) to use for grouping
	"apsdb.sort": "score<numeric:DESC>", // Still sorting by score descending
}
		    
return apsdb.callApi("Query", params, null);
String response = "no response yet";
try {
					
	String authKey = "O763A7F690"; // Replace with your Application key
    String baseURL = "https://sandbox.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.COMPLEX;
                  
    // Prepare the parameters to send to the SaveDocument API                                  
    List<NameValuePair> parameters = new ArrayList<NameValuePair>();		
 	parameters.add(new BasicNameValuePair("apsdb.store","DefaultStore"));
	parameters.add(new BasicNameValuePair("apsdb.query","score<numeric> > 0"));
	parameters.add(new BasicNameValuePair("apsdb.queryFields","score, level, player")); // We ask to return the score, level and player fields
	parameters.add(new BasicNameValuePair("apsdb.sort","score<numeric:DESC>")); // Still sorting by score descending
	parameters.add(new BasicNameValuePair("apsdb.aggregateExpression","Max($score)")); // Aggregate by max value of the score
	parameters.add(new BasicNameValuePair("apsdb.aggregateGlobal","true")); // Aggregation is applied to all documents that match the query
	parameters.add(new BasicNameValuePair("apsdb.aggregateGroupBy","level<numeric>")); // We specify the aggregation statement and field(s) to use for grouping
	
	// 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, files, mode);
} catch (Exception e) {
	response = "Error: " + e.getMessage();
	e.printStackTrace();
}				
Try it!

Dig deeper

Related tutorials