Forum Discussion

krispokkuluri's avatar
krispokkuluri
Occasional Contributor
3 years ago

How to query circular json to get desired values which are present deep in sub nodes using groovy ?

Hi ,

Given is a below json object which basically grows based on the data provided in the ui in a circular json way . I am trying to fetch the latest grant details based on the grantdetailId in the id's . My groovy scripting isn't so good after scouring the internet i am posting the question here . Kindly suggest a solution for the same 

 

{"$id":"1","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298033,"GrantId":279167,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":2,"LeaseRate":null,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-06T13:41:40.110","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":498,"MetadataList":null,"Grant":{"$id":"2","$type":"Elm7.Data.Model.Grant, Elm7.Data.Model","GrantId":279167,"Type":"P","PendingGrantId":null,"PendingGrantType":"P","ProjectId":802,"CatalogId":17940,"ForecastId":null,"StartDate":"2022-04-06T00:00:00.000","EndDate":"2022-06-29T00:00:00.000","IsCentralBudget":null,"CreatedDate":"2022-04-06T13:41:40.110","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantId":null,"Project":{"$id":"3","$type":"Elm7.Data.Model.Project, Elm7.Data.Model","ProjectId":802,"ProjectName":"DAES-US","CostCenter":"74290","ParentProjectId":null,"ProjectTypeId":488,"IsActive":true,"Description":null,"LockIdsid":null,"InheritorProjectId":null,"LockDate":null,"LegalEntityCode":"100","LacIdsid":"krehkopf","LacIdsid2":"","LacManagerIdsid":"jtwalsh","OpsManagerIdsid":"jcarlos","Approvals":[],"MetadataList":null,"Heir":[],"Inheritor":null,"ProjectDetails":[{"$id":"4","$type":"Elm7.Data.Model.ProjectDetail, Elm7.Data.Model","ProjectDetailId":4162,"ProjectId":802,"ProjectMetaDataId":1,"Value":"1","IsActive":null,"CreatedDate":"2018-10-16T03:36:34.713","CreatedBy":"wtan23","ModifiedDate":null,"ModifiedBy":null,"ProjectMetaData":null,"Project":{"$ref":"3"}},{"$id":"5","$type":"Elm7.Data.Model.ProjectDetail, Elm7.Data.Model","ProjectDetailId":4161,"ProjectId":802,"ProjectMetaDataId":2,"Value":"0","IsActive":null,"CreatedDate":"2018-10-16T03:36:34.713","CreatedBy":"wtan23","ModifiedDate":null,"ModifiedBy":null,"ProjectMetaData":null,"Project":{"$ref":"3"}},{"$id":"6","$type":"Elm7.Data.Model.ProjectDetail, Elm7.Data.Model","ProjectDetailId":4163,"ProjectId":802,"ProjectMetaDataId":3,"Value":"0","IsActive":null,"CreatedDate":"2018-10-16T03:36:34.713","CreatedBy":"wtan23","ModifiedDate":null,"ModifiedBy":null,"ProjectMetaData":null,"Project":{"$ref":"3"}},{"$id":"7","$type":"Elm7.Data.Model.ProjectDetail, Elm7.Data.Model","ProjectDetailId":5806,"ProjectId":802,"ProjectMetaDataId":4,"Value":"DAES-US","IsActive":true,"CreatedDate":"2021-05-08T06:40:18.753","CreatedBy":"Devaragudi, Prakruthi","ModifiedDate":null,"ModifiedBy":null,"ProjectMetaData":null,"Project":{"$ref":"3"}}],"DivisionBuFADB":null,"ChildProjects":[],"ParentProject":null,"Approver":null,"Approver2":null,"ApproverManager":null,"LockUser":null,"Grants":[{"$ref":"2"},{"$id":"8","$type":"Elm7.Data.Model.Grant, Elm7.Data.Model","GrantId":279168,"Type":"P","PendingGrantId":null,"PendingGrantType":"P","ProjectId":802,"CatalogId":17934,"ForecastId":null,"StartDate":"2022-04-07T00:00:00.000","EndDate":"2023-04-27T00:00:00.000","IsCentralBudget":null,"CreatedDate":"2022-04-06T22:40:37.573","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantId":null,"Project":{"$ref":"3"},"GrantDetails":[{"$id":"9","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298035,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":2,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-06T22:40:37.573","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]},{"$id":"10","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298036,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":5,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-07T00:29:35.937","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]},{"$id":"11","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298037,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":10,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-07T00:34:09.757","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]}],"IssueFlaggings":[],"Billbacks":[],"DiscountAssociations":[],"GrantOverrides":[],"Forecast":null,"Catalog":{"$id":"12","$type":"Elm7.Data.Model.Catalog, Elm7.Data.Model","CatalogId":17934,"SupplierId":275,"ProductCode":"GX9006-SF-TBL","ProductName":"GX9006-SF-TBL","Description":"License","CreatedDate":"2018-10-16T03:36:31.777","CreatedBy":"wtan23","ModifiedDate":"2020-08-19T16:51:57.807","ModifiedBy":"ariding","Billbacks":[],"Supplier":{"$id":"13","$type":"Elm7.Data.Model.Supplier, Elm7.Data.Model","SupplierId":275,"SupplierName":"Zuken","IsActive":true,"ShowPrice":true,"LeaseRate":0.18000000,"Description":null,"ParentSupplierId":null,"SupplierNotes":null,"LockIdsid":null,"LockDate":null,"StartDate":null,"EndDate":null,"CreatedDate":"2018-10-16T03:36:31.667","ModifiedDate":null,"CmOwner":null,"LmOwner":"ariding","SmOwner":null,"Commodity":null,"RenewalQuarter":"Q3","Supplier1":[],"Supplier2":null,"SupplierDaemons":[],"SupplierDetails":[],"SupplierInventories":[],"PoolTables":[],"ProductGroups":[],"FixedLicenseInventories":[],"UserInfo":null,"Discounts":[],"Billbacks":[],"CalculationHistories":[],"Catalogs":[{"$id":"14","$type":"Elm7.Data.Model.Catalog, Elm7.Data.Model","CatalogId":17940,"SupplierId":275,"ProductCode":"PC-Z1601-SF-TBL","ProductName":"PC-Z1601-SF-TBL","Description":"Design Gateway TBL no remix floating","CreatedDate":"2018-10-16T03:36:31.777","CreatedBy":"wtan23","ModifiedDate":null,"ModifiedBy":null,"Billbacks":[],"Supplier":{"$ref":"13"},"Forecasts":[],"Grants":[{"$ref":"2"}],"ProductGroupProducts":[],"CatalogYears":[],"UserInfo":null,"UserInfo1":null,"OtherLicenses":[]},{"$ref":"12"}],"OtherLicenses":[]},"Forecasts":[],"Grants":[{"$ref":"8"}],"ProductGroupProducts":[],"CatalogYears":[],"UserInfo":null,"UserInfo1":null,"OtherLicenses":[]}}],"Billbacks":[],"ForecastControls":[],"OpsManagerName":null,"OtherLicenses":[]},"GrantDetails":[{"$ref":"1"},{"$id":"15","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298034,"GrantId":279167,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":3,"LeaseRate":null,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-06T22:35:31.670","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":498,"MetadataList":null,"Grant":{"$ref":"2"},"GrantServers":[]}],"IssueFlaggings":[],"Billbacks":[],"DiscountAssociations":[],"GrantOverrides":[],"Forecast":null,"Catalog":{"$ref":"14"}},"GrantServers":[]}

 

 

