Query Data using the Web API

It took me some time to get my first Web API query works. So I am thinking to share some basic information to help anyone writing their first query.

Basic Structure of the Web API query

*The entity set name must be plural. In this example, the entity name is new_people, the entity set name used is new_peoples

*The first system query option is appended after [?] and subsequent query options are separated using [&].


Example Query

1) Query new_people entity, returning all records and all entity properties.

https://serverurl/api/data/v8.2/new_peoples

2) Query new_people entity, returning 2 properties

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    },{
      "@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001914\"","new_jobtitle":"Sales Manager","new_name":"Sales 2","new_peopleid":"4b59221f-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001910\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 1","new_peopleid":"84c0c4f2-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001911\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 2","new_peopleid":"ec37fcfc-5404-e811-8128-e0071b6a92a1"
    }
  ]
}

3) Query new_people entity with a primary key and returning 2 properties

https://serverurl/api/data/v8.2/new_peoples(4f696911-5104-e811-8128-e0071b6a92a1)?$select=new_jobtitle,new_name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)/$entity","@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
}

4)  Query new_people entity, returning 2 properties with sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name$orderby=
new_jobtitle asc,new_name desc


Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    },{
      "@odata.etag":"W/\"30001911\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 2","new_peopleid":"ec37fcfc-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001910\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 1","new_peopleid":"84c0c4f2-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001914\"","new_jobtitle":"Sales Manager","new_name":"Sales 2","new_peopleid":"4b59221f-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

5)  Query new_people entity, returning top 3 records with sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$top=3&$orderby=
new_jobtitle asc,new_name desc


Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    }
  ]
}

6)  Query new_people entity, returning 2 properties with filter and sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$filter=new_jobtitle eq 'Global Sales Executive'&$orderby=
new_jobtitle asc,new_name desc

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

7)  Query new_people entity, returning 2 properties with filters

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$filter=new_jobtitle eq 'Global Sales Executive' and new_name eq 'Global Sales 2'

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

8)  Query new_people entity, returning lookup properties

https://serverurl/api/data/v8.2/new_peoples(4f696911-5104-e811-8128-e0071b6a92a1)?$select=new_jobtitle,new_name&$expand=new_Group($select=new_name)

*new_Group is the lookup field schema name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name,new_Group,new_Group(new_name))/$entity","@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1","new_Group":{
    "@odata.etag":"W/\"29928575\"","new_name":"Sales","new_peoplegroupid":"a11ef467-5004-e811-8128-e0071b6a92a1"
  }
}