Fusion CX - How to query parent object with search criteria of child/grandchild attributes

Hi,

In this post, I will discuss how to query records of parent object based on the search conditions of child object attributes.

For example, 

I have this data structure -

Subscription (Parent)

--Subscription Product (Child)

----Covered Level (Grand Child)

If I have a use case to retrieve all subscriptions whose covered level is Product of 123, here is the end point I can use (note the highlighted q string) -

https://URL/crmRestApi/resources/11.13.18.05/subscriptions?totalResults=true&limit=10&onlyData=true&q=products.coveredLevels.ProductName='123'&fields=products:ProductName,SubscriptionNumber

If I have a use case to retrieve all subscriptions for Customer ID 100 whose covered level is Product of ID 200, here is the end point I can use (note the highlighted q string) -

https://URL/crmRestApi/resources/11.13.18.05/subscriptions?totalResults=true&limit=10&onlyData=true&q=products.coveredLevels.InventoryItemId='200' AND PrimaryPartyId=100&fields=products:ProductName,SubscriptionNumber

Please don't forget to set REST-Framework-Version to 2 or higher.

Hope you find this post helpful, and stay healthy.

Thanks,

Adam Liu

Reference Document -

https://docs.oracle.com/en/middleware/developer-tools/adf/12.2.1.4/develop/creating-adf-restful-web-services-application-modules.html#GUID-6EB54E46-4553-4477-BBCA-48755B2328CB