I am trying to transform the below input json
[
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 484.95,
"shipments": [
{
"trackingNumber": "123456789",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "101"
},
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "102"
}
]
},
{
"trackingNumber": "12345897",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "103"
},
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "104"
}
]
}
]
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 657.95,
"shipments": [
{
"trackingNumber": "987654321",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "105"
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "106"
}
]
},
{
"trackingNumber": "68435896",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "107"
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "108"
}
]
}
]
}
]
Here is the desired output:
[
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 484.95,
"Items": [
{
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "101"
},
{
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "102"
},
{
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "103"
},
{
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "104"
}
]
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 657.95,
"Items": [
{
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "105"
},
{
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "106"
},
{
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "107"
},
{
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "108"
}
]
}
]
NOTE: Here there is an order map, that has a shipments list, and inside that are shipmentItems.
I want the order details and then the Items List that contains the ShipmentItems details.
I have tried some specs on the original larger json but it seems to group the Items as per the shipments and does not give the desired output.
Can anybody help me understand how can we achieve the desired output?
Thanks in advance!
Part 2:
Here is another scenario:
Input JSON:
[
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 484.95,
"shipments": [
{
"trackingNumber": "123456789",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "101",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "101",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "12",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "101",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "101",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "101",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
},
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "102",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "102",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "122",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "102",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "102",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "102",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
}
]
},
{
"trackingNumber": "12345897",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "103",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "103",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "132",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "103",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "103",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "103",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
},
{
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "104",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "104",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "124",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "104",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "104",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "104",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
}
]
}
]
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"grandTotal": 657.95,
"shipments": [
{
"trackingNumber": "987654321",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "105",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "105",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "126",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "105",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "105",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "105",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "106",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "106",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "162",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "106",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "106",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "106",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
}
]
},
{
"trackingNumber": "68435896",
"carrierPartyId": "EXAMPLE",
"shipmentItems": [
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "107",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "107",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "165",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "107",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "107",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "107",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
},
{
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "108",
"Identifications": [
{
"IdentificationTypeId": "A",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "108",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "129",
"thruDate": null
},
{
"IdentificationTypeId": "B",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "108",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "13",
"thruDate": null
},
{
"IdentificationTypeId": "C",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "108",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "14",
"thruDate": null
},
{
"IdentificationTypeId": "D",
"lastUpdatedStamp": "2023-03-14T10:11:24-04:00",
"productId": "108",
"fromDate": "2023-02-22T15:32:23-05:00",
"idValue": "15",
"thruDate": null
}
]
}
]
}
]
}
]
Desired Output:
[
{
"grandTotal": 484.95,
"orderId": "123456",
"orderName": "ABC12345",
"orderDate": "2022-05-18T08:20:23-04:00",
"Items": [
{
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "101",
"identificationValue": "15"
},
{
"facilityId": "1009",
"facilityExternalId": "1009",
"productId": "102",
"identificationValue": "122"
},
{
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "103",
"identificationValue": "132"
},
{
"facilityId": "1003",
"facilityExternalId": "1003",
"productId": "104",
"identificationValue": "124"
}
]
},
{
"grandTotal": 657.95,
"orderId": "123457",
"orderName": "ABC12346",
"orderDate": "2022-05-18T08:20:23-04:00",
"Items": [
{
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "105",
"identificationValue": "126"
},
{
"facilityId": "1007",
"facilityExternalId": "1007",
"productId": "106",
"identificationValue": "162"
},
{
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "107",
"identificationValue": "165"
},
{
"facilityId": "1008",
"facilityExternalId": "1008",
"productId": "108",
"identificationValue": "14"
}
]
}
]
It's important to determine the grouping well. Principally need to group by orderId, and then should group the subobjects by the index nodes of the subarray(which start with shipment) such as
[
{
"operation": "shift",
"spec": {
"*": {
"grandTotal": "@1,orderId.&",
"shipments": {
"*": {
"shipmentItems": {
"*": {
"*": "@1,orderId.&",
"facility*|pro*": "@1,orderId.&3_&1.&"
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"ord*|gr*": "[#2].&",
"*": {
"*": "[#3].Items[#2].&"
}
}
}
},
{ // pick only one among the repeating components from each array
"operation": "cardinality",
"spec": {
"*": {
"ord*": "ONE"
}
}
},
{ // get rid of the null components
"operation": "modify-overwrite-beta",
"spec": {
"*": "=recursivelySquashNulls"
}
}
]
Edit : You can use the following transformation for Part 2 :
[
{
"operation": "cardinality",
"spec": {
"*": {
"shipments": {
"*": {
"shipmentItems": {
"*": {
"Identifications": "ONE"
}
}
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"grandTotal": "@1,orderId.&",
"shipments": {
"*": {
"shipmentItems": {
"*": {
"ord*": "@1,orderId.&",
"Identifications": {
"@1,facilityId": ["@2,orderId.&4_&2.facilityId", "@2,orderId.&4_&2.facilityExternalId"],
"productId": "@2,orderId.&4_&2.&",
"idValue": "@2,orderId.&4_&2.IdentificationValue"
}
}
}
}
}
}
}
},
{ // pick only one among the repeating components from each array
"operation": "cardinality",
"spec": {
"*": {
"ord*": "ONE"
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"ord*|gr*": "[#2].&",
"*": {
"*": "[#3].Items[#2].&"
}
}
}
},
{ // get rid of the null components
"operation": "modify-overwrite-beta",
"spec": {
"*": "=recursivelySquashNulls"
}
}
]