Convert JSON array of arrays to Java List of a specific class

Question

Let's assume I have JSON data that has been extracted from a source in the following form:

{
  "fileName" : "C:\\Users\\jgagnon\\sample_data\\PDM_BOM.xlsx",
  "sheets" : [ {
    "name" : "PDM_BOM",
    "data" : [ [ "BRANCH", "PARENT ITEM NUMBER", "2ND ITEM NUMBER", "QUANTITY REQUIRED", "UNIT OF MEASURE", "ISSUE TYPE CODE", "LINE TYPE", "STOCK TYPE", "TYPE BOM", "LINE NUMBER", "OPERATING SEQUENCE", "EFFECTIVE FROM DATE", "EFFECTIVE THRU DATE", "DRAWING NUMBER", "UNIT COST", "SCRAP PERCENT" ],
        [ "B20", "208E8840040", "5P884LPFSR2", 0.32031, "LB", "I", "S", "M", "M", 1.0, 10.0, "09/11/13", "12/31/40", null, 0.0, 0.0 ],
        [ "B20", "208E8840168", "5P884LPFSR2", 1.36, "LB", "I", "S", "M", "M", 1.0, 10.0, "02/26/08", "12/31/40", null, 0.0, 0.0 ],
        [ "B20", "208E8840172", "5P884LPFSR2", 1.3924, "LB", "I", "S", "M", "M", 1.0, 10.0, "02/26/08", "12/31/40", null, 0.0, 0.0 ],
        [ "B20", "208E8840180", "5P884LPFSR2", 1.4565, "LB", "I", "S", "P", "M", 1.0, 10.0, "03/04/09", "12/31/40", null, 0.0, 0.0 ],
        [ "B20", "21PPH150166", "8P315TPMRG", 1.39629, "LB", "I", "S", "M", "M", 1.0, 10.0, "03/05/18", "12/31/40", null, 0.0, 0.0 ] ],
    "maxCols" : 16,
    "maxRows" : 14996
  } ]
}

The information I care about is in the data element, which is an array of arrays representing the rows from a sheet. The first row contains the column headers.

Now, assume I have a Java class, BOM.java as follows:

public class BOM {

  private String branch;
  private String parentItemNumber;
  private String secondItemNumber;
  private double quantityRequired;
  private String unitOfMeasure;
  private String issueTypeCode;
  private String lineType;
  private String stockType;
  private String typeBom;
  private double lineNumber;
  private double operatingSequence;
  private String effectiveFromDate;
  private String effectiveThruDate;
  private String drawingNumber;
  private double unitCost;
  private double scrapPercent;

  // getters/setters omitted
}

Is there a way to parse this JSON data that results in a java.util.List of BOM objects?

I assume at the very least, that I need to add some Jackson "JSON" annotations to the class above to help Jackson with field mapping?

Up to this point I have used the Jackson ObjectMapper.readTree(File) method to pull in the data. This provides a JsonNode hierarchy which I can navigate to get to items.

I found some suggestions that referred to using a Jackson TypeFactory or TypeReference, but I don't understand how they work and couldn't get them to work for me.

Here is the simple program I'm currently using:

import java.io.File;
import java.io.IOException;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

public class JSONObjectReader {

  public static void main(String[] args)
      throws JsonParseException, JsonMappingException, IOException {
    ObjectMapper mapper = new ObjectMapper();
    JsonNode root = mapper.readTree(new File("PDM_BOM.json"));
    JsonNode sheets = root.get("sheets");
    JsonNode sheet1 = sheets.get(0);
    JsonNode dataNode = sheet1.get("data");

    // skip first row
    for (int i = 1; i < dataNode.size(); i++) {
      JsonNode n = dataNode.get(i);
      System.out.println(n);
    }
  }

}

if you have a Sheet and Book (e.g) classes you can use mapper.readValue(json, Book.class); — imperezivan
– imperezivan, Commented Sep 14, 2020 at 19:24

MyStackRunnethOver · Accepted Answer · 2020-09-14 19:17:02Z

1

Your list structure is almost like a CSV: a row of column names, followed by many rows of values. Looking around, I couldn't find any Jackson built-ins to handle such a structure - not even one to parse a list while ignoring the first element.

The reason I think you should ignore the first element is that it will be very hard to deal with if your goal is to parse into a POJO and not, for example, a Map<String, String>. This is because if the first row is not fixed, your field names may change, and then your POJO will no longer reflect what you're parsing. Better to just ignore the header, or throw an exception if it doesn't match what you expect, and focus on parsing the subsequent arrays.

The way I would do this is to write a Custom Deserializer for turning List<List<String>> into List<BOM>. The deserializer should:

Get the raw list of lists
Throw out (or verify) the first entry
For all subsequent entries, convert them to BOM
1. I would do this by writing a BOM constructor that takes a List<String>, but you could also do all the conversion in the deserializer.

answered Sep 14, 2020 at 19:17

MyStackRunnethOver

5,3652 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseph Gagnon Over a year ago

Not far from the truth. The JSON is the result of extracting information from an Excel spreadsheet. And, yes I'm ignoring the first row.

tucuxi · Accepted Answer · 2020-09-14 19:27:58Z

1

This should work:

List<DTO> result = new ArrayList<>();

for (int i = 1; i < dataNode.size(); i++) {
  JsonNode n = dataNode.get(i);
  result.add(mapper.readValue(mapper.writeValueAsString(n), DTO.class));
}

Note that it is really ugly - it parses all nodes and then writes back to a JSON string before finally re-parsing as a DTO. A cleaner version would require dealing with the malformed 1st row.

answered Sep 14, 2020 at 19:27

tucuxi

18k2 gold badges48 silver badges80 bronze badges

1 Comment

Joseph Gagnon Over a year ago

I agree. I am skipping the first row, since I don't need it. There isn't a "cleaner" way to do this?

Collectives™ on Stack Overflow

Convert JSON array of arrays to Java List of a specific class

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related