Combine records into a single JSON object per Category using Dataweave

Question

I am using Anypoint Studio 6.1 and Mule 3.8.1 and in Dataweave I have a CSV file as input and JSON list output.

Multiple records are coming in on the CSV file with the same Product Category Id but will have a different product details associated with it. I want my output to be aggregated for each of these records so I have one object for each Product Category Id which contains a list of the product details for each product under that Product Category id. How can I do this?

Current output:

[
  {
    "Products": {
      "ProductDetails": [
        {
          "ProductDetail": {
            "ProductSubDetails": {
              "ProductAmount": 7.50,
              "ProductReplenFrequency": "monthly",
              "ProductsNotes": "Product Notes 1",
              "pick": false
            },
            "ProductType": "PS4 Game"
          },
          "ProductSubType": "Game"
        }
      ]
    },
    "ProductsCategoryId": "ProductS001",
    "ProductSubType": "Computers and Games"
  },
  {
    "Products": {
      "ProductDetails": [
        {
          "ProductDetail": {
            "ProductSubDetails": {
              "ProductAmount": 7.50,
              "ProductReplenFrequency": "fortnightly",
              "ProductsNotes": "Products Notes 2",
              "pick": false
            },
            "ProductType": "X Box One Game"
          },
          "ProductSubType": "Game"
        }
      ]
    },
    "ProductsCategoryId": "ProductS001",
    "ProductSubType": "Computers and Games"
  }
]

Expected output:

[
  {
    "Products": {
      "ProductDetails": [
        {
          "ProductDetail": {
            "ProductSubDetails": {
              "ProductAmount": 7.50,
              "ProductReplenFrequency": "monthly",
              "ProductsNotes": "Product Notes 1",
              "pick": false
            },
            "ProductType": "PS4 Game"
          },
          "ProductSubType": "Game"
        },
        {
          "ProductDetail": {
            "ProductSubDetails": {
              "ProductAmount": 7.50,
              "ProductReplenFrequency": "fortnightly",
              "ProductsNotes": "Products Notes 2",
              "pick": false
            },
            "ProductType": "X Box One Game"
          },
          "ProductSubType": "Game"
        }       
      ]
    },
    "ProductsIdentifier": "ProductS001",
    "ProductSubType": "Computers and Games"
  }
]

Dataweave code:

%dw 1.0
%input payload application/csv
%output application/json skipNullOn = "everywhere" , encoding = "UTF-8"
%var dataLookup = {(payload."ProductsCategoryId" map {($.Id): $.Value})}

---
(payload filter $$ > 2) map ((payload01 , indexOfPayload01) -> {
    Products: {
        ProductsDetails: [{
            ProductsDetail: {
                ProductsubDetails: {
                    ProductsAmount: payload01."Products Amount" as :number,
                    ProductsFrequency: payload01."Products Frequency"
                },
                ProductsType: payload01."Products Type"
            }
        }]
    },
    ProductsCategoryId: payload01."ProductsCategoryId"
})

Sample Data:

ProductsCategoryId,Product Type,Product  Frequency,Product Amount
ProductS001,PS4 Game,Monthly,7.5
ProductS001,X Box One Game,Fortnightly,7.5
ProductS002,Lego,Daily,7

Thanks

I've added sample csv data with 2 records with the same Products Category Id. I have been looking at group by and flatten commands but not been able to get the right result. — user3165854

AnupamBhusari AnupamBhusari · Accepted Answer · 2017-01-17T03:55:32

You have to use look up for achieving desired output. Please refer the following answer for more details

Lookup list of Maps variable in data weave script

Updated answer as per comments

Try with following mapping. Also I am not sure why you have use filter $$ > 2. For body starts at line 5 you can add input reader property "bodyStartLineNumber" Please refer link for more details

%dw 1.0
%input payload application/csv
%output application/json skipNullOn = "everywhere" , encoding = "UTF-8"
---
payload groupBy $.ProductsCategoryId map {
    Products: {
        ProductsDetails:   $ map (product , indexOfProduct) -> {
            ProductsDetail: {
                ProductsubDetails: {
                    ProductsAmount: product."Product Amount" as :number,
                    ProductsFrequency: product."Product Frequency"
                },
                ProductsType: product."Product Type"
            }
        }
    },
    ProductsCategoryId: $."ProductsCategoryId"[0]
}

Output gnerated:-

[
  {
    "Products": {
      "ProductsDetails": [
        {
          "ProductsDetail": {
            "ProductsubDetails": {
              "ProductsAmount": 7
            },
            "ProductsType": "Lego"
          }
        }
      ]
    },
    "ProductsCategoryId": "ProductS002"
  },
  {
    "Products": {
      "ProductsDetails": [
        {
          "ProductsDetail": {
            "ProductsubDetails": {
              "ProductsAmount": 7.5
            },
            "ProductsType": "PS4 Game"
          }
        },
        {
          "ProductsDetail": {
            "ProductsubDetails": {
              "ProductsAmount": 7.5
            },
            "ProductsType": "X Box One Game"
          }
        }
      ]
    },
    "ProductsCategoryId": "ProductS001"
  }
]

HTH

Combine records into a single JSON object per Category using Dataweave

1 Answers