In RetailPro and NetSuite, there are scenarios where you need to group order items based on specific parameters using Jolt Transformation. Unfortunately, Jolt does not provide a dedicated function or wildcard for this purpose. However, you can achieve the desired grouping using the concat function.
Let’s walk through an example to illustrate how the concat function can be employed for grouping.
Requirements
- Objective: Generate JSON output in a specific format.
-
Format Structure:
- Order level details
- Items List
-
Items List Preparation:
- Items grouped when FacilityExternalId is the same.
- For different FacilityExternalIds, create a new map for Order details and items list.
-
Grouping Criteria:
- Group by both OrderId and FacilityExternalId.
- Inclusion of OrderId prevents mixing unrelated Order items with the same FacilityExternalId.
-
Result:
- Ensures clarity in representing Orders and associated Items in a structured manner.
Example Scenario
Consider the following input JSON structure:
Sample Input JSON
[
// Order 196005 with two items sharing the same FacilityExternalId
{
"productStoreId": "STORE",
"orderId": "196005",
"orderName": "SMUS#5118",
"shipments": [
{
"shipmentItems": [
{
"orderItemSeqId": "00001",
"unitPrice": 10.95,
"facilityExternalId": "144"
},
{
"orderItemSeqId": "00002",
"unitPrice": 20.95,
"facilityExternalId": "144"
}
]
}
]
},
// Order 196007 with four items, two with FacilityExternalId "144" and two with "177"
{
"productStoreId": "STORE",
"orderId": "196007",
"orderName": "SMUS#5119",
"shipments": [
{
"shipmentItems": [
{
"orderItemSeqId": "00001",
"unitPrice": 30.95,
"facilityExternalId": "144"
},
{
"orderItemSeqId": "00002",
"unitPrice": 40.95,
"facilityExternalId": "144"
}
]
},
{
"shipmentItems": [
{
"orderItemSeqId": "00003",
"unitPrice": 50.95,
"facilityExternalId": "177"
},
{
"orderItemSeqId": "00004",
"unitPrice": 60.95,
"facilityExternalId": "177"
}
]
}
]
}
]
Expected OutPut
[ {
"orderId" : "196005", //The Order with two Order Items with same facilityExternalId
"items" : [ {
"orderItemSeqId" : "00001",
"unitPrice" : 10.95,
"facilityExternalId" : "144"
}, {
"orderItemSeqId" : "00002",
"unitPrice" : 20.95,
"facilityExternalId" : "144"
} ]
}, {
"orderId" : "196007", //The Order with Four Order Items 00001,00002,00003,00004
"items" : [ {
"orderItemSeqId" : "00001", //Two Order Items 00001,00002 are the same Facility ExternalID
"unitPrice" : 30.95,
"facilityExternalId" : "144"
}, {
"orderItemSeqId" : "00002",
"unitPrice" : 40.95,
"facilityExternalId" : "144"
} ]
}, {
"orderId" : "196007",
"items" : [ { //Two Order Items 00003,00004 are same Facility ExternalID
"orderItemSeqId" : "00003",
"unitPrice" : 50.95,
"facilityExternalId" : "177"
}, {
"orderItemSeqId" : "00004",
"unitPrice" : 60.95,
"facilityExternalId" : "177"
} ]
} ]
Jolt Transformation
Certainly! Let’s refine the explanation for the modify-default-beta operation:
// In the initial modify-default-beta operation, we create a new variable 'groupOrderIds' within each shipment item. This variable is formed by concatenating the values of 'facilityExternalId' and 'orderId'.
{
"operation": "modify-default-beta",
"spec": {
// Wildcard (*) is used to iterate over the outermost elements
"*": {
// 'shipments' is iterated over
"shipments": {
// Another wildcard (*) for iteration over shipment
"*": {
// 'shipmentItems' is iterated over
"shipmentItems": {
// Another wildcard (*) to iterate over each individual item
"*": {
// Using 'concat' function to combine 'facilityExternalId' and 'orderId' with an underscore ('_') in between
"groupOrderIds": "=concat(@(1,facilityExternalId),'_',@(5,orderId))"
}
}
}
}
}
}
}
In this operation, the use of wildcards (*) allows seamless traversal through the JSON structure, accommodating varying levels of nesting. The ‘concat’ function is employed to join the values of ‘facilityExternalId’ and ‘orderId’ with an underscore (‘_’) as a separator. This results in the creation of the ‘groupOrderIds’ variable within each shipment item, capturing the combined values for subsequent processing.
Certainly! Let’s refine the explanation for the shift operation in a clearer manner:
// In the subsequent shift operation, we capitalize on the inherent uniqueness of list names in Jolt. Here, we create a list named 'groupOrderIds' for each shipment item, ensuring that the list name remains unique throughout the transformation.
{
"operation": "shift",
"spec": {
// Wildcard (*) is used to iterate over the outermost elements
"*": {
// 'shipments' is iterated over
"shipments": {
// Another wildcard (*) for iteration over shipment
"*": {
// 'shipmentItems' is iterated over
"shipmentItems": {
// Another wildcard (*) to iterate over each individual item
"*": "@groupOrderIds[]" // Utilizing '@' to prepare a list of 'groupOrderIds' values
}
}
}
}
}
}
In this operation, wildcards (*) facilitate seamless traversal through the JSON structure, accommodating different levels of nesting. The use of ‘@groupOrderIds[]’ is a distinctive approach to generate a list named ‘groupOrderIds’ for each shipment item. The uniqueness of list names in Jolt serves as a key feature, ensuring that the list remains unique across the transformation process, preventing any unintended duplication. The values in these lists are then ready for further manipulation or output.
Final Transformation
[
{
"operation": "modify-default-beta",
"spec": {
"*": {
"shipments": {
"*": {
"shipmentItems": {
"*": {
"groupOrderIds": "=concat(@(1,facilityExternalId),'_',@(5,orderId))"
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"shipments": {
"*": {
"shipmentItems": {
"*": "@groupOrderIds[]"
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"0": {
"orderId": "&2.orderId",
"orderItemSeqId": "&2.items[&1].orderItemSeqId",
"unitPrice": "&2.items[&1].unitPrice",
"facilityExternalId": "&2.items[&1].facilityExternalId"
},
"*": {
"orderItemSeqId": "&2.items[&1].orderItemSeqId",
"unitPrice": "&2.items[&1].unitPrice",
"facilityExternalId": "&2.items[&1].facilityExternalId"
}
}
}
},
{
"operation": "shift",
"spec": {
"*": "[]"
}
}
]
Conclusion
By creatively using the concat function and leveraging Jolt’s capabilities, we successfully grouped order items based on specific parameters. This approach proved effective in meeting our requirements for RetailPro and NetSuite integration.
Feel free to adapt this approach to your specific use case, and happy coding!