A query is used to return document data from the specified store according to the query condition provided. The query request can be called with one or more of the following parameters:

• The query condition

• The list of fields to be returned

• The list of fields to be used to sort the results

• The count parameter that controls if the total number of available results should be returned

• A search string to be used to perform a full text search on all the documents that are indexed

• The number of results per page and the page number to be returned

• An aggregate function to perform on the query results

• The forceCurrentSnapshot parameter that controls whether the query should be executed on a read-only snapshot or the master snapshot

• The runAs parameter that allows the account owner to execute the query with the same permission as an account user

• The queryName parameter that gives to the user the possibility to execute a previously saved query

The user should write his query using the following predicate format composed of three parts: leftOperand comparisonOperator rightOperand

Examples:
name<string> = “James”
name<string> = lastName<string>

leftOperand

The leftOperand is composed of one mandatory part and one optional part: fieldName<fieldType>
The mandatory fieldName is the name of the field. Note that some special fields (metadata) having the “apsdb.” prefix are always available for the users such as apsdb.documentKey, apsdb.versionNumber, apsdb.latestVersion, apsdb.creator, apsdb.creationTime, apsdb.lastModifiedBy, and apsdb.lastModifiedTime.

Special mention goes to the following versioning metadata fields:

• The "apsdb.versionNumber" metadata field specifies which version of the document to retrieve. If not specified, all versions of the document are returned. Example: (apsdb.documentKey = "myDoc") AND (apsdb.versionNumber = 1) --- returns the 1st version of the document keyed by "myDoc".
• The "apsdb.latestVersion" field can be used to return only the latest version of documents. It takes 0 or 1 as possible options, where 1 indicates a match for the latest version. For example, the condition ((apsdb.documentKey = "myDoc") AND (apsdb.latestVersion=1)) returns the latest version of the document keyed by "myDoc".

The optional fieldType is the type of the field; it could be numeric, string, geospatial or date. We don’t support long text type. Note that if the user doesn't specify the type it will be string by default.
The format to use for the fields of type 'date' is either yyyy-MM-dd or yyyy-MM-dd'T'HH:mm:ssZ.

comparisonOperator

The comparisonOperator can be =, !=, >, >=, <, <=, in, in-all or like.
Note that if the comparisonOperator is like, then the rightOperand should be a string and cannot start with the wildcard %.

Special mention should be made when dealing with geospatial fields.

Geospatial fields do not support the comparison operators listed above. Instead, when dealing with geospatial fields, the "within" operator should be used. It allows queries to search for geospatial locations that lie within a specified distance from a reference point. Consequently, this operator requires 2 parameters: a geospatial reference point (represented as a pair of latitude/longitude decimal degrees), and a distance value (in kilometers). As an example, the following query condition can be used to find all locations that are 200m away from the Eiffel Tower: location<geospatial> within (48.8580, 2.2951, 0.200).

rightOperand

The rightOperand is the value of the field. If the type of the field is numeric then quotes (”) should be omitted, otherwise they are required. A more complex query will include logical operators (AND, OR) and parentheses to set the precedence. Note that in the absence of parentheses: The two logical operators “AND” and “OR” have the same precedence and the query will be executed by reading the conditions in order from left to right. The following is an example of a complex query syntax, you will notice that we have omitted quotes (“) to specify the fieldType for the string field “name” as it will be read as a string by default:

(name = "James" AND birthday<date> = "1982-05-30") OR (name = "John" AND age<numeric> = 26)

The user can specify which fields should be returned after executing the query by sending the comma-separated list of field names in the parameter "queryFields". He may specify document fields (fieldName1,fieldName2,fieldName3), metadata fields (apsdb.documentKey, apsdb.versionNumber, apsdb.creator, …), derived fields (distance (gpsLocation1, gpsLocation2)) or the symbol “*” in order to request all the fields of the documents returned.
Note that if the developer requests all fields (*), the available metadata fields will also be returned.

The document key and version will always be returned even no fields were specified.
For file fields, only file names are returned. In order to get the files themselves, use the GetFile API.
All date fields will be returned with the default format yyyy-MM-dd'T'HH:mm:ssZ.

