Stuck in Nested Array object Elements transformation

[SriVinayaga]

Hi,

I have seen a discussion in this community about the difficulty in transforming nested XML Array elements. I don’t think that is limited to XML. I guess it is also for nested JSON Array object elements. Pardon me if my knowledge is limited and reiterating the difficulty in transforming nested array object elements.

I am attaching the pipeline and also sample input and expected output. The fields that required transformation are Region which is at top level array (customer) and Brand which is inside cars array within customer array.

We dont have issue in transforming at the top level array object, but when it comes to an array within array is where we face the problem. This just at 2nd level array itself where we dont have straight forward solution (to my knowledge). What if more levels of nested array’s present.PipelinewithSampleInputandExpectedOutput.zip (3.7 KB)

I have a complex solution for this ( where we make a copy / router and check if the Cars array object present, it do the lookup and transform and then use a join with the original input and set the transformed value in a mapper snap using sl.zip). But that is consuming more time if the nested structure grows.

Hence wanted to know if we have a simple solution for this.

Thanks and regards,
Sri

[tstack]

SriVinayaga:

I have seen a discussion in this community about the difficulty in transforming nested XML Array elements. I don’t think that is limited to XML. I guess it is also for nested JSON Array object elements.

Have you tried the Mapper’s “Mapping Root” feature for transforming arrays in documents?

[tstack]

SriVinayaga:

The fields that required transformation are Region which is at top level array (customer) and Brand which is inside cars array within customer array.

In your example pipeline, the data being looked up is in a JSON Generator, where will it be coming from in your final pipeline? Is it a static file or pulled from a database? Unfortunately, the InMemoryLookup and Join snaps don’t operate on array elements. If the data you’re doing the lookups on is in a JSON file, you could import that file as an expression library and then use a Mapper with a Mapping Root and an expression like the following:

$.extend(lib.brands[$Brand])

That will create a new object with the fields from the original and the fields from the brand lookup table (i.e. TargetBrand).

[SriVinayaga]

Hi Tim,

Thanks for your response. The data in JSON generator will be from a file reader snap (which is a static file that gets refreshed every day) in the actual pipeline.

Just read about MappingRoot option, I will make an attempt, but not sure if that will be helpful as the conditional lookup we do in the actual pipeline has two more fields like sourcesystem and targetsystem which is not part of the array element.

I will also come up with the same pipeline in the approach which I mentioned using Router snap, JSON Split , in memorylookup, mapper, join and another mapper to get the desired output.

I felt that is not the right approach hence this query.

Thanks and regards,
Sri