In my, previous post, I had demonstrated how to get List Items using SharePoint REST API. In this post, we will see how to retrieve the following fields:

• Lookup
• Managed Metadata
• Person or Group

These columns are special ones. They don’t contain just one string value but an array of values. For ex, a lookup field contains both the unique id as well as the text value. So if we query them using $select only then, we only get the unique id but not the text value. In some cases this might be desired but, here we will see how to fetch there actual values using, $expand.

In this example, I’ll be requesting the following fields from a SharePoint Task List,

• Predecessors – Lookup
• AssignedTo – Person or Group
• DueDate – Date
• Blog State – Managed Metadata

Add-In

Following is the code snippet to fetch these field types using SharePoint REST API:

PK.prototype.items = function () {
    var hostWebUrl = decodeURIComponent(PK.prototype.getQueryStringParameter('SPHostUrl'));
    var appweburl = decodeURIComponent(PK.prototype.getQueryStringParameter('SPAppWebUrl'));
    var executor = new SP.RequestExecutor(appweburl);

    executor.executeAsync({
        url: appweburl + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'9DED7E87-7BDD-4845-A849-E6C0A67EA635')/items?$select=AssignedTo/ID,AssignedTo/FirstName,AssignedTo/LastName,DueDate,Predecessors/Title,Blog_x0020_State/Term&$expand=AssignedTo,Predecessors&@target='" + hostWebUrl + "'",
        method: "GET",
        scope: this,
        headers: { "Accept": "application/json; odata=verbose" },
        success: PK.prototype.successHandlerItems,
        error: PK.prototype.errorHandlerItems
    });
}

Output

{
	"d": {
		"results": [{
				"__metadata": {
					"id": "2d69bcd1-7599-4803-8549-38fe28096b4d",
					"uri": "https://collabion-cdc8ba69ab46a9.sharepoint.com/PkTeamSite-Sub2/SP-ExpandListItemData-Piyush/_api/Web/Lists(guid'9ded7e87-7bdd-4845-a849-e6c0a67ea635')/Items(3)",
					"etag": "\"2\"",
					"type": "SP.Data.TaskListItem"
				},
				"Predecessors": {
					"results": [{
							"__metadata": {
								"id": "d8c45eec-914e-41ae-853a-e474dbdff90f",
								"type": "SP.Data.TaskListItem"
							},
							"Title": "Create first draft"
						}
					]
				},
				"AssignedTo": {
					"results": [{
							"__metadata": {
								"id": "ceffb7f3-d4cf-4fed-bd6c-938f9edc9bcc",
								"type": "SP.Data.UserInfoItem"
							},
							"ID": 15,
							"FirstName": "Piyush",
							"LastName": "Singh"
						}
					]
				},
				"DueDate": "2017-08-02T07:00:00Z",
				"Blog_x0020_State": {
					"__metadata": {
						"type": "Collection(SP.Taxonomy.TaxonomyFieldValue)"
					},
					"results": [{
							"Label": "Review",
							"TermGuid": "644e1442-3357-46da-8015-b5bce6a61009",
							"WssId": 5
						}, {
							"Label": "Submit",
							"TermGuid": "b5cae2dc-1d03-44dc-86cf-0359fa813a01",
							"WssId": 6
						}
					]
				}
			}
		]
	}
}

The above output after some parsing and styling will look like the following:-

Plz note that out of the three types mentioned above, Managed Metadata does not require expanding. When I tried to expand it for my field, Blog_x0020_State, I received the following Microsoft.SharePoint.SPException,

The field or property ‘Blog_x0020_State’ does not exist.

JSOM

Even though this post was supposed to be for expanding columns via REST API only, I thought it would make the package complete, if I also post the alternate way of doing it by JSOM!

For JSOM, you don’t need any special treatment to fetch the data. It can be fetched by our standard CAML query. However, the part which is different is the one where, we need to parse the above mentioned types from the result of a JSOM query.

var ctx = new SP.ClientContext('/PkTeamSite-Sub2');
var oList = ctx.get_web().get_lists().getByTitle('BlogTask-Piyush');

var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml("<View><Query/><ViewFields><FieldRef Name=\"DueDate\" /><FieldRef Name=\"AssignedTo\" /><FieldRef Name=\"Blog_x0020_State\" /><FieldRef Name=\"Predecessors\" /></ViewFields></View>");
this.collListItem = oList.getItems(camlQuery);

ctx.load(this.collListItem);

ctx.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

In the above code snippet, I am again fetching the same List data, this time, using CAML query. Once, we get the result from the above request, this is how we need to parse all the special columns,

var listItemEnumerator = this.collListItem.getEnumerator();

while (listItemEnumerator.moveNext()) {

	var oListItem = listItemEnumerator.get_current();

	/* Lookup Fields
	* They are always returned in array. */
	oListItem.get_item('Predecessors')[0].get_lookupValue();
	
	/* Person or Group
	* They are always returned in array. */
	oListItem.get_item('AssignedTo')[0].get_lookupValue();
	
	/* Managed Metadata
	* Here the array of items are included inside the _Child_Items_ prop */
	oListItem.get_item('Blog_x0020_State')._Child_Items_[0]['Label'];
	
	/* Date, Text, or Number */
	oListItem.get_item('DueDate');
	
}

Key Takeaways

• Managed Metatdata field does not require expanding.
• Each of these special properties, in turn, contains an additional results field which, contains the array of values.
• For JSOM, you have to use, get_lookupValue() for Lookup and People or Group field. For, Managed Metadata, use the _Child_Items_ properties.
• The entire source code is available on GitHub. Plz note that the solution is that of a SharePoint-hosted add-in. So, only the REST example will work directly, and not the JSOM.