If no fields were specified and the apsdb.count is true then only the count is returned and the query is not executed. If no fields were specified and the apsdb.count is false then the INVALID_QUERY_REQUEST exception is returned.

Derived Fields

In addition to returning fields,

Request parameters:
 
apsdb.query=country<string>="France" and location<geospatial> within (48.8580, 2.2951, 200)
apsdb.includeFieldType=true
apsdb.queryFields=country,apsdb.documentKey,distance(location,'48.8580', '2.2951')
apsdb.sort=distance(location,48.8580,2.2951)<ASC>

JSON Response:
 
{"response": {
  "metadata": {
    "requestId": "21961fdf-8cd6-4c39-97f0-26a3095c0eb4",
    "status": "success",
    "statusCode": "200"
  },
  "result": {
    "count": "2",
    "documents": [
      {
        "key": "8D954C3442033A59F8E934B85794A221",
        "versionNumber": "1.0",
        "country": "France",
        "apsdb.documentKey": "testDocu",
        "location": "47.0,4.0",
        "_type": {
          "country": "string",
          "apsdb.documentKey": "string",
          "location": "geospatial"
        },
        "_derivedFields": {
          "distance(location,'48.8580','2.2951')": "242.5116"
        }
      },
      {
        "key": "716A3A6467A967835CED2C4CB1B4C9CD",
        "versionNumber": "1.0",
        "country": "France",
        "apsdb.documentKey": "testDocuFar",
        "location": "68.0,14.0",
        "_type": {
          "country": "string",
          "apsdb.documentKey": "string",
          "location": "geospatial"
        },
        "_derivedFields": {
          "distance(location,'48.8580','2.2951')": "2225.9267"
        }
      }
    ]
  }
}}

The user can choose to sort the results of the query by sending the sorting condition in the request. The apstrata database sort format is composed of three parts: fieldname<fieldType:sortingOrder>
Example: name<string:ASC>

fieldName

The fieldName is the name of the field which could be also one of the metadata fields.

fieldType

The fieldType is the type of the field; it could be numeric, string or date. Note that if the user doesn't specify the type it will be “string” by default.

sortingOrder

The sortingOrder is the order in which the results should be sorted; It could be ASC (ascending) or DESC (descending).

Multiple fields can be used for sorting by separating every sorting condition by a comma. In the example below, the results of the query will be sorted by name first and for similar names they will be sorted by date.
Example: “name<string:ASC>”, “birthday<date:DESC>”
Note that when sorting the records on a multi-value field, the same document will be returned n times, where n is the number of values it has of the sorted field.

Sorting on fields of type geospatial is however a bit different. We offer the ability to sort the documents returned by the query based on how far their geospatial points are from a reference point by using the distance function.

distance(geospatialFieldName, 48.8580, 2.2951 )<ASC>

In the example above, the distance of all the fields geospatialFieldName to the reference point Eiffel Tower (48.8580°, 2.2951°) will be calculated. The documents will be returned in the ascending order of distances from the specified geospatial field to the reference field.

Full Text Search Syntax

The user can choose to do a full text search on all the documents that are full text search indexed by specifying the pattern to match in apsdb.ftsQuery. It is worth noting that querying fields in this manner is more like a keyword search or a filter than a relevancy search.

The files that are indexed for the full text search have the following extensions: txt, htm, html, shtm, shtml, jsp, asp, aspx, cfm, cfml, xml, pdf, doc, docx, xls, xlsx, ppt, pps, mht

There are two types of terms that can be used for the search: Single Terms and Phrases.

• A Single Term is a single word that doesn't need to be surrounded by double quotes. Note that the Single Term can contain any wildcards.

• A Phrase is a group of words surrounded by double quotes such as "hello world"

The final output of the query operation will be the intersection between the full text search (if any) and the query results.

Note that when using the apsdb.ftsQuery parameter, the apsdb.sort and apsdb.aggregateExpression parameters will be ignored.

