Forum Discussion

New2API's avatar
New2API
Frequent Contributor
5 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.

 

 

  • nmrao's avatar
    nmrao
    5 years ago

    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

Sort By
  • nmrao's avatar
    nmrao
    Champion Level 3
    5 years ago

    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
    5 years ago

    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.

  • HimanshuTayal's avatar
    HimanshuTayal
    Community Hero
    5 years ago

    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
    5 years ago

    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
      5 years ago

      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.