How to transform CSV to a nested JSON structure in DataWeave?

Question

I'm working on a project where I need to convert CSV data into a specific JSON format. The CSV contains information about people and their associated products, and I want to aggregate this data into a nested JSON structure where each person is an object with their details and an array of their products

the input CSV is:

Dan,FirstName,LastName,DateofBirth,ProductName,ProductValue
q,denis,Kriz,01/01/1980,sales cloud,1000
q,denis,kriz,01/01/1980,service cloud,2000
x,lucas,kriz,01/01/2010,marketing cloud,3000

Desired JSON output:

{
  "Dan": "q",
  "FirstName": "denis",
  "LastName": "kriz",
  "DateOfBirth": "01/01/1980",
  "Products": [
    {
      "productName": "sales cloud", 
      "productValue": 1000
    },
    {
      "productName": "service cloud",
      "productValue": 2000
    }
  ],
  "Dan": "x",
  "FirstName": "lucas",
  "LastName": "kriz",
  "DateOfBirth": "01/01/2010",
  "Products": [
    {
      "productName": "marketing cloud",
      "productValue": 3000
    }
  ]
}

Here's the script I tried using DataWeave:

%dw 2.0
output application/json

var data = payload
var header = data[0]
var rows = data[1 to -1]

var includedFields = ["ProductName", "ProductValue"]

var groupedData = rows groupBy ($.Dan) mapObject ((value, key, index) -> {
    Dan: key,
    FirstName: value[0].FirstName,
    LastName: value[0].LastName,
    DateOfBirth: value[0].DateofBirth,
    Products: value map ((item, index) -> {
        productName: (item.ProductName as String),
        productValue: (item.ProductValue as Number)
    })
})

---
groupedData

Answer 1

If you are looking for the below JSON as desired output

[
  {
    "Dan": "q",
    "FirstName": "denis",
    "LastName": "kriz",
    "DateOfBirth": "01/01/1980",
    "Products": [
      {
        "productName": "sales cloud",
        "productValue": 1000
      },
      {
        "productName": "service cloud",
        "productValue": 2000
      }
    ]
  },
  {
    "Dan": "x",
    "FirstName": "lucas",
    "LastName": "kriz",
    "DateOfBirth": "01/01/2010",
    "Products": [
      {
        "productName": "marketing cloud",
        "productValue": 3000
      }
    ]
  }
]

you can use this script:

%dw 2.0
output application/json
import * from dw::core::Strings
var groupedData = payload groupBy (item) -> 
    item.Dan ++ '|' ++ item.FirstName  ++ '|' ++ item.LastName ++ '|' ++ item.DateofBirth
---
valuesOf(groupedData) map ((group, index) -> {
    Dan: group[0].Dan,
    FirstName: group[0].FirstName,
    LastName: group[0].LastName,
    DateOfBirth: group[0].DateofBirth,
    Products: group map (product) -> {
        productName: product.ProductName,
        productValue: (product.ProductValue as Number)
    }
})