SharePoint REST API: Selecting, Filtering, Sorting Results in a SharePoint List

In this post we will see samples about $select$orderby$filter and $expand.


1.Simple API call:
{siteUrl}/_api/web/lists/getbytitle('ListName')/items


2.Select Columns:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee


3.OrderBy Columns:
  • Ascending order: {siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee asc
  • Descending order: {siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee desc

4.Filtering Columns:
  • Filtering by Title:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= Employee eq ‘parth' 
  • Filtering by ID:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= ID eq 2
  • Filtering by Date: {siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=Start_x0020_Date le datetime'2016-03-26T09:59:32Z'
  • Title name starts with the letter P:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=startswith(Title,‘P’)
  • Return all items from the "Employees" modified in May:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=month(Modified) eq 5

  • You can also use multiple filters using the and operator to combine them, and wrapping each filter condition in a parenthesis:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=(Modified le datetime'2016-03-26T09:59:32Z') and (ID eq 2)

5.Expanding for a person or lookup fields:

   To get values from Person or Lookup fields, we can use Expand to retrieve values.

  • Lookup field:  City lookup column : {siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee,company,city/Title&$expand= city/Id
  • People field: People field Author:{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=Author/Title&$expand=Author/Id

  •  Lookup Column other properties:(Lookup properties will give parent list columns values)
  •  /_api/lists/getbytitle('ListName')/items?select=Title,LookupField/Id,LookupField/Title,LookupField/Modified,
  • LookupField/Created&$expand=LookupField/Id
  • People Column other properties:
    • "1 Id 2 ContentTypeID 3 ContentType 4 Name 5 Modified 6 Created 7 Account 8 EMail 9 MobileNumber 10 AboutMe 11 SIPAddress 12 IsSiteAdmin 13 Deleted 14 Hidden 15 Picture 16 Department 17 JobTitle 18 LastName 19 FirstName"
      /items?$select=Users/EMail,Users/FirstName,Users/LastName,Users/EMail&$expand=Users/Id

5.Expanding Metadata fields:(link)

    We can use "TaxCatchAll" keyword to expand metadata column. here Location & department is metadata columns.
url: _spPageContextInfo.siteServerRelativeUrl +
      "/_api/web/lists/getbytitle('ListName')/items?" +
      "$select=ID,Title," +
      "MyLocation,MyDepartment," +
      "TaxCatchAll/ID,TaxCatchAll/Term," +
      "&$expand=TaxCatchAll" +
      "&$top=5000",

Comments

Popular posts from this blog

Upload Single/Multiple file by using the REST API and jQuery SharePoint 2013

Add content type to SharePoint List/Library using REST API

A type named 'SP.Data. could not be resolved by the model error