Tour of query.v1
This topic explains how to write readable queris in Agility.
What is query.v1?
This endpoint was introduced in 13.2, Summer 2013. Please view the About Digital.aiAgility information from the help icon in the menu bar to see if you are on this release or later.
- A way to write queries in a more readable style using YAML or JSON.
- With the rest-1.v1/Data endpoint, Digital.ai Agility API queries are expressed in a REST syntax, with the Asset Type and ID encoded into the URL, and the query terms encoded into the URL query string. This encoding is not human-oriented, especially when URL query escaping is applied.
- The query parameters are simple enough to encode in another format, and the sub-query and group-query parameters are naturally hierachical. Because the parameter order doesn't matter, a mapping from parameter name to parameter value is sufficient. JSON is a standard way to encode such structured values, but has quite a lot of syntax for simple values. YAML is a super set of JSON, and offers a lighter-weight indentation based syntax, similar to writing an outline in a text editor.
- Allows retrieval of arbitrarily nested master/detail and hierarchical relationships. (For example, you can retrieve all the Schedules in a Scope, all the Iterations in the Schedules, and all the Workitems in the iterations, all in one go.)
- An arbitrary number of independent queries can be executed in one HTTP round-trip.
- The REST format only allows access to one root Asset Type per HTTP call, but does allow arbitrary "dotted" relations in the selection tokens.
- Data is returned in a natural JSON attribute format.
- Creating or writing to assets is not supported at this time.
Why use this instead of the REST endpoint?
- Native JSON format can be handled "out of the box" by many languages.
- The response format is a simple name to value mapping of attributes to their values. It contains very little metadata, and yields a usable object when deserialized by most JSON decoders. It is not necessary to write code that "unpacks" the returned data.
- Higher performance when issuing many queries.
- When HTTP round-trip time dominates the query response time, it can be useful to submit many queries in one HTTP transaction. The previous REST endpoint supported only one query per HTTP transaction, but the body query endpoint can accept an arbitrary number of independent queries. They are submitted to the retrieval subsystem in parallel.
Why use the REST endpoint instead?
- Need to write or update assets
- Have legacy XML-based code
- Desire strict REST and HTTP semantics
Fundamentals
Prerequisites
A valid reachable address must be configured for the system in the SMTP server configuration. The OAuth2 endpoints are communicated to the client using this fully qualified address, and the client must be able to reach that address.
How do I access the endpoint?
The query endpoint is located at query.v1 (actually it's at apiConsole.mvc) and requires OAuth2 Authentication using the scope query-api-1.0.
An endpoint that accepts Basic auth or the V1 Ticket cookie (for use in a browser session) is available at query.legacy.v1.
YAML vs JSON syntax
JSON and YAML have similar means of writing structured documents of mappings, sequences and scalars. The query syntax is a JSON or YAML document that contains the appropriate key/value elements. The query endpoint is a YAML parser, and since YAML is a proper super set of JSON, it will parse JSON input without issues. You may use whichever format you are more comfortable with, or that supports your usage pattern better.
JSON delimits structures with curly braces, square brackets, and quotes. White space is ignored and indentation is irrelevant. Since JSON is the native serialization format of JavaScript and is supported by many languages, it is suitable when you are submitting queries that are serialized from your own domain objects that build up a query object.
YAML delimits structures with indentation, dashes to indicate sequence items, or lack thereof to indicate a mapping entry. The syntax is minimal, and it's important to keep track indentation. It is more suitable for hand-written queries or for query examples.
For example, a single query in JSON and YAML
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br />"Estimate"<br />]<br />} | from: Story select: - Estimate |
Two independent queries as a single YAML document. The document contains a sequence with two query mappings in it.
| JSON | YAML |
|---|---|
<br> {<br> "from": "Story",<br/> "select": [<br> "Estimate"<br/> ]<br /> },<br /> {<br /> "from": "Member",<br /> "select": [<br /> "Name"<br /> ]<br /> }<br/>] | from: Story<br />select:<br /> - Estimate<br /><br />- from: Member<br />select:<br /> - Name |
Two queries in a multi-document YAML stream (JSON has no equivalent)
from: Story
select:
- Estimate
---
from: Member
select:
- Name
In cases where YAML's syntax may be ambiguous, such as with strings containing newlines or colons, you can apply JSON-style delimiters.
Components of a Query Mapping
To specify your query, provide some of the following keys in the query mapping. Keys that are not recognized are ignored. Keys with a value that don't match the format expected are ignored.
from
Every query must have a "from" key with a string value naming the Asset Type to query. All other keys are optional.
select
In order to retrieve any data, a selection list of the desired attributes is required. These attributes are in the Digital.ai Agility Selection Token syntax. Scalar attributes, relations, multivalued attributes, and traversal through a relation to all of the above are suppported.
The Oid Token for each asset is always retrieved. So if you include no selections, you'll get a list of the asset's OID Tokens.
The select key is a sequence of the following
- selection tokens as strings
- query mappings
An example of simple selections
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br /> "Name",<br /> "Estimate"<br />]<br />} | from: Story<br />select:<br /> - Name<br /> - Estimate |
An example of traversing a single-valued relation attribute
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name",<br /> "Scope.Name"<br /> ],<br /> "page": {<br /> "size": "1",<br /> "start": "0"<br /> }<br />} | from: Story<br />select:<br /> - Name<br /> - Scope.Name<br />page:<br /> size: 1<br /> start: 0 |
Result
[
[
{
"_oid": "Story:1144",
"Name": "Product Overview",
"Scope.Name": "Corporate Website"
}
]
]
An example of traversing a multi-valued relation attribute
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name",<br /> "Owners.Name"<br /> ],<br /> "where": {<br /> "Name": "Sales Rep Lookup",<br /> }<br />} | from: Story<br />select:<br /> - Name<br /> - Owners.Name<br />where:<br /> Name: Sales Rep Lookup |
And its result
[
[
{
"_oid": "Story:1147",
"Name": "Sales Rep Lookup",
"Owners.Name": [
"Willy Webguy"
]
}
]
]
an example of subselecting a relation
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name",<br /> {<br /> "from": "Owners",<br /> "select": [<br /> "Name",<br /> "Nickname",<br /> "Avatar.Content"<br /> ]<br /> }<br /> ],<br /> "where": {<br /> "Name": "Executive Bios",<br /> }<br />} | from: Story<br />select:<br /> - Name<br /> - from: Owners<br /> select:<br /> - Name<br /> - Nickname<br /> - Avatar.Content<br />where:<br /> Name: Executive Bios |
and result
[
[
{
"_oid": "Story:1146",
"Name": "Executive Bios",
"Owners": [
{
"_oid": "Member:1062",
"Name": "Willy Webguy",
"Nickname": "WW",
"Avatar.Content": "iVBORw0KGgoAAAANSUhEU...(bae64 encoded data)...ErkJggg=="
}
]
}
]
]
an example of aggregating a relation
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name",<br /> "Owners.@Count"<br /> ],<br /> "filter": [<br /> "Owners.@Count>'2'"<br /> ],<br /> "page": {<br /> "start": "0",<br /> "size": "2"<br /> }<br />} | from: Story<br />select:<br /> - Name<br /> - Owners.@Count<br />filter:<br /> - Owners.@Count>'2'<br />page:<br /> start: 0<br /> size: 2 |
and result
[
[
{
"_oid": "Story:1697",
"Name": "Pop-up blocker message.",
"Owners.@Count": "3"
},
{
"_oid": "Story:1700",
"Name": "Filter for call manager.",
"Owners.@Count": "3"
}
]
]
filter and where
The filter key is a sequence of Digital.ai Agility filter tokens as strings. Filter tokens name an attribute (possible traversing relations) and give a comparison operator and either a comparison value or a context variable that will be defined in the with clause.
For example,
from: Member
select:
- Name
filter:
- Name='Joe Koberg'
---
from: Member
select:
- Name
filter:
- Name=$myName
with:
$myName: Joe Koberg
the where key takes a value that is also a mapping. The keys of the mapping are taken as selection tokens, and the values of the mapping are taken as the value to compare against in an "equal" comparison.
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Member",<br /> "select": [<br /> "Name"<br /> ],<br /> "where": {<br /> "Name": "Joe Koberg"<br /> }<br />} | from: Member<br /> select:<br /> - Name<br /> where:<br /> Name: Joe Koberg |
sorting
Any selection token can be used to sort the results. include a "sort" key with a value that is a list of selection tokens to sort by. Tokens can be preceded by + or - to indicate ascending or descending sort.
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name",<br /> "Estimate"<br /> ],<br /> "sort": [<br /> "-Estimate",<br /> "+Name"<br /> ],<br /> "page": {<br /> "start": "0",<br /> "size": "5"<br /> }<br />} | from: Story<br /> select:<br /> - Name<br /> - Estimate<br /> sort:<br /> - -Estimate<br /> - +Name<br /> page:<br /> start: 0<br /> size: 5 |
result:
`[`
` [`
` {`
` "_oid": "Story:1158",`
` "Name": "CRM Integration",`
` "Estimate": "50"`
` },`
` {`
` "_oid": "Story:1238",`
` "Name": "Remote Customer Lookups",`
` "Estimate": "25"`
` },`
` {`
` "_oid": "Story:1214",`
` "Name": "Process Wizard",`
` "Estimate": "20"`
` },`
` {`
` "_oid": "Story:1154",`
` "Name": "Knowledge Base",`
` "Estimate": "15"`
` },`
` {`
` "_oid": "Story:1159",`
` "Name": "Web Seminars",`
` "Estimate": "15"`
` }`
` ]`
`]`
grouping
You may group a set of assets by a related-to value. For example, stories can be grouped by status. The queried assets are returned in the _children element of the grouped-on asset.
| JSON | YAML |
|---|---|
<br />{<br /> "from": "Story",<br /> "select": [<br /> "Name", "Number"<br /> ],<br /> "group": [<br /> {<br /> "from": "Status",<br /> "select": [<br /> "Name"<br /> ]<br /> }<br /> ]<br />} | from: Story<br /> select:<br /> - Name<br /> - Number<br /> group:<br /> - from: Status<br /> select:<br /> - Name |
result
[
[
{
"_oid": "NULL",
"Name": null,
"_children": [
{
"_oid": "Story:1215",
"Name": "User Interface Story",
"Number": "S-01054"
},
...
]
},
{
"_oid": "StoryStatus:133",
"Name": "Future",
"_children": [
{
"_oid": "Story:1153",
"Name": "Partnerships",
"Number": "S-01009"
},
...
]
},
{
"_oid": "StoryStatus:134",
"Name": "In Progress",
"_children": [
{
"_oid": "Story:1194",
"Name": "Enter RMA",
"Number": "S-01038"
},
{
"_oid": "Story:1196",
"Name": "Add Shipping Notes",
"Number": "S-01039"
}
]
},
...
]
]
find and findin
find provides basic substring matching. Provide the search term in the find key, and the list of selected attributes to search in the findin sequence.
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br /> "Name",<br /> "Number"<br />],<br />"find": "Update",<br />"findin": [<br /> "Name"<br />]<br />} | from: Story<br /> select:<br /> - Name<br /> - Number<br /> find: Update<br /> findin:<br /> - Name |
results
[
[
{
"_oid": "Story:1161",
"Name": "Home Page Updates",
"Number": "S-01017"
},
{
"_oid": "Story:1163",
"Name": "Ship-To Cannot be Updated",
"Number": "S-01019"
},
{
"_oid": "Story:1182",
"Name": "Update Member",
"Number": "S-01026"
},
{
"_oid": "Story:1200",
"Name": "Update RMA",
"Number": "S-01042"
},
{
"_oid": "Story:1915",
"Name": "Update to New Colors",
"Number": "S-01088"
}
]
]
page
You may limit the number of returned results, or start retrieving results starting from a particular index.
Results are returned in ID order if no other sorting is applied, so paging will always be deterministic.
Paging only works for the topmost query. You cannot limit/page the results of subselections.
Example query:
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br /> "Name"<br /> ],<br /> "page": {<br /> "start": "0",<br /> "size": "5"<br /> }<br />} | from: Story<br />select:<br /> - Name<br />page:<br /> start: 0<br /> size: 5 |
Example result:
[
[
{
"_oid": "Story:1144",
"Name": "Product Overview"
},
{
"_oid": "Story:1146",
"Name": "Executive Bios"
},
{
"_oid": "Story:1147",
"Name": "Sales Rep Lookup"
},
{
"_oid": "Story:1148",
"Name": "Site Search"
},
{
"_oid": "Story:1149",
"Name": "Customer Profiles"
}
]
]
with
You may define variables in your filter tokens that will be filled via the with mapping. This can allow parameterization and alleviate quoting issues.
with is a mapping of variable names to their values, and is applied over the entire query.
Example query:
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br /> "Name"<br />],<br />"where": {<br />"Scope": "$myScope"<br />},<br />"with": {<br />"$myScope": "Scope:1083"<br />}<br />} | from: Story<br />select:<br /> - Name<br />where:<br /> Scope: $myScope<br />with:<br /> $myScope: Scope:1083 |
asof
Historical data is kept for all assets, and you may query for the state of assets "as of" a particular point in time. The asof value is a string with a timestamp in ISO format.
OID tokens that are returned from a historical query have the Digital.ai Agility Moment Number appended, identifying the version of the asset that existed at that point in time.
Example query
| JSON | YAML |
|---|---|
{<br />"from": "Story",<br />"select": [<br /> "Name",<br /> "ChangeDateUTC",<br /> "Estimate"<br /> ],<br />"where": {<br /> "ID": "Story:1211",<br /> "asof": "2012-09-07"<br /> }<br />} | from: Story<br />select:<br /> - Name<br /> - ChangeDateUTC<br /> - Estimate<br />where:<br /> ID: 'Story:1211'<br /> asof: 2012-09-07 |
Example result
[
[
{
"_oid": "Story:1211:1265",
"Name": "Forgotten Passwords",
"ChangeDateUTC": "2012-09-06T15:06:26.7530000Z",
"Estimate": "5"
}
]
]
If you want to see the complete history of an asset, set asof to the value "All" (without the double quotes).
How data is returned
Data is returned as a sequence of result sets. Each result set is a sequence that contains the results from one query mapping. Each result is a JSON object with fields for the selections you defined in the query mapping.