Nested CDS view / oData result

[Former Member]

I am trying to merge two tables (by ID) and return the data as a nested json object via oData, but got no luck so far. Since I couldn't find any useful resources, I am starting to wonder if it's even possible to do.
I'm using Eclipse Oxygen to code the CDS-Views, and already tried different SQL joins, but they only generate multiple sets of data, instead of one nested object. I attached images with some sample data I made in Exel, so you can understand my problem a bit better.

Example of what I am trying to achieve:
First Table (CustomerDetails):

Second Table (CustomerMails):

Expected result (as JSON):

I only added the first 3 entries into the JSON, but you get what I mean.

JSON Overview:

The actual result looks something like this (same ID multiple times):

Code of my CDS-View (unrelated to the data above):

define view ZXZTI_CUSTOMER as select from kna1 as k
left outer join adr6 as a on k.adrnr = a.addrnumber
{
    @Semantics.user.id: true
    key k.kunnr as CustomerNumber,
    @Semantics.name.fullName: true
    k.name1 as CustomerName,
    @Semantics.address.country: true
    k.land1 as CustomerCountry,
    @Semantics.address.zipCode: true
    k.pstlz as CustomerZipcode,
    @Semantics.address.city: true
    k.ort01 as CustomerCity,
    @Semantics.address.street: true
    k.stras as CustomerStreet,
    @Semantics.eMail.address: true
    a.smtp_addr as CustomerEMail
}

Thanks in advance!

[Former Member]

Hi

I too face the exactly the same problem. i am trying to develop CDS view for Profit center lookup. I want all the company details to be attached to single profit center with out all the data get repeated . Did you get any breakthrough on the above problem that you have mentioned?.

any help will be highly appriciated

Regards

Ibrahim.

[Former Member]

No, I couldn't solve the problem so far. Still looking for an useful solution. I made some workarounds, but they aren't the way you should do it. (For example getting all results, and filtering them with JS clientside)

I would appreciate any help on this topic too.

[former_member185414]

may be try using Union clause