The count is the number of records that match the query. The apsdb.count sent in the request is optional. If it is not sent, it is considered as false by default. It specifies if the count should be returned or not in the response.

If the user chooses to get the count but not run the query, he can set apsdb.count to true and not specify any fields in apsdb.queryFields. The user also has the option of getting both the count and the results of the query by sending both apsdb.count as true and some fields in apsdb.queryFields.

Special Cases:

• When requested at the same time as sorting a multi-value field, the count returned will be equal to the number of records returned and not to the number of documents returned, as the same document will be returned multiple times based on how many values it has of the sorted field.
• When requested at the same time as doing a full text search (apsdb.ftsQuery), the count will be equal to the number of documents returned in the first page instead of the total number of hits.
• When requested with fields to be returned (apsdb.queryFields) and without a page number (apsdb.pageNumber) or results per page(apsdb.resultsPerPage), the count will be equal to the number of rows returned which could be 500 at most.

The user can execute an aggregate function on the results returned by his query request. To do so he needs to add the “apsdb.aggregateExpression” attribute to the request.

Example: apsdb.aggregateExpression = function_name(${column_name})

• The function_name is the name of the aggregate function (MIN, MAX, SUM, AVG, or COUNT)

• The column_name is the name of the field on which the aggregate function should be executed. The column_name should always be preceded by the $ sign, to specify that it is a field, and optionally by the {} when needed. Note that aggregates can only be used with fields of type numeric.

Example: apsdb.aggregateExpression = avg($age-1)

Here $age-1 could be the name of the field or it could mean that we want to subtract 1.

The user can send two other parameters used to define the scope in which the aggregate should be executed. Aggregate totals can be performed either on all the documents, or only on the documents that are displayed on the current page, or on both. The scope will be defined by sending two parameters called apsdb.aggregatePage and apsdb.aggregateGlobal. They could be true or false depending on the scope that the user needs. By default, apsdb.aggregatePage is true while apsdb.aggregateGlobal is false. The apsdb.aggregatePage and apsdb.aggregateGlobal cannot be both set to false; an error will be thrown to warn the user.

Note that the developer can request the aggregates even if he didn’t request any fields to be returned.

The user can also execute a GroupBy statement when aggregating. GroupBy is used in conjunction with the aggregate functions to group the result by one or more columns. To use the GroupBy option, the user should send the parameter:

 apsdb.aggregateGroupBy

The parameter should contain a comma separated list of the columns the user wishes to “groupby” along with each column type. The type is mandatory for each column. An error is thrown if the type is missing. The types (not case sensitive) are: numeric, string, and date. Example:

 apsdb.aggregateGroupBy = field1<numeric>, field2<string>;

Aggregates with multi-multivalue fields give results that are not defined, as in, we don't know what an aggregate operation on a multivalue field means. Consider this example: multivaluefield + singlevaluefield where multivalue has values 1, 2 and 3 and singlevalue field has value 4. Would the result be 1+4 + 2+4 + 3+4 OR 1 + 2 + 3 + 4?

The user can choose to execute a saved query instead of building one on the fly. To do so, he needs to send with the query request the name of the query he wants to be executed. Along with the query name, the user is allowed to send only the following parameters:

• The number of the page that should be returned after executing the query (apsdb.pageNumber).
• The name of the store on which he wishes to execute the query (apsdb.store). Note that if the name of the store was previously saved in the query then it cannot be changed when executing it. If the user tries to send a different store name the system will return an error to inform him that he doesn’t have the permission to execute this query on another store.
• The list of parameters that are used in the condition of the saved query.

Querying to Lock Documents

Documents can be locked  to control consistency across concurrent updates. Locking a batch of documents with a Query call can only be done  with the following conditions :

• The apsdb.lock parameter must be set to true.
• The query condition must have the apsdb.documentKey field only.
• All fields must be returned  by setting apsdb.queryFields  to a * .
• The query count must be suppressed by not sending the apsdb.count parameter.
• There should be no sorting on any field.
• No aggregates must be executed

Fields will not appear in the query result if the user does not have “read” permission on them.

(Refer to Common Request Parameters)

(Refer to Common Response Elements)

