Iterate over json document and pivot the output

Question

I have input as below from source and target, where I need to find the difference between same columns from source (SRC) and target (TGT), and pivot the result to get the desired output as shown below&nbsp;--My Input[&nbsp; {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "SRC_DATE": "2023-11-20 09:24:29.231",&nbsp; &nbsp; "SRC_NAME": "AGENT",&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "SRC_OWNER": "JHON",&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "TGT_DATE": "2023-11-20 09:24:29.231",&nbsp; &nbsp; "TGT_NAME": "AGENT",&nbsp; &nbsp; "TGT_OWNER": "CHRIS",&nbsp; &nbsp;&nbsp; &nbsp; "reason": "Source &amp; Target Records Different",&nbsp; &nbsp; "srcTable": "CLASS",&nbsp; &nbsp; "primaryColumns": "DATE,NAME",&nbsp; &nbsp; "nonPrimaryColumns": "OWNER",&nbsp; &nbsp; "srcSchool": "TEST"&nbsp; }]&nbsp;&nbsp;Desired output -&nbsp;[&nbsp; {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "COLUMN_NAME" : "SRC_OWNER",&nbsp; &nbsp;"COLUMN_VALUE_SRC": "JHON",&nbsp; &nbsp;"COLUMN_VALUE_TGT": "CHRIS",&nbsp; &nbsp;&nbsp; &nbsp; "reason": "Source &amp; Target Records Different",&nbsp; &nbsp; "srcTable": "CLASS",&nbsp; &nbsp; "primaryColumns": "DATE,NAME",&nbsp; &nbsp; "nonPrimaryColumns": "OWNER",&nbsp; &nbsp; "srcSchool": "TEST"&nbsp; }]

bojanvelevski · Answer

Not sure I understand your requirements here, are the differences already being spotter, and you only need to construct the data? If yes, check the pipeline below:

If this is not what you need, please share some more details.

Regards,

Bojan

imran · Answer

&nbsp;bojanvelevski&nbsp;- Did get chance to look into the issue

imran · Answer

@bojanvelevski&nbsp;- The SRC and TGT columns will be dynamic, the above given is just an example. As you said, I am able to spot the difference. But I need to dynamically iterate over json and find the difference between same column present in source and target. (There might be chance that there might be difference in other columns as well), any help is appreciated.&nbsp;&nbsp;Adding more info -If the Input is[&nbsp; {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "SRC_DATE": "2023-11-20 09:24:29.231",&nbsp; &nbsp; "SRC_NAME": "AGENT",&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "SRC_OWNER": "JHON",&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "TGT_DATE": "2023-11-20 09:24:29.231",&nbsp; &nbsp; "TGT_NAME": "AGENT_TEST",&nbsp; &nbsp; "TGT_OWNER": "CHRIS",&nbsp; &nbsp;&nbsp; &nbsp; "reason": "Source &amp; Target Records Different",&nbsp; &nbsp; "srcTable": "CLASS",&nbsp; &nbsp; "primaryColumns": "DATE,NAME",&nbsp; &nbsp; "nonPrimaryColumns": "OWNER",&nbsp; &nbsp; "srcSchool": "TEST"&nbsp; }]The output I am looking for is -[&nbsp; {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "COLUMN_NAME" : "SRC_OWNER",&nbsp; &nbsp;"COLUMN_VALUE_SRC": "JHON",&nbsp; &nbsp;"COLUMN_VALUE_TGT": "CHRIS",&nbsp; &nbsp;&nbsp; &nbsp; "reason": "Source &amp; Target Records Different",&nbsp; &nbsp; "srcTable": "CLASS",&nbsp; &nbsp; "primaryColumns": "DATE,NAME",&nbsp; &nbsp; "nonPrimaryColumns": "OWNER",&nbsp; &nbsp; "srcSchool": "TEST"&nbsp; },{&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; "COLUMN_NAME" : "SRC_NAME",&nbsp; &nbsp;"COLUMN_VALUE_SRC": "AGENT",&nbsp; &nbsp;"COLUMN_VALUE_TGT": "AGENT_TEST",&nbsp; &nbsp;&nbsp; &nbsp; "reason": "Source &amp; Target Records Different",&nbsp; &nbsp; "srcTable": "CLASS",&nbsp; &nbsp; "primaryColumns": "DATE,NAME",&nbsp; &nbsp; "nonPrimaryColumns": "OWNER",&nbsp; &nbsp; "srcSchool": "TEST"&nbsp; }]

Forum Discussion

Iterate over json document and pivot the output

3 Replies

Recent Discussions

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?

Multipart Reader failure - 'content-type' was not found

Unable to write sldb file to file system