如何在DataWeave中将CSV转换为嵌套JSON结构？

需求 #

我正在推进一个项目，需要将包含人员及其关联产品信息的CSV转换成嵌套结构的JSON，每个人员对象要包含个人详情和对应的产品数组。

输入CSV #

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

期望输出（注意：存在JSON语法问题） #

{
  "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
    }
  ]
}

我的尝试脚本 #

%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

问题说明与修正 #

首先要指出：你给出的期望JSON存在语法错误——JSON对象不允许重复定义相同的键（比如重复的Dan、FirstName），这种结构在实际中是无效的。

你编写的脚本其实已经实现了正确的数据聚合逻辑，它会生成一个以Dan字段值为键的对象，每个键对应一个完整的人员信息（包含产品数组），输出示例如下：

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

如果想要得到人员对象数组的格式（更符合常见的API输出规范），可以修改脚本，将mapObject替换为map，去掉键映射的逻辑：

%dw 2.0
output application/json

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

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

---
groupedData

修改后的输出为：

[
  {
    "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
      }
    ]
  }
]

内容的提问来源于stack exchange，提问作者denis krizanovic