AnsweredAssumed Answered

How to insert JSON objects into DB dynamically.

Question asked by girijak on Apr 19, 2018
Latest reply on Jun 12, 2018 by Joel NeSmith

Hi All,

 

I have a requirement where am using Rest API in my test case. The JSON response that am getting from API will be similar to below.

 

{
"payload": {"data": [
{
"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,
"pstnTyp": null,
"accessTokenReq": null,
"year": null,
"instrumentId": null,
"validFrom": null,
"enttReconRequired": null,
"eventOptions": null,
"sbDptId": null,
"recordId": null,
"bPCategory": null,
"messageType": null,
"securitySubType": null,
"diffInGrsAmnt": null,
"creditDebit": null,
"cshPstngStatus": null,
"receivedDate": null,
"secondArchivalDate": null,
"frgnrsAllwdToHld": null,
"grossRate": null,
"taxType": null,
"entryDate": null,
"thirdArchivalDate": null,
"tradedCrDrInd": null,
"txResCntry": null,
"narrative": null,
"eventType": null,
"accessToken": null,
"cashAccountId": null,
"classification": null,
"rlstnAmt": null,
"sedol": null,
"secAccountId": null,
"uniInstrId": null,
"responseRequired": null,
"accountId": null,
"eligReconStatus": null,
"processingStatus": null,
"channelReference": null,
"securityQty": 2000,
"subAccountId": null,
"technicalReference": null,
"prpsOfTheAccnt": null,
"wthTxOnAccIntrst": null,
"activationDate": null,
"crncy": null,
"paidAmount": null,
"rdToNmnlVlInd": null,
"status": null,
"taxableInd": null,
"processingType": null,
"uniqueInstrumentId": null,
"extPMTradable": null,
"sbOptnSeq": 0,
"mrktSecId": null,
"nmbrngSchm": null,
"levelDescription": null,
"accountStatus": null,
"sentDate": null,
"finalAmount": null,
"incomeTyp": null,
"locationId": "063063900000001 - DTCC",
"projectedAmount": null,
"shrtNm": null,
"eventGroup": null,
"setupTyp": null,
"claimTransformationCreationDt": null,
"holidayLevel": null,
"interestRate": null,
"contractual": null,
"zeroBond": null,
"clientId": null,
"intrstPractc": null,
"sourceAddress": null,
"transactionStatus": null,
"glblSec": null,
"instrmntTyp": null,
"domicileCountry": null,
"userId": null,
"businessInfo": null,
"accountFullLegalName": null,
"queueName": null,
"baseCrncy": null,
"lateEvent": null,
"stage": null,
"sourceDestination": null,
"taxCountry": null,
"instrmntSbTyp": null,
"firstArchivalDate": null,
"bnxId": null,
"cusip": null,
"heldnessIndicator": null,
"payoutType": null,
"accountShortName": null,
"bucketIndicator": "Credit",
"issCntry": null,
"amountToBeRecovered": null,
"autoCompensated": null,
"bPShortName": null,
"cntryOfInc": null,
"levelId": null,
"cashEnttReconStatus": null,
"intrstAppFreq": null,
"day": null,
"recordDt": null,
"eventId": "1800055837",
"cshPstngAccntTyp": null,
"unqInstrmntId": null,
"eligibilityDate": null,
"accountType": null,
"externalEventIdentifier": null,
"securityId": null,
"grossAmount": null,
"creationDate": null,
"cshAcntTyp": null,
"eligibilityBucket": "Eligible",
"diffInTxAmnt": null,
"trnsctnClntRef": null,
"taxCategory": null,
"cshAccntCrncy": null,
"exercisedQty": 0,
"taxRate": null,
"month": null,
"projectionStatus": null,
"securityType": null,
"bPIdOldCustodySys": null,
"lstIntrstAppDate": null,
"rstrInd": null,
"counterPartId": null,
"taxAmount": null,
"clientAcntId": null,
"secCntryOfInc": null,
"secured": null,
"lastEligibilityDate": null,
"firstEligibilityDate": null,
"ownSecurity": null,
"depotId": "BP0630639",
"interestType": null,
"leadMngr": null,
"rlstnPrcntg": null,
"smllstIssDnmntn": null,
"businessReference": null,
"holding": null,
"eventPayouts": null,
"transactionSense": null,
"eligReconRequired": null,
"securityFundAccountId": "070359200001 - 2342678",
"recocilationStatus": null,
"acctInOldCstdSys": null,
"bkngBs": null,
"businessPartnerId": null,
"netAmount": null,
"convertible": null,
"transactionPurpose": null,
"positionBucket": "Settled",
"transactionRef": 0,
"effDt": null,
"entityId": null,
"securityShortName": null,
"trnsctnSns": null,
"message": null,
"denCrncy": null,
"bPFullLglNm": null,
"diffInNtAmnt": null,
"eventIdAtDepot": null,
"nominalValue": null,
"isin": null
},
{
"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,
"pstnTyp": null,
"accessTokenReq": null,
"year": null,
"instrumentId": null,
"validFrom": null,
"enttReconRequired": null,
"eventOptions": null,
"sbDptId": null,
"recordId": null,
"bPCategory": null,
"messageType": null,
"securitySubType": null,
"diffInGrsAmnt": null,
"creditDebit": null,
"cshPstngStatus": null,
"receivedDate": null,
"secondArchivalDate": null,
"frgnrsAllwdToHld": null,
"grossRate": null,
"taxType": null,
"entryDate": null,
"thirdArchivalDate": null,
"tradedCrDrInd": null,
"txResCntry": null,
"narrative": null,
"eventType": null,
"accessToken": null,
"cashAccountId": null,
"classification": null,
"rlstnAmt": null,
"sedol": null,
"secAccountId": null,
"uniInstrId": null,
"responseRequired": null,
"accountId": null,
"eligReconStatus": null,
"processingStatus": null,
"channelReference": null,
"securityQty": 8000,
"subAccountId": null,
"technicalReference": null,
"prpsOfTheAccnt": null,
"wthTxOnAccIntrst": null,
"activationDate": null,
"crncy": null,
"paidAmount": null,
"rdToNmnlVlInd": null,
"status": null,
"taxableInd": null,
"processingType": null,
"uniqueInstrumentId": null,
"extPMTradable": null,
"sbOptnSeq": 0,
"mrktSecId": null,
"nmbrngSchm": null,
"levelDescription": null,
"accountStatus": null,
"sentDate": null,
"finalAmount": null,
"incomeTyp": null,
"locationId": "063063900000001 - DTCC",
"projectedAmount": null,
"shrtNm": null,
"eventGroup": null,
"setupTyp": null,
"claimTransformationCreationDt": null,
"holidayLevel": null,
"interestRate": null,
"contractual": null,
"zeroBond": null,
"clientId": null,
"intrstPractc": null,
"sourceAddress": null,
"transactionStatus": null,
"glblSec": null,
"instrmntTyp": null,
"domicileCountry": null,
"userId": null,
"businessInfo": null,
"accountFullLegalName": null,
"queueName": null,
"baseCrncy": null,
"lateEvent": null,
"stage": null,
"sourceDestination": null,
"taxCountry": null,
"instrmntSbTyp": null,
"firstArchivalDate": null,
"bnxId": null,
"cusip": null,
"heldnessIndicator": null,
"payoutType": null,
"accountShortName": null,
"bucketIndicator": "Credit",
"issCntry": null,
"amountToBeRecovered": null,
"autoCompensated": null,
"bPShortName": null,
"cntryOfInc": null,
"levelId": null,
"cashEnttReconStatus": null,
"intrstAppFreq": null,
"day": null,
"recordDt": null,
"eventId": "1800055837",
"cshPstngAccntTyp": null,
"unqInstrmntId": null,
"eligibilityDate": null,
"accountType": null,
"externalEventIdentifier": null,
"securityId": null,
"grossAmount": null,
"creationDate": null,
"cshAcntTyp": null,
"eligibilityBucket": "Eligible",
"diffInTxAmnt": null,
"trnsctnClntRef": null,
"taxCategory": null,
"cshAccntCrncy": null,
"exercisedQty": 0,
"taxRate": null,
"month": null,
"projectionStatus": null,
"securityType": null,
"bPIdOldCustodySys": null,
"lstIntrstAppDate": null,
"rstrInd": null,
"counterPartId": null,
"taxAmount": null,
"clientAcntId": null,
"secCntryOfInc": null,
"secured": null,
"lastEligibilityDate": null,
"firstEligibilityDate": null,
"ownSecurity": null,
"depotId": "BP0630639",
"interestType": null,
"leadMngr": null,
"rlstnPrcntg": null,
"smllstIssDnmntn": null,
"businessReference": null,
"holding": null,
"eventPayouts": null,
"transactionSense": null,
"eligReconRequired": null,
"securityFundAccountId": "068873800024 - 2695077",
"recocilationStatus": null,
"acctInOldCstdSys": null,
"bkngBs": null,
"businessPartnerId": null,
"netAmount": null,
"convertible": null,
"transactionPurpose": null,
"positionBucket": "Settled",
"transactionRef": 0,
"effDt": null,
"entityId": null,
"securityShortName": null,
"trnsctnSns": null,
"message": null,
"denCrncy": null,
"bPFullLglNm": null,
"diffInNtAmnt": null,
"eventIdAtDepot": null,
"nominalValue": null,
"isin": null
},
{
"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,
"pstnTyp": null,
"accessTokenReq": null,
"year": null,
"instrumentId": null,
"validFrom": null,
"enttReconRequired": null,
"eventOptions": null,
"sbDptId": null,
"recordId": null,
"bPCategory": null,
"messageType": null,
"securitySubType": null,
"diffInGrsAmnt": null,
"creditDebit": null,
"cshPstngStatus": null,
"receivedDate": null,
"secondArchivalDate": null,
"frgnrsAllwdToHld": null,
"grossRate": null,
"taxType": null,
"entryDate": null,
"thirdArchivalDate": null,
"tradedCrDrInd": null,
"txResCntry": null,
"narrative": null,
"eventType": null,
"accessToken": null,
"cashAccountId": null,
"classification": null,
"rlstnAmt": null,
"sedol": null,
"secAccountId": null,
"uniInstrId": null,
"responseRequired": null,
"accountId": null,
"eligReconStatus": null,
"processingStatus": null,
"channelReference": null,
"securityQty": 70000,
"subAccountId": null,
"technicalReference": null,
"prpsOfTheAccnt": null,
"wthTxOnAccIntrst": null,
"activationDate": null,
"crncy": null,
"paidAmount": null,
"rdToNmnlVlInd": null,
"status": null,
"taxableInd": null,
"processingType": null,
"uniqueInstrumentId": null,
"extPMTradable": null,
"sbOptnSeq": 0,
"mrktSecId": null,
"nmbrngSchm": null,
"levelDescription": null,
"accountStatus": null,
"sentDate": null,
"finalAmount": null,
"incomeTyp": null,
"locationId": "063063900000001 - DTCC",
"projectedAmount": null,
"shrtNm": null,
"eventGroup": null,
"setupTyp": null,
"claimTransformationCreationDt": null,
"holidayLevel": null,
"interestRate": null,
"contractual": null,
"zeroBond": null,
"clientId": null,
"intrstPractc": null,
"sourceAddress": null,
"transactionStatus": null,
"glblSec": null,
"instrmntTyp": null,
"domicileCountry": null,
"userId": null,
"businessInfo": null,
"accountFullLegalName": null,
"queueName": null,
"baseCrncy": null,
"lateEvent": null,
"stage": null,
"sourceDestination": null,
"taxCountry": null,
"instrmntSbTyp": null,
"firstArchivalDate": null,
"bnxId": null,
"cusip": null,
"heldnessIndicator": null,
"payoutType": null,
"accountShortName": null,
"bucketIndicator": "Credit",
"issCntry": null,
"amountToBeRecovered": null,
"autoCompensated": null,
"bPShortName": null,
"cntryOfInc": null,
"levelId": null,
"cashEnttReconStatus": null,
"intrstAppFreq": null,
"day": null,
"recordDt": null,
"eventId": "1800055837",
"cshPstngAccntTyp": null,
"unqInstrmntId": null,
"eligibilityDate": null,
"accountType": null,
"externalEventIdentifier": null,
"securityId": null,
"grossAmount": null,
"creationDate": null,
"cshAcntTyp": null,
"eligibilityBucket": "Eligible",
"diffInTxAmnt": null,
"trnsctnClntRef": null,
"taxCategory": null,
"cshAccntCrncy": null,
"exercisedQty": 0,
"taxRate": null,
"month": null,
"projectionStatus": null,
"securityType": null,
"bPIdOldCustodySys": null,
"lstIntrstAppDate": null,
"rstrInd": null,
"counterPartId": null,
"taxAmount": null,
"clientAcntId": null,
"secCntryOfInc": null,
"secured": null,
"lastEligibilityDate": null,
"firstEligibilityDate": null,
"ownSecurity": null,
"depotId": "BP0630639",
"interestType": null,
"leadMngr": null,
"rlstnPrcntg": null,
"smllstIssDnmntn": null,
"businessReference": null,
"holding": null,
"eventPayouts": null,
"transactionSense": null,
"eligReconRequired": null,
"securityFundAccountId": "070810900001 - 2340911",
"recocilationStatus": null,
"acctInOldCstdSys": null,
"bkngBs": null,
"businessPartnerId": null,
"netAmount": null,
"convertible": null,
"transactionPurpose": null,
"positionBucket": "Settled",
"transactionRef": 0,
"effDt": null,
"entityId": null,
"securityShortName": null,
"trnsctnSns": null,
"message": null,
"denCrncy": null,
"bPFullLglNm": null,
"diffInNtAmnt": null,
"eventIdAtDepot": null,
"nominalValue": null,
"isin": null
},
{
"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,
"pstnTyp": null,
"accessTokenReq": null,
"year": null,
"instrumentId": null,
"validFrom": null,
"enttReconRequired": null,
"eventOptions": null,
"sbDptId": null,
"recordId": null,
"bPCategory": null,
"messageType": null,
"securitySubType": null,
"diffInGrsAmnt": null,
"creditDebit": null,
"cshPstngStatus": null,
"receivedDate": null,
"secondArchivalDate": null,
"frgnrsAllwdToHld": null,
"grossRate": null,
"taxType": null,
"entryDate": null,
"thirdArchivalDate": null,
"tradedCrDrInd": null,
"txResCntry": null,
"narrative": null,
"eventType": null,
"accessToken": null,
"cashAccountId": null,
"classification": null,
"rlstnAmt": null,
"sedol": null,
"secAccountId": null,
"uniInstrId": null,
"responseRequired": null,
"accountId": null,
"eligReconStatus": null,
"processingStatus": null,
"channelReference": null,
"securityQty": 1000,
"subAccountId": null,
"technicalReference": null,
"prpsOfTheAccnt": null,
"wthTxOnAccIntrst": null,
"activationDate": null,
"crncy": null,
"paidAmount": null,
"rdToNmnlVlInd": null,
"status": null,
"taxableInd": null,
"processingType": null,
"uniqueInstrumentId": null,
"extPMTradable": null,
"sbOptnSeq": 0,
"mrktSecId": null,
"nmbrngSchm": null,
"levelDescription": null,
"accountStatus": null,
"sentDate": null,
"finalAmount": null,
"incomeTyp": null,
"locationId": "063063900000001 - DTCC",
"projectedAmount": null,
"shrtNm": null,
"eventGroup": null,
"setupTyp": null,
"claimTransformationCreationDt": null,
"holidayLevel": null,
"interestRate": null,
"contractual": null,
"zeroBond": null,
"clientId": null,
"intrstPractc": null,
"sourceAddress": null,
"transactionStatus": null,
"glblSec": null,
"instrmntTyp": null,
"domicileCountry": null,
"userId": null,
"businessInfo": null,
"accountFullLegalName": null,
"queueName": null,
"baseCrncy": null,
"lateEvent": null,
"stage": null,
"sourceDestination": null,
"taxCountry": null,
"instrmntSbTyp": null,
"firstArchivalDate": null,
"bnxId": null,
"cusip": null,
"heldnessIndicator": null,
"payoutType": null,
"accountShortName": null,
"bucketIndicator": "Credit",
"issCntry": null,
"amountToBeRecovered": null,
"autoCompensated": null,
"bPShortName": null,
"cntryOfInc": null,
"levelId": null,
"cashEnttReconStatus": null,
"intrstAppFreq": null,
"day": null,
"recordDt": null,
"eventId": "1800055837",
"cshPstngAccntTyp": null,
"unqInstrmntId": null,
"eligibilityDate": null,
"accountType": null,
"externalEventIdentifier": null,
"securityId": null,
"grossAmount": null,
"creationDate": null,
"cshAcntTyp": null,
"eligibilityBucket": "Eligible",
"diffInTxAmnt": null,
"trnsctnClntRef": null,
"taxCategory": null,
"cshAccntCrncy": null,
"exercisedQty": 0,
"taxRate": null,
"month": null,
"projectionStatus": null,
"securityType": null,
"bPIdOldCustodySys": null,
"lstIntrstAppDate": null,
"rstrInd": null,
"counterPartId": null,
"taxAmount": null,
"clientAcntId": null,
"secCntryOfInc": null,
"secured": null,
"lastEligibilityDate": null,
"firstEligibilityDate": null,
"ownSecurity": null,
"depotId": "BP0630639",
"interestType": null,
"leadMngr": null,
"rlstnPrcntg": null,
"smllstIssDnmntn": null,
"businessReference": null,
"holding": null,
"eventPayouts": null,
"transactionSense": null,
"eligReconRequired": null,
"securityFundAccountId": "064489200001 - FSTY01-1746316",
"recocilationStatus": null,
"acctInOldCstdSys": null,
"bkngBs": null,
"businessPartnerId": null,
"netAmount": null,
"convertible": null,
"transactionPurpose": null,
"positionBucket": "Settled",
"transactionRef": 0,
"effDt": null,
"entityId": null,
"securityShortName": null,
"trnsctnSns": null,
"message": null,
"denCrncy": null,
"bPFullLglNm": null,
"diffInNtAmnt": null,
"eventIdAtDepot": null,
"nominalValue": null,
"isin": null
},
{
"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,
"pstnTyp": null,
"accessTokenReq": null,
"year": null,
"instrumentId": null,
"validFrom": null,
"enttReconRequired": null,
"eventOptions": null,
"sbDptId": null,
"recordId": null,
"bPCategory": null,
"messageType": null,
"securitySubType": null,
"diffInGrsAmnt": null,
"creditDebit": null,
"cshPstngStatus": null,
"receivedDate": null,
"secondArchivalDate": null,
"frgnrsAllwdToHld": null,
"grossRate": null,
"taxType": null,
"entryDate": null,
"thirdArchivalDate": null,
"tradedCrDrInd": null,
"txResCntry": null,
"narrative": null,
"eventType": null,
"accessToken": null,
"cashAccountId": null,
"classification": null,
"rlstnAmt": null,
"sedol": null,
"secAccountId": null,
"uniInstrId": null,
"responseRequired": null,
"accountId": null,
"eligReconStatus": null,
"processingStatus": null,
"channelReference": null,
"securityQty": 1000,
"subAccountId": null,
"technicalReference": null,
"prpsOfTheAccnt": null,
"wthTxOnAccIntrst": null,
"activationDate": null,
"crncy": null,
"paidAmount": null,
"rdToNmnlVlInd": null,
"status": null,
"taxableInd": null,
"processingType": null,
"uniqueInstrumentId": null,
"extPMTradable": null,
"sbOptnSeq": 0,
"mrktSecId": null,
"nmbrngSchm": null,
"levelDescription": null,
"accountStatus": null,
"sentDate": null,
"finalAmount": null,
"incomeTyp": null,
"locationId": "063064100000001 - Vault - Long Title",
"projectedAmount": null,
"shrtNm": null,
"eventGroup": null,
"setupTyp": null,
"claimTransformationCreationDt": null,
"holidayLevel": null,
"interestRate": null,
"contractual": null,
"zeroBond": null,
"clientId": null,
"intrstPractc": null,
"sourceAddress": null,
"transactionStatus": null,
"glblSec": null,
"instrmntTyp": null,
"domicileCountry": null,
"userId": null,
"businessInfo": null,
"accountFullLegalName": null,
"queueName": null,
"baseCrncy": null,
"lateEvent": null,
"stage": null,
"sourceDestination": null,
"taxCountry": null,
"instrmntSbTyp": null,
"firstArchivalDate": null,
"bnxId": null,
"cusip": null,
"heldnessIndicator": null,
"payoutType": null,
"accountShortName": null,
"bucketIndicator": "Credit",
"issCntry": null,
"amountToBeRecovered": null,
"autoCompensated": null,
"bPShortName": null,
"cntryOfInc": null,
"levelId": null,
"cashEnttReconStatus": null,
"intrstAppFreq": null,
"day": null,
"recordDt": null,
"eventId": "1800055837",
"cshPstngAccntTyp": null,
"unqInstrmntId": null,
"eligibilityDate": null,
"accountType": null,
"externalEventIdentifier": null,
"securityId": null,
"grossAmount": null,
"creationDate": null,
"cshAcntTyp": null,
"eligibilityBucket": "Eligible",
"diffInTxAmnt": null,
"trnsctnClntRef": null,
"taxCategory": null,
"cshAccntCrncy": null,
"exercisedQty": 0,
"taxRate": null,
"month": null,
"projectionStatus": null,
"securityType": null,
"bPIdOldCustodySys": null,
"lstIntrstAppDate": null,
"rstrInd": null,
"counterPartId": null,
"taxAmount": null,
"clientAcntId": null,
"secCntryOfInc": null,
"secured": null,
"lastEligibilityDate": null,
"firstEligibilityDate": null,
"ownSecurity": null,
"depotId": "BP0630641",
"interestType": null,
"leadMngr": null,
"rlstnPrcntg": null,
"smllstIssDnmntn": null,
"businessReference": null,
"holding": null,
"eventPayouts": null,
"transactionSense": null,
"eligReconRequired": null,
"securityFundAccountId": "063815700001 - DAY01-1733509",
"recocilationStatus": null,
"acctInOldCstdSys": null,
"bkngBs": null,
"businessPartnerId": null,
"netAmount": null,
"convertible": null,
"transactionPurpose": null,
"positionBucket": "Settled",
"transactionRef": 0,
"effDt": null,
"entityId": null,
"securityShortName": null,
"trnsctnSns": null,
"message": null,
"denCrncy": null,
"bPFullLglNm": null,
"diffInNtAmnt": null,
"eventIdAtDepot": null,
"nominalValue": null,
"isin": null
}
]},
"statusMessage": "Success",
"statusCode": "200"
}

 

 

The "data" JSON array has three objects, so each object will be considered as one row.

So after inserting into DB what ever the response you see above will be three rows of data.

For exp:

"issCurnc": null,
"payDt": null,
"businessPartnerStatus": null,
"intrstFxFreq": null,

 

I want to insert in such a way that, the payDt should become as column name and null is data to that particular column. i.e the key of the object will be column name and the value will be data to that column. 

 

So i just thought one way of doing this.

Initially find the array length of "data".

Now using Map we can make the key as column name and value as data to that particular column.

 

So i stored the Rest step response in a property RES using filter. and tried below to find the length of the data array.

But its not working.

String jsob= testExec.getStateValue("RES");

int i=jsob.payload.data.length;

return i;

 Could you please suggest any other way of doing it. If my approach is fine then can u please help me how i can proceed with JSON array length and the Map functionality.

 

Thank you!

 

Regards,

Girija K.

 

Outcomes