In a previous blog post, we covered the basics of querying data in Aras Innovator using the Aras RESTful API. One of the examples covered in that blog post went over how to perform a simple filter to find items that had a specific property value. While this is effective for many common use cases, this blog post will cover how to utilize the RESTful API to perform more complex queries. If you'd like to follow along with the samples, we'd recommend using a REST client like Postman or Insomnia. The screenshots you'll see in this blog post were taken using Insomnia specifically.
Setting Up the Call
In our first blog post on the Aras RESTful API, we showed how to authenticate by passing the username and a hashed password directly through the headers for every request. Part of the focus during the development of Aras Innovator 12.0 was on security, so this kind of authentication is no longer supported. Instead, we recommend following the steps in this guide on using token authentication instead.
Filter Functions
The Aras RESTful API supports many of the same comparison operators that you might be familiar with through using AML such as equals (eq), greater than (gt), less than or equal to (le), etc. A full list of these can be found in Section 2.4.2 of the Aras Innovator Programmer's Guide. In addition to these comparison operators, the RESTful API also supports many useful functions that can be used with the $filter attribute to extend your filter beyond these basic checks.
For the first few sample queries, we will include the results returned with the filter applied in order to demonstrate that the filter is working. However, the complexity of the later filters will make it this kind of demonstration difficult from just the results of the query. If you're following along with the samples, we recommend checking the items you expect to be returned directly through the Aras Innovator client, so you can confirm that they are returned from our API call.
Filter with Wildcards
What if we don't know the specific value of a property? In AML, we can use a wildcard character ('%') to search on patterns instead of specific values. Including these wildcard characters is tricky inside of a URL because the percent sign is already used for URL encoding. To avoid needing to worry about encoding, the $filter attribute supports several functions that can be used to get the same functionality as using wildcard characters.
One useful way of utilizing these functions is to filter on date properties. Dates are stored in the database as both a day and time (e.g. 2019-05-31T17:30:00) which can make it impossible to know the exact value. Using the startswith function, we can filter on just a specific day without needing to worry about the time. This would be equivalent to using <created_on condition="like">2019-05-31%</created_on> in AML.
GET {base url}/Part?$filter=startswith(created_on, '2019-05-31')
Similarly, we can use the endswith function to perform a wildcard search on the end of a property's value. The query below would be equivalent to <item_number condition="like">%0103</item_number>.
GET {base url}/Part?$filter=endswith(item_number, '0103')
The most general of these functions is contains which checks if the string passed in is anywhere in the property's value. The equivalent here would be <name condition="like">%Washer%</name>.
GET {base url}/Part?$filter=contains(name, 'Washer')
Filter on Linked Item Properties
One of the more common use cases when querying for items in Innovator is to query based on some properties of a linked item such as when we want to find all of the items created by a particular user. The query below shows how we can find all of the Parts in the system that were created by a user named Mike Miller.
GET {base url}/Part?$filter=created_by_id/keyed_name eq 'Mike Miller'
The syntax for querying on a linked item is to include the property of the child item after a forward slash ('/'). In this case, we're querying based on the keyed_name of the User item that's linked via the created_by_id property of Part. One of the major benefits of this syntax is that you can continue to query on linked items of other linked items! For example, we could rewrite our query to instead find all of the Parts in the system that were created by any user who had Mike Miller as their manager.
GET {base url}/Part?$filter=created_by_id/manager/keyed_name eq 'Mike Miller'
This syntax also works for Relationships and related items. This lets us perform queries like finding all of the parent Parts of a child part with a given item number. One thing to keep in mind when writing queries on Relationships like this is that relationships with spaces in their names will need to be wrapped in square brackets like the query below.
GET {base url}/Part?$filter=[Part BOM]/related_id/item_number eq 'MP2954'
We can even combine this kind of linked item querying with the wildcard functions we covered earlier! This lets us perform queries like finding all of the parent Parts that have a Washer in their bill of materials.
GET {base url}/Part?$filter=contains([Part BOM]/related_id/name, 'Washer')
Filter on Collections of Items
In addition to querying on linked items, there are times when we want to base our query on entirely separate, unlinked items such as when we want to perform a reverse lookup. For these cases, the RESTful API has an in function implemented that lets us query on a collection of items. The query below takes advantage of this to return all of the Parts that are being referenced by a PR.
GET {base url}/Part?$filter=in(id,$root/PR($select=affected_item), 'affected_item')
This function is a little more complex so let's break it down piece by piece. The first argument defines the property on the main query item that we want to filter on which is just id in this case. The next argument defines a sub-query that builds the collection of items we'll be checking against. To specify the ItemType we're querying on from within an in function we first need to prefix the ItemType with $root to get us out of the context of the main ItemType of the query. In this case, we're getting all of the PRs with a small select attribute to get just the affected_item property. The last argument is the property of the collection that we will be comparing against. In this case, we're comparing the affected_item of the PRs to the id of the Parts.
The query for the collection is also not limited to just the $select statement. We can also define another $filter to get just the Parts that are referenced by a subset of PRs. One thing to note is that when we want to use multiple attributes inside of a function like this, we need to separate them with a semicolon (';') instead of the ampersand ('&') we would use normally.
GET {base url}/Part?$filter=in(id,$root/PR($select=affected_item;$filter=item_number eq 'PR-100001'), 'affected_item')
More Samples
One of the foundations of the Aras Innovator platform has always been its robust querying capabilities. This can be seen in AML, the Query Builder, and now in the Aras RESTful API. Keep an eye out for future blog posts that cover even more of the functionality of the RESTful API, and in the meantime, check out the Aras Labs GitHub page to find our latest open-source projects and sample code. In particular, the REST Upload Example, REST Auth Example, and PWA Sample App contain some great samples of REST queries being made in actual code.