Json Parse Issue when json data has uneven schema

[ossDataEngineer]

Hello Apache Hop Team,

Trying to parse the string field data using JSON Input transform,

{ "records": [ { "BillingAddress": { "BillingCity": "Pune", "BillingCountry": "India" }, "name": "BMWInc", "id": "123" }, { "BillingAddress": null, "name": "MercedesBenz", "id": "456" } ] }

Error:

2025/02/19 17:21:41 - JSON input.0 - ERROR: org.apache.hop.pipeline.transforms.jsoninput.exception.JsonInputException:
2025/02/19 17:21:41 - JSON input.0 - The data structure is not the same inside the resource! We found 1 values for json path [$.records.[].BillingAddress.BillingCity], which is different that the number returned for path [$.records.[].name] (2 values). We MUST have the same number of values for all paths.

Goal:

Expecting output data in format below, which later can be persisted to database table

id, name, billingcity, billingcountry
123, BMWInc, Pune, India
456, MercedesBenz, null, null

Can anyone please assist how to solve for above error?

Thanks,
Apache Hop Newbie

[kielegat]

My workaround is to use multiple json input transformations in a sequence. The first json input retrieves the 2 records with 3 fields: Billing address as a new json string, name and id. You use the next json input to extract the fields from the billing address.

[bamaer]

yes, there's a sample for this use case in transforms/json-input-nested-elements.hpl in the samples project.