Skip to end of metadata
Go to start of metadata

Description

 

The Query API allows a User to retrieve data by running a dynamic Query.

 What is a Dynamic Query?

 

In apstrata, Documents that are saved in a Store can be retrieved by running a Dynamic Query or a Saved Query.

A Dynamic Query is one that is built every time on the fly by specifying the condition, fields to return, sort order, and aggregates.

A Saved Query is one whose definition is saved for later use. Rather than every time specifying the condition, fields to return, sort order, and aggregates, a developer can add a Saved Query whose definition specifies all the previously mentioned query constructs. Each Saved Query has a name which is used to run the Query and get back the results.

Moreover, a developer can implement a pagination model for the matching results by specifying the number of Documents per page as well as the number of the page to return. This is very useful for cases where a huge number of Documents matches the Query condition. In these cases, the results will be returned in pages or chunks rather than a whole block which impacts the performance of the application at hand.

Furthermore, when running a Query, a developer can look for documents matching a specific full text search criteria. In this case, documents satisfying both the Query condition and the full text search criteria will be returned in the results.

In terms of security, Documents or fields within a Document are returned in the results only if their read ACL contains the User or one of the Groups of the User running the Query

A developer may run as many Queries as needed per store. However, based on the apstrata account type, there is a limit on the number of predicates used in the Query condition, on the number of Documents returned in the results, and on the total size of the result set.

 

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

Query Condition Syntax

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

Example:
name<string> = “James”

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 and defaults to string. Note that we do not support fields of type text in the query condition, however, you can query on them using the Full Text Search feature.

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)

Fields to be Returned Syntax

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,

Apstrata Queries can also return Derived Fields. These are fields that can be returned by a query, but do not exist in the document. Specifically, the "distance" between geospatial points is a derived field recognized by Apstrata.

When returning fields, the distance formula can be expressed in several ways:

  • It can be passed a pair of geospatial points, each represented as a latitude and longitude pair. (Example: distance ('1.0', '2.0', '3.0', '4.0') returns the distance between the geopatial coordinates (1.0, 2.0) and (3.0, 4.0)).
  • It can be passed a pair of geospatial fields. (Example: distance (gpsLocation1, gpsLocation2)).
  • It can be passed a geospatial field and a geospatial point. (Example: distance(gpsLocation, '1.0', '2.0')).

In all cases, the value returned will be the distance in kilometers between the two geospatial locations.
Please note that derived fields are returned as a separate element in the response, as seen in the example below.

 

 

Sort Syntax

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.

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.

Count

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.

Aggregates

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:

 

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:


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?

Query Name

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

Permissions

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

Specific Request Parameters

(Refer to Common Request Parameters)

NameDescriptionRequiredDefaultPossible Values
apsdb.storeThe store in which you are executing your query.NoDefaultStore 
apsdb.queryThe query condition to execute.No  
apsdb.queryFieldsThe list of fields that should be returned by the query in addition to the optional queryFields requested, the query will return the document_key.No (mandatory if not count or aggregate is defined)  
apsdb.sortThe fields on which to sort. Note that this paremeter will be ignored when using the apsdb.ftsQuery parameter.No  
apsdb.resultsPerPageDetermines the number of results per page to return.No50 
apsdb.pageNumberDefines the page number of the page to return in case there is more than one 1.No  
apsdb.ftsQueryThis string is used to refine the search after executing the query condition by performing a full text search on the returned documents. Note that when using the apsdb.ftsQuery parameter, the apsdb.sort and apsdb.aggregateExpression parameters will be ignored.No  

apsdb.count
Specifies if the amount of results should be returned or not.
When set to true, the total number of documents found by the search will be returned. However, if the apsdb.ftsQuery parameter was sent in the request, the count will be equal to the number of documents returned in the first page instead of the total number of hits.
Nofalsetrue
false


apsdb.forceCurrentSnapshot
In order to avoid data inconsistency, even for a short duration of time, the user can send this parameter with value "true" to query data from the master database server. Will query from slave database server if the value is set to “false”.Nofalse
true
false

apsdb.aggregatePage
Specifies if the aggregate function should be executed on the documents that are displayed on the current page.Notruetrue
false
apsdb.aggregateGlobalSpecifies if the aggregate function should be executed on all the documents returned by the condition.Nofalsetrue
false

