Nested expansion using the $expand query option via the Web API by example

Views (11588)

Sudosandwich

Like(2)

Report

A question that I keep getting is, "Can I perform nested queries using $expand query option with the Dynamics 365 Web API?". The answer is, yes this is possible without the need to leverage fetch XML. Unfortunately, a quick internet search at the time of writing this blog might lead you to believe it is not possible. In this short post I will show you how by example.

The following example queries an account record, expanding the primary contact lookup, and on the primary contact expands the created by lookup. Each level has a select statement to demonstrate how it works at the different layers. For reference, I am using Postman to perform my tests.

Request:

GET [Organization URI]/api/data/v9.1/accounts(85268bf8-448a-ea11-a812-000d3a579cc6)?$select=name&$expand=primarycontactid($select=fullname;$expand=createdby($select=fullname)) HTTP/1.1

[EDIT] *Note that you do not need to included the navigation property you wish to expand in they query.

Response:

{
    "@odata.context": "https://admissions-dev.crm.dynamics.com/api/data/v8.2/$metadata#accounts(primarycontactid,primarycontactid(fullname,createdby(fullname)))/$entity",
    "@odata.etag": "W/\"2082428\"",
    "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
    "primarycontactid": {
        "@odata.etag": "W/\"2081664\"",
        "fullname": "Yvonne McKay (sample)",
        "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
        "createdby": {
            "fullname": "Paul Breuler",
            "systemuserid": "17541fec-8178-4196-84cc-240a576e308c",
            "ownerid": "17541fec-8178-4196-84cc-240a576e308c"
        }
    }
}

You can nest up to 11 expand commands. If you want to see what the beast of a string looks like check out the following request used to test. For this example, I leveraged the circular reference between accounts and contacts.

Request:

GET [Organization URI]/api/data/v9.1/accounts(85268bf8-448a-ea11-a812-000d3a579cc6)?$select=name&$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname))))))))))) HTTP/1.1

Response

{
    "@odata.context": "https://admissions-dev.crm.dynamics.com/api/data/v8.2/$metadata#accounts(name,primarycontactid(fullname,parentcustomerid_account(name,primarycontactid(fullname,parentcustomerid_account(name,primarycontactid(fullname,parentcustomerid_account(name,primarycontactid(fullname,parentcustomerid_account(name,primarycontactid(fullname,parentcustomerid_account(name,primarycontactid(fullname))))))))))))/$entity",
    "@odata.etag": "W/\"2082428\"",
    "name": "Fourth Coffee (sample)",
    "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
    "primarycontactid": {
        "@odata.etag": "W/\"2081664\"",
        "fullname": "Yvonne McKay (sample)",
        "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
        "parentcustomerid_account": {
            "name": "Fourth Coffee (sample)",
            "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
            "primarycontactid": {
                "fullname": "Yvonne McKay (sample)",
                "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
                "parentcustomerid_account": {
                    "name": "Fourth Coffee (sample)",
                    "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
                    "primarycontactid": {
                        "fullname": "Yvonne McKay (sample)",
                        "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
                        "parentcustomerid_account": {
                            "name": "Fourth Coffee (sample)",
                            "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
                            "primarycontactid": {
                                "fullname": "Yvonne McKay (sample)",
                                "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
                                "parentcustomerid_account": {
                                    "name": "Fourth Coffee (sample)",
                                    "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
                                    "primarycontactid": {
                                        "fullname": "Yvonne McKay (sample)",
                                        "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6",
                                        "parentcustomerid_account": {
                                            "name": "Fourth Coffee (sample)",
                                            "accountid": "85268bf8-448a-ea11-a812-000d3a579cc6",
                                            "primarycontactid": {
                                                "fullname": "Yvonne McKay (sample)",
                                                "contactid": "32278bf8-448a-ea11-a812-000d3a579cc6"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Thanks for reading, hopefully you found this helpful.

Comments

*This post is locked for comments

Amit.Jain
Posted at

Hi Sudosandwich , Any idea, if you know why its not working when referencing to itself. Like Account is linked to another account. It does not expand second level. {{webapiurl}}/accounts(b6a3e1c9-c5fb-ed11-8848-000d3a3378b5)?$select=name,_createdby_value&$expand=account_parent_account($select=name;$expand=account_parent_account($select=name)) OR {{webapiurl}}/accounts(b6a3e1c9-c5fb-ed11-8848-000d3a3378b5)?$select=name,_createdby_value&$expand=createdby($select=fullname),account_parent_account($select=name;$expand=createdby($select=fullname))

Like (0)

Report
Community Member
Posted at

The Common Data Service (current environment) connector (which is highly likely to be renamed soon) Dataflex (Common Data Service current) connector has Expand Query parameter for Get a record and List records actions which can be used to retrieve the

Like (0)

Report
Community Member
Posted at

Want to get newsletter to your mailbox? Subscribe here Don’t miss the opportunity to pick new skills or brush up old ones; this Saturday Powerthon 365 Saturday is hosting Power Platform ...

Like (0)

Report
ChrisGroh
Posted at

@khaack - "on-premise" would be Dynamics 365 hosted on your company's server or a third party hosting providers service. "Online" refers to Microsoft hosted where your organizations domain ends with ".dynamics.com". The organization version I had nested expands working is 9.1.0.17461.

Like (0)

Report
khaack
Posted at

@ChrisGroh - I'm not sure what you mean by online vs on-premise, could you expand?

Like (0)

Report
ChrisGroh
Posted at

@khaack - This works for me online but not on-premise. Maybe that's what you're running into.

Like (0)

Report
Linn Zaw Win 3,407
Posted at

It's interesting that we can nest up to 11 expand commands if we Retrieve the record with GUID but we can only nest up to 10 expand commands if we change the query to RetrieveMultiple style with a filter condition. SAMPLE RETRIEVEMULTIPLE QUERY WITH 11 EXPAND ##BEGIN## /accounts?$filter=accountid eq 85268bf8-448a-ea11-a812-000d3a579cc6&$select=name&$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname;$expand=parentcustomerid_account($select=name;$expand=primarycontactid($select=fullname))))))))))) ##END##

Like (0)

Report
khaack
Posted at

Ohhh, gotcha, thank you! I have one more question if you don't mind. I have an entity, with a navigation property to a collection, which I can do an expansion on and it works, however if I try to nest another expand within the entity of the collection type, it fails with the error: "Term '($select=xx_propertyName;expand=xx_navigationProperty)' is not valid in a $select or $expand expression.",

Like (0)

Report
Sudosandwich 42
Posted at

The web API does not appear to like navigation properties included in the nested select query. Note that you do not need to select the property you want to expand. You can expand any property and exclude it from the select. BAD QUERY ##BEGIN## /accounts(85268bf8-448a-ea11-a812-000d3a579cc6)?$select=name&$expand=primarycontactid($select=fullname,createdby;$expand=createdby($select=fullname)) ##END## | GOOD QUERY ##BEGIN## /accounts(85268bf8-448a-ea11-a812-000d3a579cc6)?$select=name&$expand=primarycontactid($select=fullname;$expand=createdby($select=fullname)) ##END## Note in the succeeding example I do not select the navigation property in either case. Sorry for the terrible format... comments don't have proper formatting it seems.

Like (0)

Report
khaack
Posted at

Hi there, thanks for the post. When I try to do this, I get the error "The navigation properties are not supported with $select clause". Any ideas what would cause that?

Like (0)

Report

Community site session details

Nested expansion using the $expand query option via the Web API by example

Comments

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!