{"id":4624,"date":"2020-05-19T07:55:46","date_gmt":"2020-05-19T14:55:46","guid":{"rendered":"https:\/\/officedevblogs.wpengine.com\/?p=4624"},"modified":"2020-05-19T07:55:46","modified_gmt":"2020-05-19T14:55:46","slug":"build-advanced-queries-with-count-filter-search-and-orderby","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/build-advanced-queries-with-count-filter-search-and-orderby\/","title":{"rendered":"Build advanced queries in Microsoft Graph with $count, $filter, $search, and $orderby"},"content":{"rendered":"

UPDATE 9\/22: read the General Availability announcement<\/a>.<\/strong><\/p>\n

Some of the top asks we have received on Azure Active Directory were for better sorting, counting, and filtering capabilities. We are excited to announce that we are now providing these capabilities on Azure Active Directory objects to developers through Microsoft Graph! In addition to addressing these important requests, we\u2019ve added the ability to provide keyword-based searches.<\/p>\n

In the past, if you\u2019ve tried to filter and sort users in the same request, using a query like this:<\/p>\n

GET<\/a> https:\/\/graph.microsoft.com\/v1.0\/users?$filter=startswith(displayName, 'al')&$orderby=displayName&$select=id,displayName<\/pre>\n
You might have seen the following response:<\/p>\n
\"error\"<\/span>: {\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"code\"<\/span>: \"Request_UnsupportedQuery\"<\/span>,\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"message\"<\/span>: \"Sorting not supported for current query.\"<\/span>\n}<\/pre>\n
Now, this will result in a sorted list of users, with a new @odata.count<\/code> property:<\/p>\n
\"@odata.context\"<\/span>: \"https:\/\/graph.microsoft.com\/beta\/$metadata#users(id,displayName)\"<\/span>,\n\"@odata.count\"<\/span>: 2<\/span>,\n\"value\"<\/span>: [\n   {\n      \"id\"<\/span>: \"4782e723-f4f4-4af3-a76e-25e3bab0d896\"<\/span>,\n      \"displayName\"<\/span>: \"Alex Wilber\"<\/span>\n   },\n   {\n      \"id\"<\/span>: \"c03e6eaa-b6ab-46d7-905b-73ec7ea1f755\"<\/span>,\n      \"displayName\"<\/span>: \"Allan Deyoung\"<\/span>\n   }\n]<\/pre>\n
To achieve this, you need to:<\/strong><\/p>\n
    \n
  1. Select the beta endpoint<\/li>\n
  2. Add $count=true<\/code> in the QueryString<\/li>\n
  3. Add ConsistencyLevel = eventual<\/code> to the Request headers<\/li>\n<\/ol>\n
    \"graph<\/p>\n
    Supported Objects<\/h3>\n
    In the table below there are all the objects and related links that currently have the new advanced querying capabilities.<\/p>\n\n\n\n\n\n\n\n\n\n\n
    Object<\/b><\/td>\nLinks<\/b><\/td>\n<\/tr>\n
    Users<\/td>\nMember Of, Transitive Member Of, Owned Objects, Registered Devices,\nOwned Devices, Direct Reports<\/td>\n<\/tr>\n
    Groups<\/td>\nMembers, Transitive Members, Owners<\/td>\n<\/tr>\n
    Applications<\/td>\nMember Of, Transitive Member Of<\/td>\n<\/tr>\n
    Service Principals<\/td>\nMember Of, Transitive Member Of<\/td>\n<\/tr>\n
    Devices<\/td>\nMember Of, Transitive Member Of, Registered Users, Registered Owners<\/td>\n<\/tr>\n
    Org Contacts<\/td>\nMember Of, Transitive Member Of, Registered Users, Registered Owners<\/td>\n<\/tr>\n
    Administrative Units<\/td>\nMembers<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    What is eventual consistency?<\/h3>\n
    Azure Active Directory stores multiple copies of data to handle large read volume and provide high availability. When data is updated, the change will eventually be applied to all the copies.\n$search<\/code> and $count<\/code> queries require the client to opt-in for eventual consistency by setting the header ConsistencyLevel = eventual<\/code>.\nFor example, this means that when you add a user, you need to wait for all the copies to be updated to search or count them.<\/p>\n
    Sample Queries<\/h3>\n
    Count the number<\/a> of users, groups, applications, contacts, devices, service principals in a tenant, answering questions like:<\/p>\n
    \u25fe How many users are in the tenant?<\/p>\n
    GET<\/a> https:\/\/graph.microsoft.com\/beta\/users\/$count<\/pre>\n
    \u25fe How many guest users are in the tenant, and who are they? Note<\/strong>: $count<\/code> can also be a QueryString parameter.<\/em><\/p>\n
    GET<\/a> https:\/\/graph.microsoft.com\/beta\/users\/?$count=true&$filter=userType eq \u2018Guest\u2019<\/pre>\n
    \u25fe How many members are in the engineering group (including transitive and direct members of a group)?<\/p>\n
    GET<\/a> https:\/\/graph.microsoft.com\/beta\/groups\/{group-id}\/transitiveMembers\/$count<\/pre>\n
    Note<\/strong>: A group can contain nested groups, so we are using the\u00a0transitiveMembers\u00a0linked property to include all the nested groups\u2019 members.<\/em><\/p>\n
    Search terms using tokenization<\/a>. We are using a tokenization approach for our searches, detailed as follows:<\/p>\n