An idea that i am applying is present below but that is only to search with hard coded way i want a more dynamic approach to get the required details from the above json into a data source that i could use further . Thanks in Advance 

 

import groovy.json.*
import groovy.util.*
def request = '[{"$ref":"2"},{"$id":"8","$type":"Elm7.Data.Model.Grant, Elm7.Data.Model","GrantId":279168,"Type":"P","PendingGrantId":null,"PendingGrantType":"P","ProjectId":802,"CatalogId":17934,"ForecastId":null,"StartDate":"2022-04-07T00:00:00.000","EndDate":"2023-04-27T00:00:00.000","IsCentralBudget":null,"CreatedDate":"2022-04-06T22:40:37.573","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantId":null,"Project":{"$ref":"3"},"GrantDetails":[{"$id":"9","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298035,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":2,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-06T22:40:37.573","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]},{"$id":"10","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298036,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":5,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-07T00:29:35.937","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]},{"$id":"11","$type":"Elm7.Data.Model.GrantDetail, Elm7.Data.Model","GrantDetailId":298037,"GrantId":279168,"StartDate":"2022-03-31T16:00:00.000","ToolVersion":null,"HSDNumber":"","PONumber":"","Quantity":10,"LeaseRate":0.800000000,"Price":null,"Formula":null,"IsAmortized":false,"CreatedDate":"2022-04-07T00:34:09.757","CreatedBy":"kpokkulx","ModifiedDate":null,"ModifiedBy":null,"Elm6GrantDetailId":null,"LicenseTypeId":494,"MetadataList":null,"Grant":{"$ref":"8"},"GrantServers":[]}],"IssueFlaggings":[],"Billbacks":[],"DiscountAssociations":[],"GrantOverrides":[],"Forecast":null,"Catalog":{"$id":"12","$type":"Elm7.Data.Model.Catalog, Elm7.Data.Model","CatalogId":17934,"SupplierId":275,"ProductCode":"GX9006-SF-TBL","ProductName":"GX9006-SF-TBL","Description":"License","CreatedDate":"2018-10-16T03:36:31.777","CreatedBy":"wtan23","ModifiedDate":"2020-08-19T16:51:57.807","ModifiedBy":"ariding","Billbacks":[],"Supplier":{"$id":"13","$type":"Elm7.Data.Model.Supplier, Elm7.Data.Model","SupplierId":275,"SupplierName":"Zuken","IsActive":true,"ShowPrice":true,"LeaseRate":0.18000000,"Description":null,"ParentSupplierId":null,"SupplierNotes":null,"LockIdsid":null,"LockDate":null,"StartDate":null,"EndDate":null,"CreatedDate":"2018-10-16T03:36:31.667","ModifiedDate":null,"CmOwner":null,"LmOwner":"ariding","SmOwner":null,"Commodity":null,"RenewalQuarter":"Q3","Supplier1":[],"Supplier2":null,"SupplierDaemons":[],"SupplierDetails":[],"SupplierInventories":[],"PoolTables":[],"ProductGroups":[],"FixedLicenseInventories":[],"UserInfo":null,"Discounts":[],"Billbacks":[],"CalculationHistories":[],"Catalogs":[{"$id":"14","$type":"Elm7.Data.Model.Catalog, Elm7.Data.Model","CatalogId":17940,"SupplierId":275,"ProductCode":"PC-Z1601-SF-TBL","ProductName":"PC-Z1601-SF-TBL","Description":"Design Gateway TBL no remix floating","CreatedDate":"2018-10-16T03:36:31.777","CreatedBy":"wtan23","ModifiedDate":null,"ModifiedBy":null,"Billbacks":[],"Supplier":{"$ref":"13"},"Forecasts":[],"Grants":[{"$ref":"2"}],"ProductGroupProducts":[],"CatalogYears":[],"UserInfo":null,"UserInfo1":null,"OtherLicenses":[]},{"$ref":"12"}],"OtherLicenses":[]},"Forecasts":[],"Grants":[{"$ref":"8"}],"ProductGroupProducts":[],"CatalogYears":[],"UserInfo":null,"UserInfo1":null,"OtherLicenses":[]}}]'
def response = context.expand( '${REST Request#Response#$[0]}' )
//def grant = context.expand( '${REST Request#Response#$[0][\'Grant\']')
//def grantDetail1 = context.expand( '${REST Request#Response#$[0][\'Grant\'][\'GrantDetails\']}' )
//def grants = context.expand( '${REST Request#Response#$[0][\'Grant\'][\'Project\'][\'Grants\']}' )
//log.info(grants)
//def grantdetailsforthegrants = context.expand( '${REST Request#Response#$[0][\'Grant\'][\'GrantDetails\']}' )