apsdb.aggregateExpression
Contains the aggregate expression to execute. Note that this parameter is ignored when using the apsdb.ftsQuery parameter.No  
apsdb.aggregateGroupByGroups the aggregate result by one or more columns.No numeric
string
date
apsdb.runAsIt allows the owner to run a service as one of his own users. The possible values are any of the usernames.No  
apsdb.deniedFieldsThis parameter gives the owner the possibility to request the denied fields for the runAs user. Values of these fields will not be returned. Please note that if the owner runs the query as owner, no field will be denied. This only applies to runAs user and based on the security ACL set on the document. Also note that if a user has a write ACL permission on the document, he can also read the document.No  
apsdb.queryNameThe name of the saved query that the user wants to execute.
Yes (if the user is executing a saved query)
  
apsdb.lockSpecifies if the documents retrieved should be locked in the database [when querying by documentKey]. Locking documents is only available within a script inside a transaction.Nofalse
true
false
apsdb.includeFieldTypeTo limit response size, JSON query responses do not include field type information for each field in a document. Setting this paramter to true will cause each document to have an additional attribute in the JSON response "_type" which lists the fields included in the document and their corresponding field types.Nofalse
true

false
apsdb.clientCacheTimeSets the "Cache-Control" response header to cache the response in an HTTP 1.0, 1.1 RFC-compliant client. The value of this parameter is set as the value of max-age, eg. a value of 3600 will result in a response header: "Cache-Control": "max-age=3600".No  
apsdb.authTokenThis parameter is used to sign the request with a token that was initially obtained by calling VerifyCredentails  API. For more details on signing requests, please refer to the page entitled Authentication .No  

 

Specific Response Elements

(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:

 

Specific Logical Errors

(Refer to Common Logical Error Codes)

 

ErrorMessage
Status Code
INVALID_QUERY_WILDCARD_USEWrong syntax, cannot use wildcards at the beginning of the pattern.400
INVALID_QUERY_CONDITIONThere is an error in the syntax of your query, refer to the user documentation for help.400
MAX_PREDICATES_EXCEEDEDThe maximum number of predicates allowed is: [predicatesLimit].400
STORE_REQUIREDThe store was not sent in the request.400
MAX_RESPONSE_DOCUMENTS_EXCEEDEDThe number of results [resultsPerPage] is larger than the maximum allowed number of results [maxResults].400
STRING_TO_NUMERIC_EXCEPTIONCannot convert string value to int. Evaluated value [strPageNumber].400
INVALID_QUERY_REQUESTThe query request must contain either requested fields, a count, or an aggregate expression

Or

Non-optional view predicate has no value for field: [fieldName]

Or

The query request must contain either a query or a full text search
400
INVALID_SORT_SYNTAXThere is an error in the syntax of your sort fields, refer to the user documentation for help.400
MAX_QUERY_RESPONSE_SIZE_EXCEEDEDThe response size [responseSize] is larger than the maximum response size [maxResponseSize].400
INVALID_AGGREGATE_SYNTAXAggregate [aggValue] is invalid.400
INVALID_AGGREGATE_SYNTAX_MISSING_FIELDThe field is missing from the aggregate expression [aggValue].400
BAD_AGGREGATE_FIELD_TYPEMetadata fields and non-numeric fields cannot be used in the aggregate functions.400
INVALID_QUERY_SYNTAXAn error occurred while executing your query, refer to the user documentation for help.400
INVALID_AGGREGATE_NAME_SYNTAXAggregate name [aggName] is invalid.400
PERMISSION_DENIEDNot enough permission to perform the requested action.403
INVALID_QUERY_FIELDS_SYNTAXYou can send either a comma separated list of field names or the symbol * in the parameter "queryFields" but not both.400
INCORRECT_DATE_FORMATAn incorrect date format is sent in the request.400
SAVED_QUERY_NOT_FOUNDThe query [queryName] that you are trying to execute was not found.404
INVALID_CLIENT_CACHE_TIMEThe value of [apsdb.clientCacheTime] must be an integer, got [unexpectedValue].400
MUST_BE_ANONYMOUSThe parameter [apsdb.clientCacheTime] can only be sent with anonymous query requests.400

 

Dynamic Query Examples

Sample Request

POST parameters:

Sample XML Response

Success XML:

 

Failure XML:

 

Sample JSON Response

 

Saved Query Examples

Sample Request

 

POST parameters:


Sample XML Response

Success XML:

 

Failure XML:

 

Sample JSON Response

  • No labels