Object Query Commands (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
OLAP REST Commands: Object Query Commands

---

Doradus OLAP supports two commands for submitting object queries. Details of object query parameters and the output formats returned by object queries are described in the section [OLAP Object Queries](https://github.com/dell-oss/Doradus/wiki/OLAP-Object-Queries).

Object Query via URI

An object query can submit all query parameters in the URI of a GET request. The general form is:

GET /{application}/{table}/_query?{params}

where {application} is the application name, {table} is the perspective table to be queried, and {params} are URI parameters, separated by ampersands (&) and encoded as necessary. The following parameters are supported:

• q=text (required): A DQL query expression that defines which objects to select. Examples:

    q=*		// selects all objects
    q=FirstName:Doug
    q=NONE(InternalRecipients.Person.WHERE(LastName=Smith)).Department=Sales
  

• s=size (optional): Limits the number of objects returned. If absent, the page size defaults to the search_default_page_size option in the doradus.yaml file. The page size can be set to 0 to disable paging, causing all results to be returned in a single page. Examples:

    s=50
    s=0		// return all objects
  

• f=fields (optional): A comma-separated list of fields to return for each selected object. Without this parameter, all scalar fields of each object are returned. Link paths can use parenthetical or dotted qualification. Link fields can use WHERE filtering and per-object value limits in square brackets. Examples:

    f=*
    f=_all
    f=Size,Sender.Person.*
    f=InternalRecipients[3].Person.DirectReports.WHERE(LastName=Smith)[5].FirstName
    f=Name,Manager(Name,Manager(Name))
  

• m=metric expression list (optional): A comma-separated list of metric expressions to be computed and returned for each selected objects. The syntax for metric expressions is the same as for aggregate queries. Examples:

    m=COUNT(DirectReports)
    m=COUNT(Messages.MessageAsSender) AS 'Messages Sent'
    m=COUNT(Messages.MessageAsSender),SUM(Messages.MessageAsSender.Size)
    m=COUNT(DirectReports^.WHERE(LastName:smith))+COUNT(Manager^.WHERE(Department:support))
  

• o=field [ASC|DESC] (optional): Orders the results by the specified field, which must be a scalar field belonging to the perspective table. Without this parameter, objects are returned in an internally-defined order. When an order field is specified, by default objects are sorted in ascending of the field’s value. Optionally, the field name can be followed by ASC to explicitly request ascending order or DESC to request descending order. Examples:

    o=FirstName
    o=LastName DESC
  

• k=count (optional): Causes the first count objects in the query results to be skipped. Without this parameter, the first page of objects is returned. Examples:

    k=100
    k=0		// returns first page
  

• g=token (optional): Returns a secondary page of objects starting with the first object _greater _than the given continuation token. The continuation token must be one returned in a previous query. The &g parameter can only be used for queries that are not sorted (no &o parameter). The &g and &e parameters cannot both be used in the same query.

• e=token (optional): Returns a secondary page of objects starting with the first object _equal _to the given continuation token. The continuation token must be one returned in a previous query. The &e parameter can only be used for queries that are not sorted (no &o parameter). The &g and &e parameters cannot both be used in the same query.

• shards=shards (required*): A comma-separated list of shard names. Only the specified shards are searched. Either this or the range parameter must be provided. Examples:

    shards=2010-01-01
    shards=13Q1,13Q2,13Q3
  

• range=shard-from[,shard-to] (required*): A starting shard name and optional ending shard name that defines the range of shards to search. If the ending shard-to name is omitted, all shards whose name is greater than or equal to the shard-from name are searched. Either this or the shards parameter must be provided, but not both. Examples:

    range=2013-11-01,2013-12-31	// "2013-11-01" <= shard name <= "2013-12-31"
    range=2013-01-01	// "2013-01-01" <= shard name
    range=2013	// "2013" <= shard name
  

Query results can be paged using either of two methods depending on whether or not the results are sorted with the &o parameter:

• Sorted queries: To retrieve a secondary page, the same query text should be submitted along with the &k parameter to specify the number of objects to skip. Doradus OLAP will re-execute the query and skip the specified number of objects.
• Unsorted queries: To retrieve a secondary page, the same query text should be submitted using either the &g or &e parameter to pass the continuation token from the previous page. This form of paging is more efficient since Doradus OLAP does not re-execute the entire query but instead continues the query from the continuation object. .

The following object query selects people whose LastName is Powell and returns their full name, their manager's name, and their direct reports' name. The shard named 2014-01-01 is queried:

GET /Email/Person/_query?q=LastName=Powell&f=Name,Manager(Name),DirectReports(Name)
&range=2014-01-01

A typical result in XML is shown below:

<results>
	<totalobjects>2</totalobjects>
	<docs>
		<doc>
			<field name="Name">Karen Powell</field>
			<field name="_ID">gfNqhYF7LgBAtKTdIx3BKw==</field>
			<field name="DirectReports">
				<doc>
					<field name="Name">PartnerMarketing EMEA</field>
					<field name="_ID">mKjYJmmLPoTVxJu2xdFmUg==</field>
				</doc>
			</field>
			<field name="Manager">
				<doc>
					<field name="Name">David Cuss</field>
					<field name="_ID">nLOCpa7aH/Y3zDrnMqG6Fw==</field>
				</doc>
			</field>
		</doc>
		<doc>
			<field name="Name">Rob Powell</field>
			<field name="_ID">sHUm0PEKu3gQDDNIHHWv1g==</field>
			<field name="DirectReports"/>
			<field name="Manager">
				<doc>
					<field name="Name">Bill Stomiany</field>
					<field name="_ID">tkSQlrRqaeHsGvRU65g9HQ==</field>
				</doc>
			</field>
		</doc>
	</docs>
</results>

The same result in JSON:

{"results": {
	"totalobjects": "2",
	"docs": [
		{"doc": {
			"Name": "Karen Powell",
			"_ID": "gfNqhYF7LgBAtKTdIx3BKw==",
			"DirectReports": [
				{"doc": {
					"Name": "PartnerMarketing EMEA",
					"_ID": "mKjYJmmLPoTVxJu2xdFmUg=="
				}}
			],
			"Manager": [
				{"doc": {
					"Name": "David Cuss",
					"_ID": "nLOCpa7aH/Y3zDrnMqG6Fw=="
				}}
			]
		}},
		{"doc": {
			"Name": "Rob Powell",
			"_ID": "sHUm0PEKu3gQDDNIHHWv1g==",
			"DirectReports" :[],
			"Manager": [
				{"doc": {
					"Name": "Bill Stomiany",
					"_ID": "tkSQlrRqaeHsGvRU65g9HQ=="
				}}
			]
		}}
	]
}}

As shown, requested link fields are returned even if when they are empty.

Object Query via Entity

An object query can be performed by passing all parameters in an input entity. Because some clients do not support HTTP GET-with-entity, the PUT method can be used instead, even though no modifications are made. Both these examples are equivalent:

GET /{application}/{table}/_query
PUT /{application}/{table}/_query

The entity passed in the command must be a JSON or XML document whose root element is search. The query parameters are given as child elements. URI query parameters map to the following element names:

URI Parameter	Element name
e	countinue-at
f	fields
g	countinue-after
k	skip
o	order
m	metric
q	query
s	size
shards	shards
range	shards-range

Here is an example search document in XML:

<search>
	<query>LastName=Powell</query>
	<fields>Name,Manager(Name),DirectReports(Name)</fields>
	<shards>2014-01-01</shards>
</search>

The same example in JSON:

{"search": {
	"query": "LastName=Powell",
	"fields": "Name,Manager(Name),DirectReports(Name)",
	"shards": "2014-01-01"
}}