JSON transformation using JOLT for Multiple arrays

Question

I am new new to JSON. I have Input JSON which includes two arrays. 1st one (main results) contains two vendor and other one (BankDetailSet ->results) has vendor bank details (Key and Account ).i-e vendor can have multiple accounts. In put each vendor has two accounts. I am trying with Jolt, in output, I am able to get Vendor info and bank (bsb and account_number). The issue is corresponding vendor bank (bsb and account_number ) have been populated with wrong details.

In Input 1st vendor (Chigo PvtLimited") has BankKey (9877988787 & 89797879798)
while second one (UFCDD Pvt Limited) has BankKey (652588887 & 294454545)
In Out Put 1st vendor (Chigo PvtLimited") has bsb (9877988787 & 652588887)
while second one (UFCDD Pvt Limited) has bsb (89797879798 & 294454545)

The question is why Bankey values have been interchanged in output (bsb). I have pasted below the current and expected output

The Input is :

{
  "d": {
    "results": [
      {
        "__metadata": {
          "type": "Core vendor.Vendor"
        },
        "VendorNumber": "7779898",
        "VendorName": "Chigo PvtLimited",
        "BankDetailSet": {
          "results": [
            {
              "__metadata": {
                "type": "UFCDR Pvt Limited"
              },
              "BankKey": "9877988787",
              "BankAccount": "987788798778879"
            },
            {
              "__metadata": {
                "type": "UFCDR Pvt Limited"
              },
              "BankKey": "89797879798",
              "BankAccount": "564654456456465"
            }
          ]
        }
      },
      {
        "__metadata": {
          "type": "Alpha vendor.Vendor"
        },
        "VendorNumber": "987545",
        "VendorName": "UFCDD  Pvt Limited",
        "BankDetailSet": {
          "results": [
            {
              "__metadata": {
                "type": "UFCDD.BankDetail"
              },
              "BankKey": "652588887",
              "BankAccount": "66887454"
            },
            {
              "__metadata": {
                "type": "UFCDR Pvt Limited"
              },
              "BankKey": "294454545",
              "BankAccount": "4578777"
            }
          ]
        }
      }
    ]
  }
}

the Spec I've tried :

  [
    {
      "operation": "shift",
      "spec": {
        "d": {
          "results": {
            "*": {
              "VendorNumber": "vendors.[&1].name",
              "VendorName": "vendors.[&1].VendorName",
              "BankDetailSet": {
                "results": {
                  "*": {
                    "BankKey": "vendors[&1].Bank[&4].bsb",
                    "BankAccount": "vendors[&1].Bank[&4].account_number"
                  }
                }
              }
            }
          }
        }
      }
    }
  ]

The Current Output

{
  "vendors": [{
    "name": "7779898",
    "VendorName": "Chigo PvtLimited",
    "Bank": [{
      "bsb": "9877988787",
      "account_number": "987788798778879"
    }, {
      "bsb": "652588887",
      "account_number": "66887454"
    }]
  }, {
    "Bank": [{
      "bsb": "89797879798",
      "account_number": "564654456456465"
    }, {
      "bsb": "294454545",
      "account_number": "4578777"
    }],
    "name": "987545",
    "VendorName": "UFCDD  Pvt Limited"
  }]
}

and the Expected Output

    {
      "vendors": [
        {
          "name": "7779898",
          "VendorName": "Chigo PvtLimited",
          "Bank": [
            {
              "bsb": "9877988787",
              "account_number": "987788798778879"
            },
            {
              "bsb": "89797879798",
              "account_number": "564654456456465"
            }
          ]
        },
        {
          "name": "987545",
          "VendorName": "UFCDD  Pvt Limited",
          "Bank": [
            {
              "bsb": "652588887",
              "account_number": "66887454"
            },
            {
              "bsb": "294454545",
              "account_number": "4578777"
            }
          ]
        }
      ]
    }

Barbaros Özhan · Accepted Answer · 2022-06-06 14:47:00Z

1

You can collect the elements "VendorNumber", "VendorName" and the object with key name "BankDetailSet" under common object notation, then set the relative positioning wildcards such as [&1], [&4] to meet at the same level of indexes of the outermost "results" array such as

[
  {
    "operation": "shift",
    "spec": {
      "d": {
        "results": {
          "*": {
            "VendorNu*": "vendors[&1].name",
            "VendorNa*": "vendors[&1].&",
            "Bank*": {
              "results": {
                "*": {
                  "Bank*": "vendors[&1].Bank[&4].&(0,1)"
                }
              }
            }
          }
        }
      }
    }
  }
]

the demo on the site http://jolt-demo.appspot.com/ is

Edit : What you need within the last comments is just reverse of the previous one, eg. use

"Bank*": "vendors[&4].Bank[&1].&(0,1)"

instead of

"Bank*": "vendors[&1].Bank[&4].&(0,1)"

or literally use (as in your case)

"BankKey": "vendors[&4].Bank[&1].bsb",
"BankAccount": "vendors[&4].Bank[&1].account_number"

such as

[
  {
    "operation": "shift",
    "spec": {
      "d": {
        "results": {
          "*": {
            "VendorNu*": "vendors[&1].name",
            "VendorNa*": "vendors[&1].&",
            "Bank*": {
              "results": {
                "*": {
                  "BankKey": "vendors[&4].Bank[&1].bsb",
                  "BankAccount": "vendors[&4].Bank[&1].account_number"
                }
              }
            }
          }
        }
      }
    }
  }
]

the demo is

edited Jun 6, 2022 at 14:47

answered Jun 5, 2022 at 18:42

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Athar Iqbal Over a year ago

Thank you so much for quick response. One more thing is i want to change the keys name as per below expected result.

Athar Iqbal Over a year ago

Thank you so much for quick response. Output is as per expected.

Barbaros Özhan Over a year ago

Hi @AtharIqbal , you're welcome. Then just replace the line "Bank*": "vendors[&1].Bank[&4].&(0,1)" with two lines : "BankK*": "vendors[&1].Bank[&4].bsb", "BankA*": "vendors[&1].Bank[&4].account_number"

Barbaros Özhan Over a year ago

Were you able to check the last case out @AtharIqbal ?

Athar Iqbal Over a year ago

@ Barbaros Özhan. Yes, I did it and notice big issue. In Input 1st vendor BankKey are 9877988787 & 89797879798 while 2nd vendor has 652588887 & 294454545. while in out put, 1st Bank bsb are 9877988787 & 652588887 and 2nd has 89797879798 & 294454545. We can see values are interchanged. So I tried with 10 vendors and can see each Bank has 10 bsb and account_number array instead of 2. So it's basically populating 1st bankey from each vendor into 1st bank bsb. in 2nd bank its populating 2nd bankey of each vendor and so on. What's it's resolution.

|

Athar Iqbal · Accepted Answer · 2022-06-06 17:34:16Z

1

@ Barbaros Özhan, Thank you so much, below one is producing the required result..

[ { "operation": "shift",

"spec": {

  "d": {

    "results": {


      "*": {
        "VendorNu*": "vendors[&1].name",
        "VendorNa*": "vendors[&1].&",
        "Bank*": {
          "results": {
            "*": {
              "BankKey": "vendors[&4].Bank[&1].bsb",
              "BankAccount": "vendors[&4].Bank[&1].account_number"
            }
          }
        }
      }
    }
  }
}

} ]

answered Jun 6, 2022 at 17:34

Athar Iqbal

414 bronze badges

Collectives™ on Stack Overflow

JSON transformation using JOLT for Multiple arrays

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related