Iterate over json document and pivot the output

[Imran]

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 

--My Input

[

  {

    "SRC_DATE": "2023-11-20 09:24:29.231",

    "SRC_NAME": "AGENT",    

    "SRC_OWNER": "JHON",    

    "TGT_DATE": "2023-11-20 09:24:29.231",

    "TGT_NAME": "AGENT",

    "TGT_OWNER": "CHRIS",   

    "reason": "Source & Target Records Different",

    "srcTable": "CLASS",

    "primaryColumns": "DATE,NAME",

    "nonPrimaryColumns": "OWNER",

    "srcSchool": "TEST"

  }

]

Desired output -

[

  {

    "COLUMN_NAME" : "SRC_OWNER",

   "COLUMN_VALUE_SRC": "JHON",

   "COLUMN_VALUE_TGT": "CHRIS",   

    "reason": "Source & Target Records Different",

    "srcTable": "CLASS",

    "primaryColumns": "DATE,NAME",

    "nonPrimaryColumns": "OWNER",

    "srcSchool": "TEST"

  }

]

[bojanvelevski]

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]

@bojanvelevski - 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. 

Adding more info -

If the Input is

[
  {
    
    "SRC_DATE": "2023-11-20 09:24:29.231",
    "SRC_NAME": "AGENT",    
    "SRC_OWNER": "JHON",    
    "TGT_DATE": "2023-11-20 09:24:29.231",
    "TGT_NAME": "AGENT_TEST",
    "TGT_OWNER": "CHRIS",   
    "reason": "Source & Target Records Different",
    "srcTable": "CLASS",
    "primaryColumns": "DATE,NAME",
    "nonPrimaryColumns": "OWNER",
    "srcSchool": "TEST"
  }
]

The output I am looking for is -

[
  {
    
    "COLUMN_NAME" : "SRC_OWNER",
   "COLUMN_VALUE_SRC": "JHON",
   "COLUMN_VALUE_TGT": "CHRIS",   
    "reason": "Source & Target Records Different",
    "srcTable": "CLASS",
    "primaryColumns": "DATE,NAME",
    "nonPrimaryColumns": "OWNER",
    "srcSchool": "TEST"
  },
{
    
    "COLUMN_NAME" : "SRC_NAME",
   "COLUMN_VALUE_SRC": "AGENT",
   "COLUMN_VALUE_TGT": "AGENT_TEST",   
    "reason": "Source & Target Records Different",
    "srcTable": "CLASS",
    "primaryColumns": "DATE,NAME",
    "nonPrimaryColumns": "OWNER",
    "srcSchool": "TEST"
  }
]

[Imran]

 @bojanvelevski - Did get chance to look into the issue