Convert a delta table (flattened json format) to a nested java object

[tbmadhav]

I have a delta table with columns like below format. I am querying Databricks using databricks-jdbc 2.7.1 driver. I want to convert the result set to a nested java object.
Order_actualPickupAddress_residential -> string
Order_pickupRequestDetails -> array<struct<pickupRequestOccurenceNumber:string,payorContact:struct<personName:string,companyName:string,phoneNumber:string>,pickupAddress:struct<classification:string,streetLines:array<string>,city:string,stateOrProvinceCode:string,postalCode:string,countryCode:string,residential:string>,softwareId:string,softwareVersion:string,deviceType:string,serviceOfferingId:string,primaryOrderConfirmationIdDetails:array<struct<id:string,type:string>>,requestReceivedDateTime:string,pickupDetailsByDay:array<struct<expectedDate:string,performingOpCo:string,expectedDateTimeWindow:struct<startDateTime:string,endDateTime:string>,attemptEngagementWindow:struct<startDateTime:string,endDateTime:string>,attemptOutcome:string>>>>

I have a java object "Order" which has an object "pickupRequestDetails" which internally have "payorContact" object. what is an easy way to map 100s of such nested fields? 

[BigRoux]

Here are some suggestions/ideas to consider:

To map hundreds of nested fields from a Databricks Delta table to complex Java objects like the Order object described, consider the following approaches:

1. Using Libraries for Object Mapping Utilize libraries such as Jackson, Gson, or MapStruct in Java for structured mapping: - Jackson: It is well-suited for mapping JSON-like structures to complex nested objects. Read the JDBC ResultSet into a JSON object and use ObjectMapper to map it directly to your desired Java class structure. - Parse the result set: java ObjectMapper objectMapper = new ObjectMapper(); Order order = objectMapper.readValue(resultSetJson, Order.class); - Configure annotations on your Java classes, e.g., @JsonProperty for specific mappings.

2. Custom Mapping Functions Write custom converter methods that traverse nested structures in the ResultSet and construct corresponding Java objects: - Iterate through the array and struct columns. - For nested fields, use recursive deserialization logic.

3. Databricks-Specific Tools If possible, transform your Delta table into a JSON-like structure via SQL before retrieving it using the JDBC driver. For example: sql SELECT to_json(struct(*)) AS json_data FROM delta_table This returns each row as JSON, which makes mapping straightforward.

4. Schema-Driven Approach Switch to schema evolution techniques or utilize schema information to automate field mapping: - Delta Lake supports schema evolution to process changes in nested column structures efficiently. - Structure your Java objects to accommodate the schema evolution dynamically.

Notes and Considerations - Ensure databricks-jdbc compatibility to retrieve complex structures like arrays of structs. Libraries such as Arrow (when enabled) simplify serialization but might require configurations like disabling EnableArrow for better UTF-8 handling. - Nested structs (e.g., struct<pickupRequestOccurenceNumber: string, ...>) must align with the Java class definition.

Hope this helps, Lou.