sharepoint REST Services Get List Items with Lookup Columns

Example

Sometimes, you may have a list structure that looks like this:

Animal Listing Table

Name	Type	Description
Title	String (Text)	Name of the animal
Age	Number	How old the animal is
Value	Currency	Value of the animal
Type	Lookup (Animal Types Table)	Lookup Field (Gives dropdown of choices from Animal Types Table)

Animal Types Table

Name	Type	Description
Title	String (Text)	Name of the species/animal type (ex. Pig)
NumLegs	Number	Number of legs on the animal

Probably not the most serious example but the problem here is still valid. When you use the usual request in order to retrieve values from the SharePoint list, for the Type of the animal, you will only get back a field called TypeId in the JSON response. In order to expand these items out in just a single AJAX call, some extra markup is required in the URL parameters.

This example applies to more than just lookup columns too. When you are using People/Groups columns, they are essentially just lookups as well, so you can pull items such as Title, EMail, and others easily.

Example Code

Important Note: When you define the fields that you want to get back from the lookup columns, you must prefix the name of the field with the name of the lookup field in the original table. For example, if you want to get back the NumLegs attribute from the lookup column, you must type Type/NumLegs.

JavaScript

// webUrl: The url of the site (ex. https://www.contoso.com/sites/animals)
// listTitle: The name of the list you want to query
// selectFields: the specific fields you want to get back
// expandFields: the name of the fields that need to be pulled from lookup tables
// callback: the name of the callback function on success
function getItems(webUrl,listTitle,selectFields, expandFields, callback){
    var endpointUrl = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/items";
    endpointUrl+= '?$select=' + selectFields.join(",");
    endpointUrl+= '&$expand=' + expandFields.join(",");
    return executeRequest(endpointUrl,'GET', callback);
}

function executeRequest(url,method,callback,headers,payload) 
{
    if (typeof headers == 'undefined'){
        headers = {};
    }
    headers["Accept"] = "application/json;odata=verbose";
    if(method == "POST") {
        headers["X-RequestDigest"] = $("#__REQUESTDIGEST").val();
    }   

    var ajaxOptions = 
    {       
    url: url,   
    type: method,  
    contentType: "application/json;odata=verbose",
    headers: headers,
    success: function (data) { callback(data) }
    };
    if(method == "POST") {
    ajaxOptions.data = JSON.stringify(payload);
    }  

    return $.ajax(ajaxOptions);
}

// Setup the ajax request by setting all of the arguments to the getItems function
function getAnimals() {
    var url = "https://www.contoso.com/sites/animals";
    var listTitle = "AnimalListing";
    
    var selectFields = [
        "Title",
        "Age",
        "Value",
        "Type/Title",
        "Type/NumLegs"
    ];

    var expandFields = [
        "Type/Title",
        "Type/NumLegs"
    ];

    getItems(url, listTitle, selectFields, expandFields, processAnimals);
}

// Callback function
// data: returns the data given by SharePoint
function processAnimals(data) {
    console.log(data);
    // Process data here
}

// Start the entire process
getAnimals();