The following specific "result" element is a child of the common root element "response" and a sibling of the common "metadata" element:

{
	"result": {
		"count": "an integer representing the total count of matching documents",
		"aggregate": {
			"pageScope": {
				"groupby": [
					{
						"value": "the value of the requested aggregate expression for the requested page",
						"fields": [
							{
								"name": "name of a field being grouped by",
								"value": "value of a field being grouped by"
							},
							{
								"name": "name of another field being grouped by",
								"value": "value of another field being grouped by"
							}
						]
					}
				]
			},
			"globalScope": {
				"groupby": [
					{
						"value": "the value of the requested aggregate expression across all pages",
						"fields": [
							{
								"name": "name of a field being grouped by",
								"value": "value of a field being grouped by"
							},
							{
								"name": "name of another field being grouped by",
								"value": "value of another field being grouped by"
							}

						]
					}
				]
			}
		},
		"documents": [			{
				"key": "key of a matching document; this is always returned even if not asked for",
				"versionNumber": "version number of a matching document; this is always returned even if not asked for",
				"fieldName1": "value of a requested field for a matching document",                
				"fieldName2": "value of another requested field for a matching document",
               	"_type": {
                	"fieldName1": "the type of the requested field fieldName1",
                    "fieldName2": "the type of the requested field fieldName2"
                },
                "_derivedFields": {                          
 					"distance(geospatialFieldName,'latitude','longitude ')": "distance calculated from the point geospatialFieldName to the reference point defined by the latitude/longitude pair."
                }
			},
			{
				"key": "key of another matching document; this is always returned even if not asked for",
				"versionNumber": "version number of another matching document; this is always returned even if not asked for",
				"fieldName1": "value of a requested field for another matching document",                
				"fieldName2": "value of another requested field for another matching document",
				"_type": {
                	"fieldName1": "the type of the requested field fieldName1",
                    "fieldName2": "the type of the requested field fieldName2"
     	     	},
      			"_derivedFields": {                          
 					"distance(geospatialFieldName,'latitude','longitude ')": "distance calculated from the point geospatialFieldName to the reference point defined by the latitude/longitude pair."
		        }
			}
		]
	}
}

Sample Request

Request URL: http://sandbox.apstrata.com/apsdb/rest/ [authenticationkey]/Query?apsws.time=[timestamp]&apsws.authSig=[signature]

POST parameters:

apsdb.store=ClevelandMain 
apsdb.query=age>20 
apsdb.includeFieldType=true 
apsdb.queryFields= lastName,firstName,country 
apsdb.sort=country<String:ASC> 
apsdb.resultsPerPage=10 
apsdb.pageNumber=1 
apsdb.ftsQuery="test" 
apsdb.count=true 
apsdb.forceCurrentSnapshot=false 
apsdb.aggregateGlobal=true 
apsdb.aggregatePage=true 
apsdb.aggregate.expression=MAX($age) 
apsdb.aggregateGroupBy =country<string>

Sample XML Response

Success XML:

< response xmlns="http://www.apstrata.com/services/schemas/apstrata_database_response.xsd" >
    < metadata >
        < requestId >xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx< /requestId >
        < status >success< /status >
    < /metadata >
    < result >
        < count >2< /count >
        < aggregate >
          < pageScope >
            < groupby >
              < group >
                < value >89< /value >
                < field name="country" value="US"/ >
              < /group >
              < group >
                < value >81< /value >
                < field name="country" value="France"/ >
              < /group >
            < /groupby >
          < /pageScope >
          < globalScope >
            < groupby >
              < group >
                < value >90< /value >
                < field name="country" value="US"/ >
              < /group >
              < group >
                < value >88< /value >
                < field name="country" value="France"/ >
              < /group >
            < /groupby >
          < /globalScope >
        < /aggregate >
        < documents >
            < document key="docKey1" versionNumber="1.0">
                < deniedFields >
                   < deniedField name="lastName" type="string" >
                         < values / >
                   < /deniedField >
                   < /deniedFields >
              < fields >
                      < field name="firstName" type="string" >
                          < values >
                              < value >John< /value >
                          < /values >
                      < /field >
                      < field name="country" type="string" >
                          < values >
                              < value >US< /value >
                          < /values >
                      < /field >
                    < /fields >
            < /document >
            < document key="docKey2" versionNumber="1.0">
                < deniedFields >
                   < deniedField name="lastName" type="string" >
                         < values / >
                   < /deniedField >
                 < /deniedFields >
                    < fields >
                   < field name="firstName" type="string" >
                        < values >
                              < value >John< /value >
                            < /values >
                   < /field >
                   < field name="country" type="string" >
                        < values >
                              < value >France< /value >
                        < /values >
                   < /field >
                 < /fields >
           < /document >
        < /documents >