def parsedJson = new JsonSlurper().parseText(request)
//def parsedJson = new JsonSlurper().parseText(response)
log.info(parsedJson)
def cnt = parsedJson.Grants.GrantDetailIs.size()  
log.info(cnt)
for (i=0; i < cnt;) {
                
     def grantDetailId = parsedJson.Grants.GrantDetailIs[1].toString()
     log.info grantDetailId
  //   int qty = parsedJson.Grants.GrantDetailIs[i].Quantity.toInteger()
     /*
     if (grantDetailId == "298037") {
               log.info grantDetailId + '  '+ qty.toString()
              // log.info parsedJson.shareInfo[i].Grant
               assert qty == 10
          break
     }
                ++i;*/
}
/*for(json in parsedJson){
	log.info json
}*/
//log.info parsedJson.keySet()
//log.info(parsedJson.Grant)
//log.info(parsedJson.Grant.Project)
//log.info(parsedJson.Grant.GrantDetails)
//log.info(parsedJson.Grant.Project.Grants)
//log.info(parsedJson.Grant.Project.Grants.1.GrantDetails)
//def test2 = new JsonSlurper().parseText(grantDetail1)
//log.info(test2 )
//log.info test2.keySet()
//def parseresponse2 = new JsonSlurper().parseText(grants)
//log.info(parseresponse2)


/*
//def parseresponse3 = new JsonSlurper().parseText(grantdetailsforthegrants)
//log.info(parseresponse3)

def cnt = parsedJson.size()  
log.info(cnt)  
     def grantDetailId = parsedJson.Grant.GrantDetails
   log.info(grantDetailId)  
     
for (i=0; i < cnt;) {
                def grantDetailId = parsedJson.Grant.GrantDetails
   log.info(grantDetailId)  

     int qty = parsedJson.Quantity.toInteger()
     
     if (grantDetailId == "298033") {
               log.info grantDetailId + '  '+ qty.toString()
              // log.info parsedJson.Grant
               assert qty == 2
          break
     }
                ++i;
}
*/

 

The above solution is just an attempt and not the actual required need . I want to query the json to get data from sub nodes in ready api details liek quantity startand end date so on and so forth kindly help

Data-Driven Testing
Function Tests
REST
Scripting

1 Reply

Sort By
  • KarelHusa's avatar
    KarelHusa
    Champion Level 3
    3 years ago

    Hi krispokkuluri ,

    you can try to analyze the JSON with JSONPath, e.g.:

     

    import com.jayway.jsonpath.JsonPath

    def json = new File("C:\\MyPath\\MyFile.json").text

    def maxGrantDetailId = JsonPath.read(json, '$..GrantDetailId.max()')
    def grantInfo = JsonPath.read(json, "\$..*[?(@.GrantDetailId == ${maxGrantDetailId})]")

    assert grantInfo.Quantity[0] == 10

    You can check your JSONPath at https://jsonpath.herokuapp.com/ and then apply it inGroovy.

     

    Another option could be to utilize the JSON DataSource test step.

     

    Best regards,

    Karel