Introducing our Beta Item Search API with Facets

One of the most consistent pieces of feedback we get from customers both large and small is that they wish they could query for items across products. Many moons ago when I first started programming on Sprintly I made a conscious decision to treat projects as silos. With the advantage of hindsight, it’s apparent that people use projects for things other than segregating tickets for different products. For instance, many of our customers break up their product into multiple projects (e.g. API, Mobile, Web) or use them to segregate tickets by team (e.g. Marketing, Ops, Development). At Sprintly our approach is to use tags for everything; we love this all-in-one approach, but recognize it doesn’t work for others.

In addition to this, customers have asked for the ability to do negative filters (e.g. NOT tagged with “foo“), stemming support in search results, and structured query languages. A few weeks ago we started work on an entirely new search index. This new search index, which is built on the unicorn that is Elasticsearch, supports stemming (English only for now), negative filters, and a robust query syntax that will be familiar to anyone who’s used Gmail or GitHub’s advance search syntax.

The new API endpoint can be found at /api/items/search.json and accepts the following arguments:

• q – The query you’d like ran (e.g. “dashboard bug”). This item is optional. Querying with an empty query string will return all items for all products the requesting user is a member of.

• sort – Which field to order results by. By default, we return the best match according to the search index.

• order – Which order to sort the results in (asc or desc).

• offset – Which record in the result set to start at. Defaults to 0.

• limit – How many records to return starting from offset. Defaults to 250.

• facets – A comma separated list of fields to return facet counts for (e.g. tag,status).

The search query syntax supports a number of fields for filtering. The following fields can be appended to your query specified in q to either filter results for keywords in q (e.g. design -tag:mobile) or as values in the facets argument:

• assignee – Filter items assigned to the user ID specified (e.g. assignee:1)

• author – Filter items created by the user ID specified (e.g. author:1)

• accepted_by – Filter items accepted by the user ID specified (e.g. accepted_by:1)

• loved_by – Filter items that have been favorited by the user ID specified (e.g. loved_by:1)

• mentioning – Filter items that mention the user ID specified (e.g. mentioning:1)

• team – An item’s team consists of anyone who has had a meaningful interaction. Anyone that favorites, follows, comments on, is mentioned in, or uploads an attachment to is added to an item’s team along with the creator, any assignee, and acceptor. Rather than using loved_by:1 mentioning:1 assignee:1, you could use team:1.

• blocks – Filter for items that are blocking the user ID specified (e.g. blocks:1)

• title – Filter for items whose title matches the search terms specified. title:"dashboard" would filter for items whose title matches the phrase “dashboard”.

• description – Filter for items whose description matches the search terms specified. description:"firefox" would filter for items whose description matches the phrase “firefox”.

• product – By default all queries will return all items for any product the requesting user has access to. To filter items by a specific product, or products, you use the product ID (e.g. product:1 for just product ID 1’s items or product:1 product:4 for both product ID 1 and 4’s items).

• favorites – Filter for items with a given number of favorites (e.g. favorites:>0).

• blocking – Filter for items that are blocking a given number of items (e.g. blocking:>0 would return items blocking at least 1 other item).

• blockers – Filter for items with a given number of blockers (e.g. blockers:>2 would return items with more than 2 blockers).

• status – Filter items for a given status. Valid values are someday, backlog, in-progress, completed, and accepted (e.g. status:backlog status:in-progress would return all items that are in the Backlog or Current columns).

• type – Filter for items of a given type. Valid values are task, story, defect, and test.

• size – Filter for items with a given size estimate. Valid values are ~, S, M, L, and XL.

• has – Items possess a number of qualities; this field allows you to query for items that possess a certain quality. For instance, you might be looking for stories that have sub-items. type:story has:children would get you what you want. We assess each item with a number of these possessive qualities:

  • moved – Whether the item has been moved to another product or not.

  • pr – Whether the item has had a PR issued against it.

  • commit – Whether or not an item has had a commit pushed relating back to it.

  • attachment – Whether or not an item has one or more attachments.

  • comment – Whether or not an item has one or more comments.

  • favorite – Whether or not an item has been favorited.

  • children – Whether an item has sub-items or not.

  • tag – Whether or not an item has one or more tags.

  • estimate – Whether or not an item has been assigned a size estimate.

• is – Items also have a number of qualities related to its state. The is facet allows you to filter an item’s state attributes. For instance, is:duplicate will return any item marked as a duplicate while is:unassigned will return any item lacking an assignee. We assess each item with a number of these state attributes:

  • duplicate – Whether or not the item is a duplicate of another item.

  • duplicated – Whether or not one or more items has been marked as a duplicate of the item.

  • blocked – Whether or not the item is currently blocking another item.

  • blocker – Whether or not the item is a blocker for one or more items.

  • unassigned – If the item does not currently have an assignee.

  • assigned – If the item does have an assignee.

  • child – If the item is a sub-item.

• parent – Filter for items with the given parent. parent:55 would return any item whose parent is #55. Keep in mind the API is cross-product by default and ticket numbers are not globally unique. This mean it is possible to get sub-items for two parents that share the same number.

