Forum Discussion

Frequent Contributor

5 years ago

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

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

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

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

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.

Recent Discussions

Accessing database connection string from groovy test step
Solved
3 hours ago
chirangv
Best Practices for Testing HTTP/2 Steps
6 hours ago
chandar_1998
Include external files (XML) for JDBC connection
11 hours ago
Kribbstar
Assert Step Not Executing All Assertions
11 days ago
HawkeyesTester
JDBC database connection pooling
26 days ago
bcu_

Forum Discussion

Guys need groovy help to build a json map

Related Content

ReadyAPI GUI problem

Property Transfer failed when run through maven maven build

Maven build fails

Parse a JSON Response Using Groovy

SVN integration failure with Windows desktop GUI

Recent Discussions

Accessing database connection string from groovy test step

Best Practices for Testing HTTP/2 Steps

Include external files (XML) for JDBC connection

Assert Step Not Executing All Assertions

JDBC database connection pooling