Your data model is analogous in levels to Opportunity - OpportunityLineItem - PricebookEntry - Product2
If you use SFDC Workbench REST Explorer with this query
select id,name,
(select id, quantity, priceBookEntry.unitPrice, pricebookEntry.product2.productCode
from OpportunityLineItems)
from Opportunity where id='xxx'
You'll see how SFDC will JSON serialize the results:
{ "totalSize" : 1, "done" : true, "records" :
[ { "attributes" : {
"type" : "Opportunity",
"url" : "/services/data/v33.0/sobjects/Opportunity/006J000000IDHEhIAP" },
"Id" : "006J000000IDHEhIAP",
"Name" : "MyOppoName",
"OpportunityLineItems" : {
"totalSize" : 1,
"done" : true,
"records" : [
{ "attributes" : {
"type" : "OpportunityLineItem",
"url" : "/services/data/v33.0/sobjects/OpportunityLineItem/00kJ0000008Tw5nIAC" },
"Quantity" : 1.0,
"PricebookEntry" : {
"attributes" : {
"type" : "PricebookEntry",
"url" : "/services/data/v33.0/sobjects/PricebookEntry/01uJ000000BUzNtIAL" },
"UnitPrice" : 0.0,
"Product2" : {
"attributes" : {
"type" : "Product2",
"url" : "/services/data/v33.0/sobjects/Product2/01tJ0000004HBbWIAW" },
"ProductCode" : "MyProductCode"
} } } ]
} }
]
}
If your target system for this JSON happens to also be SFDC, the structure is super convenient as it can be deserialized by the target system directly into sobjects.
However, if your target system has its own JSON structure, you are best of modelling that with inner classes as @susanoochidori suggests. You'll need to do the aforementioned SOQL in your APEX (changing to your custom objects from the OOB example I used) and then, map with assignment statements and inner class instanatiation the values returned from SOQL. Finally, serialize OuterWrapper
.
Put another way, you may need to work backwards from the JSON signature expected by the target system into a corresponding APEX outer with inner classes object model
Best Answer
As part of the January 2016 release SFMC added a "nested" datasource type that can be used to extract nested data objects as you have done here with SSJS. The syntax is a little clunky as it requires you to create a new datasource for each layer of the JSON data that you access. Here is an example of how to use GTL to extract the data from your sample:
And yields the following output: