Forum Discussion

New2API's avatar
New2API
Frequent Contributor
4 years ago
Solved

Guys need groovy help to build a json map

I am getting few values from a DB. I need to create json map from these values. I tried several things but not successful. 

 

desired output: [

                          {

                            "roleId": "1",

                            "nameId": "123",

                            "addrId": "1234"

                          },

                          

                         {

                            "roleId": "5",

                            "nameId": "456",

                            "addrId": "345"

                          },

                          

                         {

                            "roleId": "7",

                            "nameId": "2356",

                            "addrId": "6756"

                          }

                       ]

 

My DB output looks like this:

           roleId      nameId      addrId

row1      1          123             1234

row2      5          456             345

row3      7          2356           6756

 

 

Below was my approach:

DB out put was assigned to a varibale called list.

import com.eviware.soapui.support.GroovyUtils
import groovy.sql.Sql
import groovy.json.*
import groovy.json.JsonSlurper
import groovy.json.JsonBuilder

def jsonMap = new JsonSlurper().parseText '{"roleId":"", "nameId":"", "addrId": ""}'
def jsonPayload

def roleId
def nameId
def addrId

//way to get data from DB
//## Read SQL from a File to get data ##//
//def getData = SQLPath+"getData.sql"
//def Query = new File(getData).text
//def list = sqlsvrConn.rows(Query)

//list = DB outPUT

list.each{
	        roleId = it.roleId
            nameId = it.nameId
            addrId = it.addrId

            
            jsonMap.roleId = roleId
            jsonMap.nameId = nameId
			jsonMap.addrId = addrId
			
			jsonPayload += jsonMap
			
         
}

I was always getting last row added to jsonPayload..  any help is really appreciated.

Thanks in advance.

 

 

  • Assuming that you are getting jdbc result as below xml

     

     

     

     

    def jdbcData = '''<Results>
        <ResultSet fetchSize="10">
            <Row rowNumber="1">
                <ROLEID>1</ROLEID>
                <NAMEID>123</NAMEID>
                <ADDRESSID>1234</ADDRESSID>
            </Row>
            <Row rowNumber="2">
                <ROLEID>5</ROLEID>
                <NAMEID>456</NAMEID>
                <ADDRESSID>345</ADDRESSID>
            </Row>
            <Row rowNumber="3">
                <ROLEID>7</ROLEID>
                <NAMEID>2356</NAMEID>
                <ADDRESSID>6756</ADDRESSID>
            </Row>
        </ResultSet>
    </Results>'''
    
    def jsonString = new groovy.json.JsonBuilder(new XmlSlurper().parseText(jdbcData).ResultSet.Row.collect {
    	[
    		roleId: it.ROLEID.text(),
    		nameId: it.NAMEID.text(),
    		addrId: it.ADDRESSID.text()
    	]	
    }).toPrettyString()
    log.info jsonString

    In case if you have the list, you can use below:

     

    def list = [[roleId:"1", nameId:"123", addrId:"1234"], [roleId:"5", nameId:"456", addrId:"345"], [roleId:"7", nameId:"2356", addrId:"6756"]]
    def jsonString = new groovy.json.JsonBuilder(list).toPrettyString()
    log.info jsonString

    Either case, output will be:like as it is mentioned in the queston.

     

    [
        {
            "roleId": "1",
            "nameId": "123",
            "addrId": "1234"
        },
        {
            "roleId": "5",
            "nameId": "456",
            "addrId": "345"
        },
        {
            "roleId": "7",
            "nameId": "2356",
            "addrId": "6756"
        }
    ]
    

     

5 Replies

  • nmrao's avatar
    nmrao
    Champion Level 3

    Assuming that you are getting jdbc result as below xml

     

     

     

     

    def jdbcData = '''<Results>
        <ResultSet fetchSize="10">
            <Row rowNumber="1">
                <ROLEID>1</ROLEID>
                <NAMEID>123</NAMEID>
                <ADDRESSID>1234</ADDRESSID>
            </Row>
            <Row rowNumber="2">
                <ROLEID>5</ROLEID>
                <NAMEID>456</NAMEID>
                <ADDRESSID>345</ADDRESSID>
            </Row>
            <Row rowNumber="3">
                <ROLEID>7</ROLEID>
                <NAMEID>2356</NAMEID>
                <ADDRESSID>6756</ADDRESSID>
            </Row>
        </ResultSet>
    </Results>'''
    
    def jsonString = new groovy.json.JsonBuilder(new XmlSlurper().parseText(jdbcData).ResultSet.Row.collect {
    	[
    		roleId: it.ROLEID.text(),
    		nameId: it.NAMEID.text(),
    		addrId: it.ADDRESSID.text()
    	]	
    }).toPrettyString()
    log.info jsonString

    In case if you have the list, you can use below:

     

    def list = [[roleId:"1", nameId:"123", addrId:"1234"], [roleId:"5", nameId:"456", addrId:"345"], [roleId:"7", nameId:"2356", addrId:"6756"]]
    def jsonString = new groovy.json.JsonBuilder(list).toPrettyString()
    log.info jsonString

    Either case, output will be:like as it is mentioned in the queston.

     

    [
        {
            "roleId": "1",
            "nameId": "123",
            "addrId": "1234"
        },
        {
            "roleId": "5",
            "nameId": "456",
            "addrId": "345"
        },
        {
            "roleId": "7",
            "nameId": "2356",
            "addrId": "6756"
        }
    ]
    

     

  • nmrao's avatar
    nmrao
    Champion Level 3

    What are the test steps in the test.case? Is it like below?
    Jdbc step to fetch result in XML
    Current grrovy test step

    Or specify if different.

     

    EDIT:

    Earlier didn't notice that you are quering db from the same script.

  • New2API  : try to declare jsonPayload as StringBuilder, so that it will append every time you insert some data into it.

     

  • New2API's avatar
    New2API
    Frequent Contributor

    nmrao, there are only two test steps in my testcase, a groovy step where I get a data from DB (through jdbc connection) and loop through the rows to generate desired payload and a REST step that consumes this payload. 

     

    HimanshuTayal , I modified my script  so that you guys can run and see what's happening...  list variable represents DB output.  Also, notice that final result duplicates same result trice and json value(s) is not withing double quotes.

    import com.eviware.soapui.support.GroovyUtils
    import groovy.sql.Sql
    import groovy.json.*
    import groovy.json.JsonSlurper
    import groovy.json.JsonBuilder
    
    def roleId
    def nameId
    def addrId
    
    def jsonMap = new JsonSlurper().parseText '{"roleId":"", "nameId":"", "addrId": ""}'
    def jsonPayload = []
    
    list = [[roleId:1, nameId:123, addrId:1234], [roleId:5, nameId:456, addrId:345], [roleId:7, nameId:2356, addrId:6756]]
    
    list.each{
    	       roleId = it.roleId
                   nameId = it.nameId
                   addrId = it.addrId
    
                   jsonMap.roleId = roleId
                   jsonMap.nameId = nameId
    	       jsonMap.addrId = addrId
    			
    		jsonPayload += jsonMap
    		log.info jsonPayload 
    }
    
    def newPayload = new JsonBuilder(jsonPayload)
    log.info newPayload
    • HimanshuTayal's avatar
      HimanshuTayal
      Community Hero

      New2API  : I have modified your code a bit have a look :

      import com.eviware.soapui.support.GroovyUtils
      import groovy.sql.Sql
      import groovy.json.*
      import groovy.json.JsonSlurper
      import groovy.json.JsonBuilder
      
      def roleId
      def nameId
      def addrId
      
      def jsonMap = new JsonSlurper().parseText '{"roleId":"", "nameId":"", "addrId": ""}'
      HashMap<Integer, ArrayList<String>> HM = new HashMap<String, ArrayList<String>>()
      ArrayList<String> arrayList = new ArrayList<String>();
      list = [[roleId:1, nameId:123, addrId:1234], [roleId:5, nameId:456, addrId:345], [roleId:7, nameId:2356, addrId:6756]]
      
      for( int i = 0 ; i < list.size( ); i++ ){	
      	arrayList.add("roleId:"+list[i].roleId);
      	arrayList.add("nameId:"+list[i].nameId);
      	arrayList.add("addrId:"+list[i].addrId);
      	HM.put(list[i].roleId, arrayList)
      	arrayList= []
      }
      log.info "This is Hash Map ->"+HM

      And below is the desired output:

       

      • This is Hash Map ->[1:[roleId:1, nameId:123, addrId:1234], 5:[roleId:5, nameId:456, addrId:345], 7:[roleId:7, nameId:2356, addrId:6756]]

      Hope it will solve you issue.