Was this article helpful?

Tour of query.v1

Last modified 15:13, 30 Sep 2013

What is query.v1?

This endpoint was introduced in 13.2, Summer 2013. Please check Support Center -> About 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, VersionOne 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 superset 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 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 superset 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 a YAML document

from: Story
select:
  - Estimate

The same query as a JSON document

{
  "from": "Story",
  "select":
    [
      "Estimate"
    ]
}


Two independent queries as a single YAML document. The document contains a sequence with two query mappings in it.

- from: Story
  select:
    - Estimate

- from: Member
  select:
    - Name

The same, as a JSON document

[
  {
    "from": "Story",
    "select": [
      "Estimate"
    ]
  },
  {
    "from": "Member",
    "select": [
      "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 VersionOne 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

from: Story
select:
  - Name
  - Estimate


An example of traversing a single-valued relation attribute

from: Story
select:
  - Name
  - Scope.Name
page:
  size: 1
  start: 0

 

Result

[
  [
    {
      "_oid": "Story:1144",
      "Name": "Product Overview",
      "Scope.Name": "Corporate Website"
    }
  ]
]

 

An example of traversing a multi-valued relation attribute

from: Story
select:
  - Name
  - Owners.Name
where:
  Name: Sales Rep Lookup

 

And its result

[
  [
    {
      "_oid": "Story:1147",
      "Name": "Sales Rep Lookup",
      "Owners.Name": [
        "Willy Webguy"
      ]
    }
  ]
]

 

an example of subselecting a relation

from: Story
select:
  - Name
  - from: Owners
    select:
      - Name
      - Nickname
      - Avatar.Content
where:
  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

from: Story
select:
  - Name
  - Owners.@Count
filter:
  - Owners.@Count>'2'
page:
  start: 0
  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 VersionOne 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.

from: Member
select:
  - Name
where:
  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.

from: Story
select:
  - Name
  - Estimate
sort:
  - -Estimate
  - +Name
page:
  start: 0
  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.

from: Story
select:
  - Name
  - Number
group:
  - from: Status
    select:
      - 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 thefind key, and the list of selected attributes to search in the findin sequence.

from: Story
select:
  - Name
  - Number
find: Update
findin:
  - 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:

from: Story
select:
  - Name
page:
  start: 0
  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:

from: Story
select:
  - Name
where:
  Scope: $myScope
with:
  $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 VersionOne Moment Number appended, identifying the version of the asset that existed at that point in time.


example query

from: Story
select:
  - Name
  - ChangeDateUTC
  - Estimate
where:
  ID: 'Story:1211'
asof: 2012-09-07

example result

[
  [
    {
      "_oid": "Story:1211:1265",
      "Name": "Forgotten Passwords",
      "ChangeDateUTC": "2012-09-06T15:06:26.7530000Z",
      "Estimate": "5"
    }
  ]
]

 


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.

 

 

Was this article helpful?
Pages that link here
Page statistics
2446 view(s) and 29 edit(s)
Social share
Share this page?

Tags

Comments

You must to post a comment.

Attachments

 
Agile Made Easier