如何在Java Spring Boot中将复杂JSON转换为CSV

我有一个complex.json文件，内容如下：

{
  "requestId": "1P02XV425KCASEZGQLB8LPQMF4ZXCURRR01",
  "recyleBinRecords": [
    {
      "Record": {
        "ProductName": "Name1",
        "Id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
        "ProductArea": "Area1"
      },
      "dependecyMaterial": [
        {
          "Name": "D1",
          "id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "parentid": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "value": "Value1"
        },
        {
          "Name": "d2",
          "id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "parentid": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "value": "value2"
        }]},{
      "Record": {
        "ProductName": "Name2",
        "Id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
        "ProductArea": "Product2"
      },
      "dependecyMaterial": [
        {
          "Name": "D3",
          "id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "parentid": "P1",
          "value": "value1"
        },
        {
          "Name": "D4",
          "id": "1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01",
          "parentid": "P2",
          "value": "value2"
        }
      ]
    }
  ]
}

尝试用以下代码转换时触发了no column exception错误：

JsonNode jsonTree = new ObjectMapper().readTree(new File("src/main/resources/complex.json"));
Builder csvSchemaBuilder = CsvSchema.builder();
JsonNode firstObject = jsonTree.elements().next();
firstObject.fieldNames().forEachRemaining(fieldName -> {csvSchemaBuilder.addColumn(fieldName);} );
CsvSchema csvSchema = csvSchemaBuilder.build().withHeader();

CsvMapper csvMapper = new CsvMapper();
csvMapper.writerFor(JsonNode.class)
  .with(csvSchema)
  .writeValue(new File("src/main/resources/orderLines.csv"), jsonTree);

错误原因 #

代码直接处理顶层JSON对象，但顶层对象包含requestId和recyleBinRecords数组，而recyleBinRecords里的元素又嵌套了Record对象和dependecyMaterial数组，这种嵌套结构无法被Jackson的CsvMapper直接映射成扁平的CSV列，因此抛出无列异常。

解决方案 #

要实现转换，需先将嵌套的JSON结构扁平化，把每个dependecyMaterial条目和对应的Record字段组合成一行CSV数据，具体步骤如下：

1. 定义扁平化DTO类 #

创建一个DTO类，存储每行CSV需要的所有字段：

public class RecycledItemDto {
    private String productName;
    private String productId;
    private String productArea;
    private String dependencyName;
    private String dependencyId;
    private String dependencyParentId;
    private String dependencyValue;

    // 全参构造器
    public RecycledItemDto(String productName, String productId, String productArea,
                           String dependencyName, String dependencyId, String dependencyParentId,
                           String dependencyValue) {
        this.productName = productName;
        this.productId = productId;
        this.productArea = productArea;
        this.dependencyName = dependencyName;
        this.dependencyId = dependencyId;
        this.dependencyParentId = dependencyParentId;
        this.dependencyValue = dependencyValue;
    }

    // Getter和Setter方法
    public String getProductName() { return productName; }
    public void setProductName(String productName) { this.productName = productName; }
    public String getProductId() { return productId; }
    public void setProductId(String productId) { this.productId = productId; }
    public String getProductArea() { return productArea; }
    public void setProductArea(String productArea) { this.productArea = productArea; }
    public String getDependencyName() { return dependencyName; }
    public void setDependencyName(String dependencyName) { this.dependencyName = dependencyName; }
    public String getDependencyId() { return dependencyId; }
    public void setDependencyId(String dependencyId) { this.dependencyId = dependencyId; }
    public String getDependencyParentId() { return dependencyParentId; }
    public void setDependencyParentId(String dependencyParentId) { this.dependencyParentId = dependencyParentId; }
    public String getDependencyValue() { return dependencyValue; }
    public void setDependencyValue(String dependencyValue) { this.dependencyValue = dependencyValue; }
}

2. 读取JSON并扁平化数据 #

遍历JSON中的嵌套结构，将每个依赖物料与所属产品信息组合成DTO对象：

ObjectMapper objectMapper = new ObjectMapper();
JsonNode rootNode = objectMapper.readTree(new File("src/main/resources/complex.json"));
JsonNode recordsNode = rootNode.get("recyleBinRecords");

List<RecycledItemDto> flatData = new ArrayList<>();

for (JsonNode recordItem : recordsNode) {
    // 提取产品基础信息
    JsonNode record = recordItem.get("Record");
    String productName = record.get("ProductName").asText();
    String productId = record.get("Id").asText();
    String productArea = record.get("ProductArea").asText();

    // 遍历每个依赖物料，生成一行数据
    JsonNode dependencies = recordItem.get("dependecyMaterial");
    for (JsonNode dep : dependencies) {
        String depName = dep.get("Name").asText();
        String depId = dep.get("id").asText();
        String depParentId = dep.get("parentid").asText();
        String depValue = dep.get("value").asText();

        flatData.add(new RecycledItemDto(productName, productId, productArea,
                depName, depId, depParentId, depValue));
    }
}

3. 将扁平化数据写入CSV #

使用CsvMapper将DTO列表写入CSV文件：

CsvMapper csvMapper = new CsvMapper();
CsvSchema schema = csvMapper.schemaFor(RecycledItemDto.class).withHeader();

csvMapper.writer(schema)
        .writeValue(new File("src/main/resources/orderLines.csv"), flatData);

最终CSV格式示例 #

生成的CSV会是扁平结构，每个依赖物料对应一行，关联所属产品的信息：

productName,productId,productArea,dependencyName,dependencyId,dependencyParentId,dependencyValue
Name1,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,Area1,D1,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,Value1
Name1,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,Area1,d2,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,value2
Name2,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,Product2,D3,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,P1,value1
Name2,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,Product2,D4,1P02XV425KCASEZGQLB8LPQMF4ZXCUBCTA01,P2,value2

内容的提问来源于stack exchange，提问作者Aman Mishra