< /result >
< /response >

Failure XML:

< response xmlns="http://www.apstrata.com/services/schemas/apstrata_database_response.xsd" >
    < metadata >
        < requestId >xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx< /requestId >
        < status >failure< /status >
        < errorCode >[errorCode]< /errorCode >
        < errorDetail >[failMsg]< /errorDetail >
    < /metadata >
< /response >

Sample JSON Response

{"response": {
  "metadata": {
    "requestId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "status": "success"
  },
  "result": {
    "aggregate": {
      "pageScope": {
        "groupby": {
          "group": {
            "value": "89.0",
            "field": {
              "name": "country",
              "value": "US"
            }
          },
          "group": {
            "value": "81.0",
            "field": {
              "name": "country",
              "value": "France"
            }
          }
        }
      },
      "globalScope": {
        "groupby": {
          "group": {
            "value": "90.0",
            "field": {
              "name": "country",
              "value": "US"
            }
          },
          "group": {
            "value": "88.0",
            "field": {
              "name": "country",
              "value": "France"
            }
          }
        }
      }
    },
    "documents": [
      {
        "key": "docKey1",
        "versionNumber": "1.0",
        "lastName": "",
        "firstName": "John",
        "country": "US",
        "_type": {
          "firstName": "string",
          "lastName": "string",
          "country": "string"
        }
      },
      {
        "key": "docKey2",
        "versionNumber": "1.0",
        "lastName": "",
        "firstName": "John",
        "country": "France",
        "_type": {
          "firstName": "string",
          "lastName": "string",
          "country": "string"
        }
      }
    ]
  }

Sample Request

Request URL: http://sandbox.apstrata.com/apsdb/rest/[authenticationkey]/Query?apsws.time=[timestamp]&apsws.authSig=[signature] 

POST parameters:

apsdb.store= testStore
apsdb.queryName= selectNewEmployees
apsdb.pageNumber=2
startdate="05/30/09"

Sample XML Response

Success XML:

< response xmlns="http://www.apstrata.com/services/schemas/apstrata_database_response.xsd" >
    < metadata >
        < requestId >xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx< /requestId >
        < status >success< /status >
    < /metadata >
    < result >
        < count >11< /count >
          < documents >
            < document key="7A337626BDC1B924241F5E110CD58EA9" versionNumber="1.0">
                < fields >
                    < field >
                        < values >
                            < value >John< /value >
                        < /values >
                    < /field >
              < field name="lastName" >
                        < values >
                            < value >Smith< /value >
                        < /values >
              < /field >
          < /fields >
      < /document >
   < /documents >
< /result >
< /response >

Failure XML:

< response xmlns="http://www.apstrata.com/services/schemas/apstrata_database_response.xsd" >
    < metadata >
        < requestId >xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx< /requestId >
        < status >failure< /status >
        < errorCode >[errorCode]< /errorCode >
        < errorDetail >[failMsg]< /errorDetail >
    < /metadata >
< /response >

Sample JSON Response

{"response": {
  "metadata": {
    "requestId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "status": "success"
  },
  "result": {
    "documents": [
      {
        "key": "4A504D9EECC021DD426E3195E1903FA6",
        "versionNumber": "1.0",
        "firstName": "John",
        "lastName": "Smith",
        "_type": {
          "firstName": "string",
          "lastName": "string"
        }
      }
    ]
  }
}}