E9.2 Parsing connector response

[KMack64]

I have created a connector for an external rest call, and I am having issues parsing the return values.

The data returns correctly up to the highlighted:

This is the raw return:

ConnectorRequest1": {
"quoteSummaries": [
{
"quoteId": 2215455953,
"carrier": {
"carrierCode": "T8154",
"carrierName": "Saia Motor Freight Line",
"scac": "SAIA"
},
"customer": {},
"totalCharge": 337.95,
"totalFreightCharge": 291.08,
"totalAccessorialCharge": 46.86,
"transit": {
"minimumTransitDays": 3,
"originService": "Direct",
"destinationService": "Direct"
},
"rates": [
{
"rateId": 390089714,
"totalRate": 46.86,
"unitRate": 46.86,
"quantity": 291.0839,
"rateCode": "405",
"rateCodeValue": "Fuel Surcharge",
"currencyCode": "USD",
"isOptional": false
},
],
"transportModeType": "LTL",
"equipmentType": "Van",
"cargoLiability": {
"perPound": 5,
"max": 50000,
"amount": 5415,
"currencyCode": "USD"
},
"quoteSource": "Contractual"
}
]
},
Here is what I am getting:

"ConnectorRequest1": {
"quoteSummaries": [
{
"quoteId": 2215445778,
"carrier.carrierCode": "T8154",
"carrier.carrierName": "Saia Motor Freight Line",
"carrier.scac": "SAIA",
"totalCharge": 337.95,
"totalFreightCharge": 291.08,
"totalAccessorialCharge": 46.86,
"transit.minimumTransitDays": 3,
"transit.originService": "Direct",
"transit.destinationService": "Direct",
"transportModeType": "LTL",
"equipmentType": "Van"
}
]
},


I have noticed that the rates section is enclosed by [...]

Any ideas on how to return the rates section?

 

[DaveWagoner]

Rates is within an array (good catch noticing the square brackets, in json that means array), even if there's only 1 per return. So you need to address the first element of that array to get at the fields within. Zero-based array if memory serves.

try rates[0].rateId

 

[KMack64]

Rates is within an array (good catch noticing the square brackets, in json that means array), even if there's only 1 per return. So you need to address the first element of that array to get at the fields within. Zero-based array if memory serves.

try rates[0].rateId

Very good. That returned the first record in the rates array. Unfortunately for each quoteId, there are 30 corresponding rateId's. Do I need to designate each one: rateId[0], rateId[1]...rateId[30], or is there a better way?

 

[DaveWagoner]

There's a better way! You need to take the entire response from the connector and "piece it out". I noticed that your response BEGINS with an array as well-- is it possible for the response to have multiple parent-child recordsets within a single response?

Regardless my advice is to "return raw data" in the transformations screen on the rest component, then use a scripting step next to parse the JSON and make sense of it. This would replace completely your variable mapping.

There is some serious nuance in parsing the JSON that is probably a bit too deep for my self-imposed ~5 minute limit on researching answers in this forum. I would start up a conversation with ChatGPT, feed it a good representative example of JSON, and ask it to parse the JSON out so that you have your header fields as var and then your detail fields as array, which you can map explicitly in your scripting component output so that the fields and iterate are available downstream.

It's a big chunk to bite off if you're just getting started! I'd recommend spending some $ on consultant if you spin your wheels too much.

 

[KMack64]

There's a better way! You need to take the entire response from the connector and "piece it out". I noticed that your response BEGINS with an array as well-- is it possible for the response to have multiple parent-child recordsets within a single response?

Regardless my advice is to "return raw data" in the transformations screen on the rest component, then use a scripting step next to parse the JSON and make sense of it. This would replace completely your variable mapping.

There is some serious nuance in parsing the JSON that is probably a bit too deep for my self-imposed ~5 minute limit on researching answers in this forum. I would start up a conversation with ChatGPT, feed it a good representative example of JSON, and ask it to parse the JSON out so that you have your header fields as var and then your detail fields as array, which you can map explicitly in your scripting component output so that the fields and iterate are available downstream.

It's a big chunk to bite off if you're just getting started! I'd recommend spending some $ on consultant if you spin your wheels too much.

Thanks for the help!

 

[KMack64]

The entire raw output is attached
The tricky part is the "rates" array which will return a variable number of rates depending on the carrier. Fun stuff.

 

[DaveWagoner]

Yup so you have 1-n rates inside of 1-n quotes.

Lots of fun to be had here! If I free up some time later I'll play around, but don't count on me just in case I don't free up!

 

[Kevin Long]

It is a little tough to say exactly how I would parse this without knowing exactly what you wanted to do with the result. Essentially, you have an array of quote objects. Each quote object has a number of values, a carrier object, a customer object (appears to be empty), a transit object, and a rates array. If I were loading the quotes into JDE, then I would configure the output of the connector to be an array called quoteSummaries and a Named Object called quote.


Then I would create a second orchestration with inputs that matched the quote object. You can then iterate over the quoteSummaries array, and pass in each quote object into jde__object. That will parse the quote object for you. You will need a custom object to break out the carrier and transit objects (in your inputs these would be type Object), but the rest should map directly. Then you could have a form request to load the quote into JDE.