Querying List Items with a Contains with the REST API

In my previous blog post, I talked about how to search on a term and reflect the query in the search page refiners (highlighting it in bold). I am continuing that theme of having long blog post titles and looking at interacting with the URL and REST, but this time looking into doing a “contains” query on a list to return any items which have the given search string in the relevant list field.

Before we go any further, let’s take a look at the list instance item structure with an extract of data that I will be using in this example:

Now querying against a list using a contains filter is pretty straight forward when you know how, you just need to know your way around the ‘substringof’ method and you can get what you want (i.e. to return all list items with the phrase ‘Shenzhen Office’ in the TileAudience field):

Lovely stuff. There is always a catch though and in this particular scenario it is you cannot query against some specific types of field and annoyingly this includes the ‘Note’ field type (for performance reasons) using this REST API method shown above. So, if like me, you are planning on storing more than 255 characters in your list item field, you will need to find another way. The answer, like with most things in SharePoint 2013 is Search.

 

Using Search API to Query List Items

Using the Search API (after hooking up our field(s) as managed properties in the search schema), we can achieve the same thing:

When you decode this, it looks like the following (focusing on the query aspect of the URL):

…querytext=’owsTileAudience:”Office J”‘&selectproperties=’owsTileAudience,TileNameOWSTEXT,TileURLOWSTEXT’

Pretty straight forward, but we are not out of the woods yet. If you query the above, you will not get any results because your search source will have lots of content which does not use the ‘Tile Audience’ field and therefore it cannot perform the contains aspect of the query on all of the content being queried (you will get no results). So what we need to do (to get this working as we would like), is to restrict the query to a subset of results which have the field (either directly or via a content type) and we can do this by content type or by the path (for readability my example will use path). Here is the final search API query decoded for you:

With this restricted query, we now have the desired result set returned from the API.

Querying List Items with a Contains with the REST API

Leave a Reply

%d bloggers like this: