Forum Discussion

Imran's avatar
Imran
New Contributor
2 years ago

Iterate over json document and pivot the output

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 belo...
Staging Area
snapation6713's avatar
snapation6713
New Contributor III
2 years ago
Copy LinkReport

koryknick,   Thank you for your help.  I was provided a solution by a coworker.

It's essentially that pipeline solution you provided HTTP Client Pagination  with a few tweaks.  I modified the HTTP Client as follows: Removed pagination configurations and the parameters configurations for both http snaps and added to the body of the 2nd HTTP snap   "after" : $splitValue, "limit" : 100    Then I was getting subsequent data.   But the data was duplicating.  So I modified the map for pagination as follows:  

sl.range(1, parseInt($entity.total), 100)  -I also changed the 1st http snap to just give me 1 record just so that I get the total  and I process from page 1 to the end in the rest of the pipeline so I don't do a union.  
 
And it worked!   I am now getting subsequent data and it the records are not duplicating.  

 

 

  • Imran's avatar
    Imran
    New Contributor
    2 years ago
    Copy Link

    @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"
      }
    ]