Formatting JSON data according to given scenarios

Hi,

I need to know the ways of performing the restructuring of the JSON payload based on the requirement.for ex. quiz_id value we need to distribute to each type of question.
below are input and expected output need to achieve through snaplogic pipeline

input json
{
“quiz”: {
“quiz_id” : 123456789,
“sport”: {
“q1”: {
“question”: “Which one is correct team name in NBA?”,
“options”: [
“New York Bulls”,
“Los Angeles Kings”,
“Golden State Warriros”,
“Huston Rocket”
],
“answer”: “Huston Rocket”
}
},
“maths”: {
“q1”: {
“question”: “5 + 7 = ?”,
“options”: [
“10”,
“11”,
“12”,
“13”
],
“answer”: “12”
},
“q2”: {
“question”: “12 - 8 = ?”,
“options”: [
“1”,
“2”,
“3”,
“4”
],
“answer”: “4”
}
}
}
}

Output Json

{
“quiz”: {
“sport”: {
“q1”: {
“question”: “Which one is correct team name in NBA?”,
“options”: [
“New York Bulls”,
“Los Angeles Kings”,
“Golden State Warriros”,
“Huston Rocket”
],
“answer”: “Huston Rocket”,
“quiz_id” : 123456789
}
},
“maths”: {
“q1”: {
“question”: “5 + 7 = ?”,
“options”: [
“10”,
“11”,
“12”,
“13”
],
“answer”: “12”,
“quiz_id” : 123456789
},
“q2”: {
“question”: “12 - 8 = ?”,
“options”: [
“1”,
“2”,
“3”,
“4”
],
“answer”: “4”,
“quiz_id” : 123456789
}
}
}
}

I think you can do this with a Mapper since the “Target Path” is a JSON-Path. Using a path with some wildcards (*), you can spread the quiz_id value across all the questions. Here’s a suggested Mapper configuration:

The first row copies the quiz_id to all of the categories (e.g. “sports”) and then the questions (e.g. “q1” and “q2”). Since I set the Mapper to pass through the input doc, the second row deletes quiz_id from the input.

Community7038_2020_03_09.slp (3.8 KB)

Thanks a lot @tstack it worked great.

below is the actual case which I wanted to implement using this logic but it doesn’t seem to be working

Scenario to be implemented:
with reference to JSON payload (refer attached pipeline), the item array has field “lineItemId”, this field i need to be mapped inside AccountingCodingBlockAssignment below ProductRecipientPartyInternalID field.
here if for same lineItemId, there are more then one objects under AccountingCodingBlockAssignment, then it should map respective the “lineItemId” below each objects ProductRecipientPartyInternalID field.

i have kept one expected json snap in pipeline for better understanding, archiving this would help us to design desired complex mappings.

Hi,

I have developed logic to archive this through JavaScript, can anyone help me to translate it in snaplogic expression language ? (newPayload is created by passing the actual payload)

let itemlen = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item.length;
for (i = 0; i < itemlen; i++) {
let BlockAssignmentlen = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].AccountingCodingBlockDistribution.AccountingCodingBlockAssignment.length
for (j = 0; j < BlockAssignmentlen; j++) {
newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].AccountingCodingBlockDistribution.AccountingCodingBlockAssignment[j][“lineItemId”] = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].lineItemId
}
};

In a nutshell want to know the functions/snaps which can be used in snaplogic to archive this.

Thanks

can i know how to use for loop in context of snaplogic for single document as input ?

The wildcard JSON-Path approach should work here as well, but I think you’ll need to use the Mapping Root feature of the Mapper to iterate over the outer array. So, in this case, you’d probably use the following as the Mapping Root in the Mapper:

$newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[*]

That tells the Mapper to apply the mappings in the table to all of the elements in the PurchaseOrder.item array. Then, you would add a row to the mapping table that read the lineItemId:

$lineItemId

And, then wrote it to every element of the AccountingCodingBlockAssignment array, using this Target Path:

$AccountingCodingBlockDistribution.AccountingCodingBlockAssignment[*].lineItemId

thanks @tstack, archived this with below logic in mapper(did not used root path) :

Expression:
jsonPath($, “$PurchaseOrderERPRequest_V1.PurchaseOrder.Item.lineItemId”)[0]

Target path:
$PurchaseOrderERPRequest_V1.PurchaseOrder.Item[0].AccountingCodingBlockDistribution.AccountingCodingBlockAssignment.[“lineItemId”]

as expected, this is distributing the value evenly across any number of object under AccountingCodingBlockAssignment,
however problem here is if item field have 100 lineItemId, its impractical to pass index for each, also we are not sure how many lineItemId will be there in each request.

How this can be handled dynamically so that no need to pass hard-coded index ?