• number – Filter for items specifically by their number. Again, ticket numbers are not unique across product so it’s entirely possible number:1 would return more than one ticket for customers that are members of multiple products.

• tag – Filter for items by a specific tag. This is an OR operation grouped by modifier. In other words tag:foo tag:bar will return items tagged either “foo” OR “bar”, while tag:foo tag:bar -tag:baz -tag:bitz would return items tagged with either “foo” OR “bar” AND are NOT tagged with “baz” OR “bitz”.

• created – Filter for items that were created before or after a given date. Date fields support >, <, >=, and <= as well. Try created:>=2014-01-01 to find all items created on or after January 1st, 2014.

• triaged – Filter for items that were triaged (moved from Someday to Backlog) before or after a given date.

• started – Filter for items that were started before or after a given date.

• closed – Filter for items that were closed before or after a given date.

• accepted – Filter for items that were accepted before or after a given date.

• last_active – Filter for items that were last active before or after a given date.

• modified – Filter for items modified before or after a given date.

• who – Filter for stories whose who field matches the search terms specified. who:"paying customer" would filter for items whose who field matches the phrase “dashboard”. You can also facet by this field to get a breakdown of stories by the who field.

• what – Filter for stories whose what field matches the search terms specified. what:"dashboard" would filter for stories whose what field matches the phrase “dashboard”.

• why – Filter for stories whose why field matches the search terms specified. why:"use firefox" would filter for stories whose why field matches the phrase “use firefox”.

All of the above facets allow for negative querying. To apply a negative facet simply add - to the front of the facet name (e.g. -tag:foo would return items that are not tagged with “foo”). This is particularly interesting when you consider the has and is facets. For instance -has:pr would return any item that does not have a PR issued against it. You can, of course, do -keyword or -title:"some phrase".

The API is JSON only and returns the following arguments:

• q – The query as parsed by the backend. I would give our query parser a solid B- right now; there’s for sure some edge cases that may not work. Please let us know on Twitter if you have a query mis-match. We’re logging them in Kibana as well to keep an eye on any issues.

• total_count – The total number of items that matched the given query.

• items – A list of item objects. These item objects are large JSON objects in that they embed full item objects for parent and children. You should have all the data you need to render an item card.

• facets – A dictionary of facets keyed by field name. Each value is a dictionary keyed by facet values and their respective facet counts. Not that this is only returned when a list of fields is sent in the facets argument when the request is made.

Phew! That’s a lot to take in. So what kind of queries are now possible with our new search API? We’re not entirely sure where to start ourselves, but here’s a few of my favorites from my various testing.

• type:story status:someday assignee:1 – Returns all stories in Someday assigned to user ID 1.

• type:story is:blocker has:pr – Returns all stories blocking other items that have a PR issued against them.

• author:1 has:comment – Returns all items created by user ID 1 that also have a comment.

• tag:foo-tag:bar completed:>=2014-01-01 completed:<=2014-01-31 – Returns all items tagged with “foo” but not tagged with “bar” that were completed in January of 2014.

• "mission control" tag:dashboard -tag:python – Returns all items matching the search phrase “mission control” and tagged with “dashboard” and not tagged with “python”.

• design “mission control” -tag:python status:completed – Returns all completed items matching the keyword design and the search phrase “mission control” and is not tagged with “python”.

It’s important to note a few things about the way search works:

• Keywords and search phrases are special. Unlike other facets, they are grouped with an AND as opposed to an OR. In other words foo "my stuff" matches foo AND "my stuff" while assignee:1 assignee:2 matches assignee:1 OR assignee:1.

• Facets are grouped by field. These fields are combined into a grouped OR statement. Each group of fields are then grouped by AND. This means that is:blocked is:child status:backlog status:someday would return (is:blocked OR is:child) AND (status:backlog OR status:someday).

• The + modifier is implied. No need to do +tag:foo +tag:bar.

• It is possible to create nonsensical search queries, such as is:child has:children, which would never return results because our data model doesn’t allow sub-items to have sub-items. Use your newfound powers responsibly.

• The API returns total_count and allows offset and limit. This should make pagination reasonably easy to implement.

• The facets field is pretty rad; we’re just starting to dig into it. We’re thinking about replacing our graph AJAX endpoints with facet queries. If you just want the facet counts, you can pass limit=0 to the API to return just facets; items is just an empty list.

Needless to say, we’re extremely excited to get this into your – our customers’ – hands. We’re equally excited to start rolling this out across the application as well. We’ve been actively porting our frontend to use our API over CORS. Our long-term vision is that our frontend and our customers’ API applications use identical endpoints.

Until then, the existing search index, also served by Elasticsearch, will continue to power the existing /product/{product_id}/items.json API endpoint as well as search within the Sprintly product.

Please let us know what you think about the new API or if you end up making any cool apps with it. If JavaScript is your thing, we have an NPM module that’ll connect you to our RESTful CORS API, as well as a module for interacting with this search syntax