How to filter on a Managed Metadata column via REST in SharePoint 2013
Pardon the pun, but I just had a ‘clever workaround’ moment with SharePoint’s oData/REST implementation when it comes to filtering list items based on taxonomy (managed metadata) columns. Now I do not consider myself a developer, so this article is probably a little verbose for some readers, but should be helpful to power users or IT pros.
Here is an example term set called FilterDemo. You can see two levels of hierarchy.
Take the scenario of a custom list (called TestFilter) with a managed metadata column (called FilterDemo) that links to the above term set. Let’s also assume there are 3 entries in it as follows:
| Title | FilterDemo |
| A | A1 |
| B | B3 |
| C | Category A |
Using the wonders of the REST API, I am able to get access to all items in the list via the following URL:
http://site/_api/web/lists/getbytitle(‘TestFIlter’)/Items
If you execute that, and IE is has “feed reading view” turned off, you will get back lots of scary looking XML. If you collapse it though, you will see three entry tags in it. One for each item in the TestFilter list.
Using more wonders of REST (and oData), I can change the URL to filter my results so that I only get matching items back. For example: here I am filtering on Items where the Title field has “A” in it.
http://site/_api/web/lists/getbytitle(‘Testfilter’)/Items/?$filter=Title eq ‘A’
Now we get back just the one entry matching that criteria…
Okay, so there is nothing earth shattering about what I just did above and its well documented in various places. But look what happens when I try and filter items in the list based on the FilterDemo column which is Managed metadata based…
http://site/_api/web/lists/getbytitle(‘Testfilter’)/Items?$filter=FilterDemo eq ‘A1’
Boom! Browser returns an error. If I do the same thing using Fiddler to look at the trace, it reports a HTTP/1.1 400 Bad Request error.
So I start digging and come across articles from Phil Harding and Serge Luca informing me that Taxonomy columns are unsupported via REST. I got my hopes up when I came across an Andrew Connell article on filtering lookup fields, since behind the scenes the taxonomy field is actually a lookup field, but in the comments section, it seemed to confirm that this wasn’t doable. All seemed lost…
But in reading MSDN’s REST articles, I had a vague recollection that CAML could be done via REST queries. I knew that using CAML, it was indeed possible to filter taxonomy columns. I proved it using CAML Designer 2013, connecting to the TestFilter list and filtering it successfully using the following XML…
<Where> <Eq> <FieldRef Name='FilterDemo' /> <Value Type='TaxonomyFieldType'>A1</Value> </Eq> </Where>
So, armed with this knowledge, I came across an MSDN forum thread where a tantalising clue was offered. Christophe Humbert asked whether CAML queries could be done via the REST API and Erik C. Jordan provided this nugget of wisdom:
I was able to get the following to work:
POST https://<site>/_api/web/Lists/GetByTitle(‘[list name]’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query>[other CAML query elements]</Query></View>”}
Editors node: I will be a little verbose at this point in case you are not a developer or overly familiar with REST.
This approach looked exactly what I needed and I thought this was worth a shot, but since the remedy is a HTTP POST rather than a GET, I couldn’t do it with Internet Explorer, so I loaded up fiddler, and used the Composer function. I crafted the following POST with an empty CAML query as a test…
http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query></Query></View>”}
And this is the response I got…
HTTP/1.1 411 Length Required
A quick bit of googling, and I realise that some HTTP queries require the use of a ‘Content-Length‘ field in the HTTP header. The standard states that: “Any Content-Length greater than or equal to zero is a valid value”, so I tried this figure as shown below:
And this time I get the response:
HTTP/1.1 403 FORBIDDEN (The security validation for this page is invalid and might be corrupted. Please use your web browser’s Back button to try your operation again).
Another quick bit of googling I discover that I am missing another required HTTP header in my POST request. This is called the X-RequestDigest and it holds something called the form digest. The form digest improves SharePoint security because it is specific to a specific user, site and limited to a certain time frame. You need to request a form digest and then pass it back to SharePoint for subsequent calls. To get hold of the form digest, you have to make another REST call which generates one. This is done by making a POST request with an empty body to http://site/_api/contextinfo and extracting the value of the “d:FormDigestValue” node in the information returned. In fiddler it looks like the following…
If you look at the returned content from calling the _api/contextinfo method above, I have highlighted FormDigestValue. In Fiddler, copy this value into the Request headers section of the composer and retry the CAML request:
Now if you execute the request, we get data!
HTTP/1.1 200 OK
If you look a the raw results in fiddler, you will see a whole bunch of scary XML. If you examine the results using the XML parser built into Fiddler as shown in the image below, you will see very similar output to my original REST request that I started this article with – 3 entries in this list. It worked!
So now let’s add our CAML query into the XML and see if we can make it work. Recall that I successfully tested this query via the following CAML…
<Where> <Eq> <FieldRef Name=’FilterDemo’ /> <Value Type=’TaxonomyFieldType’>A1</Value> </Eq> </Where>
So I construct the following URL and paste into the Fiddler constructor:
http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><Where> <Eq> <FieldRef Name=’FilterDemo’ /> <Value Type=’TaxonomyFieldType’>A1</Value> </Eq> </Where> </Query></View>”}
With great excitement, I clicked “Execute” and received….
HTTP/1.1 400 Bad Request
Ah crap! Unfortunately I could not find a single example of this form of REST query to SharePoint, but I got a hint to the problem from Fiddler itself. It wasn’t happy with my request at all, showing the request as red.
Clearly I was doing something wrong, and being a non-developer I figured I wasn’t encoding things properly. So after some trial and error, I worked out that spaces were the issue. So where I was able to remove them I did, and those that I couldn’t, I encoded like so:
http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:”<View><Query><Where><Eq><FieldRef%20Name=’FilterDemo’/><Value%20Type=’TaxonomyFieldType’>A1</Value></Eq></Where></Query></View>”}
At this point fiddler stopped showing me an angry red colour and I clicked the Execute button. Wohoo! It works! Below you can see a single matching entry, just like my example when I filtered on Title column using the $filter parameter.
Expanding the XML indeed confirms it has matched term A1. 
Conclusion
While I was happy that I found a way to use REST to filter a list based on a Taxonomy column, I’m sure this method offers some interesting opportunities in various other scenarios.
In my company Seven Sigma, we have a worn-out post-it note that has the words “Alpha SharePoint Developer” written on it. This gets stuck to the office of whoever does the coolest coding trick and I’m happy to report that this little effort netted me the Alpha developer prize for the first time ever, principally because I then used this approach with SharePoint Designer 2013 workflows and it worked really well. In fact it worked so well that I have decided that using this with the new capabilities of SPD workflows warrants a blog series of its own.
Until then, I hope that this approach works for you and happy REST’ing!
Thanks for reading
Paul Culmsee
Excellent description of your analysis to come out with a solution. And yes a great outcome. Will be helpful and surely useful. I tag you as #AlphaDeveloper too. Gud on ya mate!
Kunal
Thanks Kunal – appreciate the feedback 🙂
Paul,
This is interesting and will check how this approach works out for KoSp Js – Knockout Js for SharePoint ( http://kosp.codeplex.com/ )
Thanks and Regards
Ashok Raja .T
Thanks Ashok. Your KoSp project looks great too.
Great post. Now i know two more things 1. You can use CAML query in REST calls 2. Taxonomy fields can be now called via REST API
Cool stuff mate.
Excellent man…really helpful!!!
Hi,
Thanks for the wonderful post.
I am using the same formula to fetch record from document library, i can get all Taxonomy field but i struggling to get Name (linked to document with edit menu). I need this field with link to the particular document or the name. Please help me to get this field.
Many thanks in advance for your help.
Regards,
H
Hi,
Thanks for the nice post. I am trying to use the same with Office 365 with https. When I am using GetItems method its throwing the following error
–1, Microsoft.SharePoint.Client.ClientServiceExceptionThe HTTP method ‘GET’ cannot be used to access the resource ‘GetItems’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource.
It appears that you are using a HTTP GET method when you are supposed to be using a POST method
regards
Paul
hi,
when i use the following in fiddler with response i get back from “ContextInfo” i get:
Access denied. You do not have permission to perform this action or access this resource.
Here is my steps:
1. Request headers for “contextInfo”
User-Agent: Fiddler
Host: ogere.sharepoint.com
Content-length: 0
Cookie: FedAuth=77u/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz48U1A+RmFsc2UsMGguZnxtZW1iZXJzaGlwfDEwMDMwMDAwODY4ZTZmZTBAbGl2ZS5jb20sMCMuZnxtZW1iZXJzaGlwfGtlbm55YnJpZ2h0QG9nZXJlLm9ubWljcm9zb2Z0LmNvbSwxMzAzMDMyNzEzMjE4MzM1NjIsVHJ1ZSxKSFY1dklqVjdEd1J1MVBvU1RoK0F3dmRWd2dmVVprWHRWdEloRHFYRC9iaE8zbmcxcmlZbEdGdDVOb054ZDViR2ltTEZXbG5xSTNTUCs4ZGwvM2VMUE4zMVFQVEplcXR3d1Z0MjhWSGcyYm1GTDh4elNiWnhLMHIrQzJNTXgra3FFS1FSeXNWK2hOd1dWTWZ2R2tWcTNLby9LRnJBZkpjcVdkQ1pTT2J0TjF2aHM3cFZPcjlnZGlqVFlWcW1WNWdiYlpjcnZPbVNqTTBKV0NzQVNCaHF3bFJrV3NNR1pQV3NnYXRkMFh5ZVUySmZMdEdBYUMveGxQeEoxZjJUMnNnWUU0YVk2SUlXeU5TU2g1QlJYVWZFc3Z0UjVJU0QxaDhoaXBsa3N4bVoxeWVacDRPWTdadUo0RitkemZvUjZKZEkrUGNhTGxGK1pkOWNGTTdEcmd2SGc9PSxodHRwczovL29nZXJlLWNhYzVjMzAxNjA3ODA4LnNoYXJlcG9pbnQuY29tL19sYXlvdXRzLzE1L2luaXQuanM/cmV2PWxHakVnY3ZiVjN3ajZ1YWdFTTQxU3c9PTwvU1A+; rtFa=RUQ5X+JcJxAYRY9liWzqhKRZ1sGj4yl6nR6ubiNHpgFVByl8ZCQ/1Jcw51nlr5yax8M9MXe55bunlcNTzRzSZgn34WGNa6q2tRz2HIY1I3I/JP2IbGkShIurdIz4/WBMxL73JBf+5AGc0n5LlWI1I4FJgWrm+gFMwmg9N5SfAWZxMQ1z36Y3OWEXuF4luhO6vkR/82IklqD1gu0hFZMO40UHQmsstXPbe7BjhqJQLriZ5qmdjD7ll76js9dCPLOElYn4cZqJKXen+2v1SiVmh0bB8U5AkzFe2URi1QJ812uwI+DO82vZgsLcmjAcfIFRDmOb/BQUabSV/E0KgyE8b75Vcg7gapu8xEP7l/P8nQTYXiIv/Lp+ryHtNIJq9MnDIAAAAA==
3. Get FormDigest Value from the response from above
4. Use FormDigestValue in X-RequestDigest
this where i get the error above
Samir,
I am using the following code to filter metadata, its throwing error. Please help me to fix this.
$.ajax({
type: “GET”,
url: “https://sites/site2/transmittals/_api/web/lists/getbytitle(‘On%20Transmittals’)/Items(query=@v1)?@v1={“ViewXml”:”S – Specification, Procedure, Data Sheet, Study Report, List, Schedule”}”,
headers: {
“accept”: “application/json;odata=verbose”,
“content-length” : 0,
“X-RequestDigest”: $(“#__REQUESTDIGEST”).val()
},
success: function (data) {
if (data.d.results) {
// TODO: handle the data
alert(‘handle the data’);
}
},
error: function (xhr) {
alert(xhr.status + ‘: ‘ + xhr.statusText);
}
});
Thanks,
Samir
Great article, but one question though. I would want to use this in my SharePoint Hosted App so how to do this with cross domain sites? Looking forward for your reply.
Hi Kashif
I think in a SharePoint hosted app you may have a problem because of cross site scripting. But best you check with a real developer instead of a fake one like me 🙂
Thanks for your reply. I switched gears and had to use javascript object model for my solution.
Will it be possible in SharePoint2010 also?
I doubt it, but I confess that I have not checked…
I tried this and tried some changes to it as well on SharePoint 2010. But yet to hit success.
Please let me know if someone hits a success with SharePoint 2010.
Regards,
Kangkan
http://www.geekays.net/
Seems this is POST method..When tried to browse this in the URL it is throwing a message ‘The HTTP method ‘GET’ cannot be used to access the resource ‘GetItems’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource’..
I want to use this in the Designer Workflow. Any inputs.?
Hi PCM
Read this series of articles for how to do POST methods using SPD in SharePoint 2013…
http://www.cleverworkarounds.com/2013/12/21/trials-or-tribulation-inside-sharepoint-2013-workflowspart-1/
Paul
Thank you so much for this post. It was exactly what i needed!
How can you encode the URLfor use within a JSOM REST call?
I’ve tried encoding from Chrome advanced REST client, escaping the double quotes with \ but I get the same error regarding not well formatted JSON.
The query I have works fine in a rest client just not from code within an app.
Thanks
Great Post and greater details. This will help me out a lot. Thanks.
Thank you for this. After finding this blog, I found some other references to using CAML with GetItems and using the CAML as the request body (no more encoding spaces!). Anatoly Mironov described its use here http://chuvash.eu/2014/03/25/using-caml-with-sharepoint-rest-api/ and referenced the MSDN documentation here http://msdn.microsoft.com/en-us/library/office/dn531433%28v=office.15%29.aspx#bk_ListGetItems.
This helped me out enormously, thanks man!
I extended upon this idea, because after being able to filter on term label with this solution, I wanted to filter on term GUID. This caused me some time to figure out how to do it, so in case it helps someone here is the solution to filter on term ID.
idOfTheTermHere
Couple of things to note:
1) It can’t be a query anymore, has to be
2) FieldRef Name has to point to the internal name of the term. Not the display/title name (which should end with a “_0”.
3) Pass the GUID in the value, which is a string. That’s why the Value Type is ‘Text’
Thank you Paul Culmsee, It is a great post and help me alot but I have an exception when I increase the length of the CAML query that we pass in the JSON for filter, the response I get is “404” “Unauthorized”. Is there any workaround for that . If it is than please let me know it will be great help for me.
FYI : In the list I have 6 managed metadata and each contains more than 100 values and I want to filter items based on the values.
Thanks in advance.
Regards,
Tapas Paul,
SharePoint Developer
Thanks Paul for very helpful work and article.
I tried your code and approach but facing one issue. It would be great if you can look into this matter:
Purpose: User created alert preferences using metadata to receive email whenever a new document is uploaded into one document library having similar associated metadata.
Approach: To filter managed metadata, on upload document, I am collecting all the metadata associated with the document and building the CAML query. I have another list where user has created some alert preferences which are stored in a custom list. Using this CAML query I want to filter this custom list and want to send an email to all the respective users.
But when passing the CAML query using POST method it is giving me following error:
“Cannot complete this action.\u000a\u000aPlease try again.”
And when I reduce the filters in query i.e. deselecting some managed metadata…… then it works fine.
So now please help me to resolve this issue
Thanks for sharing information on d:FormDigestValue
Appreciate your help!
Great post. Thank you for sharing
Nice Post.
One question where can i apply pagination in below syntax. Basically i want to get list items in batch of 5000 items. I searched alot but no luck.
POST https:///_api/web/Lists/GetByTitle(‘%5Blist name]’)/GetItems(query=@v1)?@v1={“ViewXml”:”[other CAML query elements]”}
Hi Paul,
I’ve been trying to use this method in SharePoint Designer Workflows 2013, but I can’t get the URL or JSON request body right. It cut’s the first tags. The response body looks like this:
{“query”:{“__metadata”:{“type”:”SP.CamlQuery”},”ViewXml”:”\u000a_tagNAME Support\u000a”}}
Sorry I take my last comment back. I copied the text from this blog and just modify values : http://site/_api/Web/lists/getByTitle(‘TestFilter’)/GetItems(query=@v1)?@v1={“ViewXml”:” A1 ”}
and the ‘ and ” were basically different characters. Bye Bye 3 hours.
Really too bad that Someguy’s post got corrupted. I was trying to figure out how to filter via TermGuid just as he discovered but unfortunately it seems he used angle-brackets and so lost most of the content of his post.
If anybody else has figured it out, please do tell!
This post is amazing, but I’m not able to filter on my taxonomy field. Instead, the endpoint always returns every document from the library.
Is the Value Type literally always going to be ‘TaxonomyFieldType’?
Does this method not work on document libraries?
Good post. Thank you for sharing these details with pictures and this will help a lot. https://bit.ly/2rb5sEa