External API select data from expand Odata V2

[axel_semling]

Hi colleagues,

we have a JAVA application, which bases on Cloud Application Programming Model (CAP). In addition we have integrated SAP Cloud SKD to CAP. We have done this in order to replicated data from Sales Cloud system to our JAVA application.

Requirement:

We want to replicate Accounts with its Addresses from Sales Cloud System to our JAVA application. Meaning we do a Get operation in order to get accounts and use an expand to get assigned addresses. For the expand of addresses we want to use a select as we need only some of the address attributes.

The OData V2 request looks like this:

Get {{url}}/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection?$expand=CorporateAccountAddress&$top=100&$select=Name,IndustrialSectorCode,LifeCycleStatusCode,RoleCode,CorporateAccountAddress/MainIndicator,CorporateAccountAddress/CountryCode,CorporateAccountAddress/City,CorporateAccountAddress/HouseNumber,CorporateAccountAddress/Street

And this request works also as expected. Meaning the expand entity CorporateAccountAddress gets back only the mentioned attributes of the select, for instance CorporateAccountAddress/CountryCode.

When I try to do the same Get operation with a CQN query with a select in the expand part then I get back all attributes of CorporateAccountAddress.

The query look like this in JAVA

private CqnSelect queryAccountCollectionWithAddressesSinceChangedOnOrderedByChangedOn(Instant changedOn, long skipValue, long limit) {
        return Select.from(C4codataapi_.CORPORATE_ACCOUNT_COLLECTION)
                .columns(
                        b -> b.get(CorporateAccountCollection.NAME),
                        b -> b.get(CorporateAccountCollection.ACCOUNT_ID),
                        b -> b.get(CorporateAccountCollection.ROLE_CODE),
                        b -> b.get(CorporateAccountCollection.CREATION_ON),
                        b -> b.get(CorporateAccountCollection.CHANGED_ON),
                        b -> b.get(CorporateAccountCollection.CORPORATE_ACCOUNT_ADDRESS),
                        b -> b.get(CorporateAccountAddressCollection.CITY),
                        b -> b.CorporateAccountAddress().expand(
                                a -> a.get(CorporateAccountAddressCollection.CITY),
                                a -> a.get(CorporateAccountAddressCollection.COUNTRY_CODE)
                        )
                )
                .where(o -> o.ChangedOn().ge(changedOn))
                .orderBy(ContactCollection.CHANGED_ON)
                .limit(limit, skipValue);
 }

The expectation is that I get back from the entitiy CorporateAccountAddress only the attributes City and Country Code. But in fact I get back all attributes.

Please can you give advice how to declare the CQN query in a correct way that only some attributes of CorporateAccountAddressCollection are returned.

Thanks in advance.

Best Regards, Axel

<cds.services.version>1.29.0

[marcbecker · Accepted solution]

You can set the logger "com.sap.cds.remote.odata" to "DEBUG", as described here: https://cap.cloud.sap/docs/java/observability#logging-configuration

This will show the OData URL that is built based on the CQN query. Maybe this gives a hint what is going wrong.