cancel
Showing results for 
Search instead for 
Did you mean: 

Guys need groovy help to build a json map

SOLVED
Highlighted
Frequent Contributor

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: Guys need groovy help to build a json map

Assuming that you are getting jdbc result as below xml

 

Selection_116.png

 

 

 

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

 



Regards,
Rao.

View solution in original post

5 REPLIES 5
Highlighted
Community Hero

Re: Guys need groovy help to build a json map

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.



Regards,
Rao.
Highlighted
Community Leader

Re: Guys need groovy help to build a json map

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

 


Click "Accept as Solution" if my answer has helped, and remember to give "kudos" Smiley Happy

Thanks and Regards,
Himanshu Tayal
Highlighted
Frequent Contributor

Re: Guys need groovy help to build a json map

@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
Highlighted
Community Leader

Re: Guys need groovy help to build a json map

@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.

 


Click "Accept as Solution" if my answer has helped, and remember to give "kudos" Smiley Happy

Thanks and Regards,
Himanshu Tayal
Highlighted
Community Hero

Re: Guys need groovy help to build a json map

Assuming that you are getting jdbc result as below xml

 

Selection_116.png

 

 

 

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

 



Regards,
Rao.

View solution in original post