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.

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

2. 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"
            }
          ]
        }
      ]
    }

CodePudding user response：

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

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

CodePudding user response：

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

} ]