Retrieving Multiple SharePoint Managed Metadata Columns via REST
I’ve used this code a few times in different projects now, and I wanted to post it in case it’s useful for anyone. Most of us know that the REST APIs still have some weaknesses, and working with Managed Metadata is definitely one of them. (See: Let’s Capture Missing or Insufficient SharePoint REST Endpoints)
If you have a couple Managed Metadata columns in your list and you just retrieve the columns in REST like so…
request: {
method: "GET",
url: _spPageContextInfo.siteServerRelativeUrl +
"/_api/web/lists/getbytitle('ListName')/items?" +
"$select=ID,Title," +
"MyLocation,MyDepartment" +
"&$top=5000",
headers: {
"Accept": "application/json; odata=nometadata"
}
}
…you’ll get back data like this:
{
"Id": 5,
"ID": 5,
"Title": "Item Title",
"MyDepartment": {
"Label": "6",
"TermGuid": "748ca38e-9cea-488f-9a9a-6a20f6dff80a",
"WssId": 6
},
"MyLocation": {
"Label": "5",
"TermGuid": "c8cb412b-ed99-4034-8ca7-5019940ec354",
"WssId": 5
},
}
That’s not very helpful at all, since we don’t get the Term itself, just some gobbledy-gook pointers and a GUID. What we really want is the text for the term.
I found a useful thread on SharePoint StackExchange. As is so often the case, Mikael Svenson (@mikaelsvenson) had posted an answer which helped a lot. (We need more Mikael!)
You can do a workaround using the TaxCatchAll field as long as you know which terms belong to which taxonomy field – if you have multiple.
Looking into this, retrieving the TaxCatchAll column was indeed the way to go. By including it in my REST calls, and requesting both the Term and the ID, I could get the text values for the Managed Metadata (aka Taxonomy, aka Term Set-based – seriously, Microsoft???) columns in my list and make sense of them.
At the most basic level, this looks something like this:
request: {
method: "GET",
url: _spPageContextInfo.siteServerRelativeUrl +
"/_api/web/lists/getbytitle('ListName')/items?" +
"$select=ID,Title," +
"MyLocation,MyDepartment," +
"TaxCatchAll/ID,TaxCatchAll/Term," +
"&$expand=TaxCatchAll" +
"&$top=5000",
headers: {
"Accept": "application/json; odata=nometadata"
}
}
This “expands” the Managed Metadata columns, returning the text for the terms. But don’t get too excited too fast! We gain the term text, but lose something else (of course!). The data you get back looks something like this:
{
"Id": 5,
"ID": 5,
"Title": "Item Title",
"TaxCatchAll": [{
"ID": 5,
"Term": "Lab 2B"
}, {
"ID": 6,
"Term": "Histology"
}],
"MyDepartment": {
"Label": "6",
"TermGuid": "748ca38e-9cea-488f-9a9a-6a20f6dff80a",
"WssId": 6
},
"MyLocation": {
"Label": "5",
"TermGuid": "c8cb412b-ed99-4034-8ca7-5019940ec354",
"WssId": 5
},
}
So we have the text for the terms, but we basically lose the ability to know which is which. Or do we?
When we request the TaxCatchAll/ID property, we get the ID property which ties everything together, though you probably won’t find much documentation on this anywhere. In my example above, the value with ID=5 matches WssId=5 and so on.
I couldn’t find a post anywhere with code that tied all this together, so here we are. I’ve written a little function you can call to get the right values easily called getTaxonomyValue. You call it like this:
getTaxonomyValue(obj, "MyLocation")
…where:
- obj = the row of data (stored as an object in JSON),
- fieldName = the name of the column for which you want to grab the term text
Note that I make almost all my REST calls with odata=nometadata
these days to reduce the payloads as much as possible; I haven’t tested this with the other variants.
function getTaxonomyValue(obj, fieldName) {
// Iterate over the fields in the row of data
for (var field in obj) {
// If it's the field we're interested in....
if (obj.hasOwnProperty(field) && field === fieldName) {
if (obj[field] !== null) {
// ... get the WssId from the field ...
var thisId = obj[field].WssId;
// ... and loop through the TaxCatchAll data to find the matching Term
for (var i = 0; i < obj.TaxCatchAll.length; i++) {
if (obj.TaxCatchAll[i].ID === thisId) {
// Augment the fieldName object with the Term value
obj[field].Term = obj.TaxCatchAll[i].Term;
return obj[field];
}
}
}
}
}
// No luck, so return null
return null;
}
Calling this function matches the WssID and ID values within the object and returns the value you actually want.
References
- Workaround to filter on taxonomy columns using OData filters instead of CAML using REST in SharePoint by Mikael Svenson
- Can I use $expand to get Managed Metadata column values? at StackOverflow
Addendum
Wonder if this works in SharePoint 2013? Check out my follow up post Retrieving Multiple SharePoint Managed Metadata Columns via REST – SharePoint 2013.
Nice post.
I also $select different localized terms like TaxCatchAll/Term1033,TaxCatchAll/Term1044 for English and Norwegian, and then use
_spPageContextInfo.currentLanguage
to match user locale.here is Gist https://gist.github.com/maxali/8fc6a5a20cbc7cf787a62ff956d411ac
Nice!
Absolutely Brilliant….
In the offchance this is a double post, we had some weird network hiccup:
Marc, is this O365 only? We’re on premises in 2013 and I don’t appear to have to ask for the TaxonomyCatchAll column to see the term labels when I GET a list that has managed metadata columns. They come back as part of the return.
@Chris:
I don’t have an on premises SharePoint 2013 instance to test on, but I’m certain that the REST APIs are behind there. They also may be behind in SharePoint 2016. That’s the nature of SaaS: they can update it very frequently. I’m not sure when the TaxonomyCatchAll field showed up, unfortunately. I’ll see if I can find out…
M.
I really like this approach but now it turned out that this does not work if you are working with a subsite. I receive an error there because the lookup list is not in the same web.
Yup, I’m seeing the same – not working in a subsite. In the end I’m making two calls for the different fields I need – one to the main item and one with “/FieldValuesAsText” tacked on the end – this gives the raw default label for the term, which is what I needed
Can you please elaborate what you did to make it work on premise?
Hello Marc,
Was this process ever simplified in 2019 so that MMD fields could just simply be $select-ed and $expand-ed and used without additional processing?
@Jared:
I suppose it depends on where you’d like this to work. Check out the PnPjs library, which makes it much easier to work with Taxonomy (aka Term Store, aka Managed Metadata).
M.