In this part of the series, we’re going to discuss how to formulate queries against the VersionOne API, with particular focus on doing so with the query.v1 endpoint, which is an endpoint that accepts a query (and as you will see – multiple queries) in a JSON or YAML format, and returns data from the API call in a JSON format.

Here’s where we are at in this series:

If you’ve been following along with this series of articles, you should now have the basic building blocks of an integration including configurations, logging, parsing, and a HTTP client used to make requests of the VersionOne API. In Part 5, I used the example of a simple “ping” query to the API, and did so through the traditional rest-1.v1 endpoint, and if you tried that query you may have noticed that the format of the data returned was XML.

Introduced in the Summer 2013 release of VersionOne, the query.v1 endpoint allows you to submit your queries in either a JSON or YAML format, but unlike the rest-1.v1 endpoint, you can submit multiple queries in one shot.

Let’s compare them…here’s the “ping” query again in rest-1.v1:

http://localhost/versionone/rest-1.v1/Data/Member?sel=Name&where=IsSelf='true'

Now here it is in query.v1:

{
   "from": "Member",
   "select": [
      "Username"
   ],
   "where": {
      "IsSelf": "true"
   }
}

And here’s what you get in return:

[
   [
      {
         "_oid": "Member:20",
         "Username": "admin"
      }
   ]
]

Note: With the release of VersionOne Summer 2014, the query.v1 endpoint supports all forms of authentication including BASIC, Windows, and OAuth. If you are running an earlier release of VersionOne and want to use BASIC or Windows authentication, you will need to use the query.legacy.v1 endpoint because prior to Summer 2014, query.v1 only supported OAuth. See the Security topic in the VersionOne Developer Community for a complete list of endpoints and the authentication methods that they currently support.

The advantages of using query.v1 is that your queries will be in a more readable format, you can submit multiple queries in one request, and the amount of meta data returned is minimal, thereby resulting in sometimes significant performance improvements. Note that what gets returned is not just a single JSON object, but is in fact an array of an array of JSON objects. This is because query.v1 may return multiple objects in the inner array based on what you ask for, and if you send multiple queries, they will be returned in the outer array.

Note: There is one major caveat to using query.v1 – it only supports getting data. To create or update assets in VersionOne, you will still need to use rest-1.v1.

Let’s try multiple queries, this time using YAML:

from: Epic
select:
  - Name
  - Description
  - Status.Name
  - Value
where:
  ID: 'Epic:3083'
---
from: Story
select:
  - Name
  - Status.Name
  - Estimate
  - Description
where:
  Super: 'Epic:3083'

And here’s the result:

[
   [
      {
         "_oid": "Epic:3083",
         "Name": "Test Epic 1",
         "Description": "<p>This is a short description of an epic.</p>",
         "Status.Name": "Implementation",
         "Value": "100"
      }
   ],
   [
      {
         "_oid": "Story:3085",
         "Name": "Story 1",
         "Status.Name": "Future",
         "Estimate": "10",
         "Description": "<p>Juts a simple story.</p>"
      },
      {
         "_oid": "Story:3086",
         "Name": "Story 2",
         "Status.Name": null,
         "Estimate": null,
         "Description": "<p>And yet another simple story.</p>\n<p>&nbsp;</p>\n<p><em><strong>With a
            list!</strong></em></p>\n<p>&nbsp;</p>\n<ul>\n<li><span style=\"font-size: 0.9em; line-height: 1.144em;\">item
            1</span></li>\n<li><span style=\"font-size: 0.9em; line-height: 1.144em;\">item 2</span></li>\n</ul>\n<p>&nbsp;</p>"
      }
   ]
]

In this case, the queries asked for a specific Epic, and then all the Stories associated with that Epic, which in this case, was two.

A final but important point to make about getting data from VersionOne using query.v1 is that when you make the HTTP request, you pass the query content in the payload of an HTTP POST request.

Here’s what that looks like:

private String jsonQuery = "http://localhost/versionone/query.legacy.v1";
public void verifyBasicQueryConnection() throws IOException, V1ConnectionException {

CloseableHttpClient  httpclient = HttpClients.createDefault();
HttpPost httpPost = new HttpPost(jsonQuery);
CloseableHttpResponse httpResponse = null;

String jsonQuery = "{'from':'Member','select':['Username'],'where':{'IsSelf':'true'}}";

try {
String authString = username + ":" + password;
byte[] authEncodedBytes = Base64.encodeBase64(authString.getBytes());
String authEncodedString = new String(authEncodedBytes);

httpPost.setHeader("Authorization", "Basic " + authEncodedString);
httpPost.setHeader("User-Agent", "com.mycompany.myapp/1.0");
StringEntity jsonEntity = new StringEntity(jsonQuery);
httpPost.setEntity(jsonEntity);
httpResponse = httpclient.execute(httpPost);

HttpEntity entity = httpResponse.getEntity();
String responseBody = EntityUtils.toString(entity, "UTF-8");

System.out.println(httpResponse.getStatusLine());
System.out.println(responseBody);

checkResponseStatus(httpResponse, responseBody);

} catch (ClientProtocolException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
httpResponse.close();
}

In this code sample, I’m using an HttpPost object instead of HttpGet, and then after setting the header values for the request, I use the setEntity method to set the payload of the request. Other than that, the code is much the same as when doing an HttpGet with the rest-1.v1 endpoint.

Note: There is far more to the query.v1 endpoint than can be covered in a blog post, be sure to see Tour of query.v1 in the VersionOne Developer Community for more in-depth coverage.

Hopefully you now have a sense of how you can make use of different endpoints to query data in VersionOne. In the next article, I’ll show you how to make HTTP requests that create and update data in VersionOne.

As always, stay agile my friend…

Join the Discussion

    • user

      Could You Please Share All The Parts of Java integartion

    • user

      Could You Please Provide All The Part Of Building VersionOne Integrations With Java

    • Jay

      Hi,
      Can you show me how to apply YAML code to select all Stories where owner is null i.e, no owners have been assigned. I’ve tried many variations of the following to no avail.
      from: Story
      select:
      – Name
      where:
      Owners: null
      I’ve been quite stumped with trying to figure this out for the last several days and can really use an expert’s help!

    • Terry

      Hi Jay,

      Thanks for the inquiry, you could use the snippet below:

      from: Story
      select:
      – Name
      – Owners
      where:
      Owners.@Count: 0
      page:
      start: 0
      size: 10

      Thanks,
      Terry

    − 3 = 1