Following on from Part 3b in this series, a few query options are exemplified.

Ardjson: https://ardjson.codeplex.com
This will include the batch files for this blog as well

Previous: Ardjson-Part 3b: cURL CRUD Examples

Correction: There was an error in the MiniJson program. It counted one too many records. this will be fixed.

NOTE: The Microsoft Azure Mobile Services API is documented at: Azure Mobile Services REST API Reference

 

HTTP verbs  that are accepted by Microsoft Azure Mobile Services are GET, POST, PATCH and DELETE.

All of the examples that follow are implemented in batch files for the ToDoItems Mobile Service table and involve HTTP GET. Only the curl line is shown, from which an HTTP GET call via other APIs can be gleaned. The batch files (named in blue below) are in the file 1.2.1 Curl, JSON and REST.zip on the Downloads tab of the Ardjson site on Codeplex.
A typical complete batch file is:

cls
@echo off
@echo *
@echo * Microsoft Azure Mobile Service (ToDo) Exerciser

call env.bat

@echo * Setting Mobil eService Table

set MSTable=toDoItem
@echo ON

@echo * 
@echo * Table = %MSTable%
@echo *
@echo *

@echo * [0] GET (Select/Read) all reords in table
@echo * > GET /tables/%MSTable%
@echo *
curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%"

env.bat is covered in blog Part-3b..

Many of the batch files actually include some formatting of the JSon string, placing one record per line, using MiniJson.exe

 

Paging

Getting a specified number of records can be implemented using $top=<n>. Also $skip=<n> can be used to skip a certain number of records.  These two combined together can implement paging.

I have found that I can’t get skip to work. <Watch this space for an update or if you can solve it please leave a comment, thx>

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$top=10"

$top Batch file toDoGetAllTop10.bat

 

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$orderby=id&$skip=4"

$skip Batch file toDoGetAllSkip4.bat

 

ORDERING

$orderby is used to specify the column to sort by.  It defaults to ascending.  There are asc (ascending) and desc (descending) options.

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$orderby=id"

$orderby Batch file toDoGetAllOrderbyId.bat

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$orderby=id+asc"

asc Batch file toDoGetAllOrderbyIdAsc.bat

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$orderby=id+desc"

desc Batch file toDoGetAllOrderbyIdDesc.bat

 

SELECTIONS

This involves $select followed by a comma separate list of columns:

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$select=text,complete"

$select to show only the text and complete fields. Batch file toDoGetAllSelectCols.bat


System Properties

Each record submitted has some hidden fields that are not by default returned with an HTTP GET. Counting records

Whilst the MiniJson app can count records in a Json string, simply by counting the number of closing braces in the string, $inlinecount can be used to generate a count at the returned JSon string. It requires  the allpages setting.

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$inlinecount=allpages"

$inlinecount Batch file toDoGetAllSystemProperties.bat

__systemproperties can be set to a comma delimited list of the system properties to be included in the response.
System properties from the following list are accepted:

  • __createdAt: system created to track the creation of the record.
  • __updatedAt: sssystem created to track the last update to the record.
  • __version: version used to support concurrency.
  • __deleted: system created to support soft delete.

Count Records

Whilst the MiniJson app can count records in a Json string, simply by counting the number of closing braces in the string, $inlinecount can be used to generate a count at the returned JSon string. It requires  the allpages setting.

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$inlinecount=allpages"

$inlinecount Batch file toDoGetCount.bat

Note it returns the records with the count at the end. MiniJson has been extend to extract the count from a $inlinecount query:

curl -v -X GET -H Content-Type:application/json -H X-ZUMO-APPLICATION:%AppKey%  "%AzureMobileServiceURL%/tables/%MSTable%?$inlinecount=allpages"  > json.txt
MiniJson json.txt count

Batch file toDoGetCount.bat

The first parameter of MiniJson is the always the Json string stored as a file. If there is a second parameter count, it extracts the count on its own, from the string, rather than the heuristic approach of counting closing braces.