Parsing a JSON string from a Custom object field in email | Community
Skip to main content
S
sg-3
New Participant
May 21, 2024
Solved

Parsing a JSON string from a Custom object field in email

  • May 21, 2024
  • 1 reply
  • 2407 views

hi

I have followed the same steps, but still no luck in parsing the JSON from my Custom Object.

My Custom Object field (Field Name: RData) has data like below

 

RData field has data as below

 

 

[ { "StuLast": "Abram", "StuFirst": "Easton", "Program": "UGD", "Subject": "MUSC", "Catalog": "10A", "Section": 90 }, { "StuLast": "StuLast2", "StuFirst": "StuFirst2", "Program": "UGD", "Subject": "Chemistry", "Catalog": "20B", "Section": 65 } ]

 

When I tried the following code in the Token to parse, I am getting an empty string

 

#set($data = ${registrar_cList.get(0).rData}) #if($data.isEmpty()) #set($data='{}') #end #set($parsedJson = '#set($parsedJson='+ $data+')') #evaluate($parsedJson) $parsedJson['subject']

 

 

 

Note: I did upload the csv file into Marketo as  UTF-8 format when updating the Custom object.

 

My Goal is to display data from this Custom object field in an email.

Any help is appreciated.

 

thanks

-Sree

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by SanfordWhiteman

$parsedJson is a collection, you can just #foreach over it.

 

#foreach( $item in $parsedJson ) ${item.property1} ${item.property2} ## etc. #end

 

 

1 reply

SanfordWhiteman
SanfordWhiteman
New Participant
May 21, 2024

Well 1st, you should be escaping reserved Velocity symbols or you will get unexpected results. Also setting your value to an empty object by default doesn’t make sense if the expected value is an array. You should set it to an empty array:

 

#set($data = ${registrar_cList.get(0).rData}) #if( $data.isEmpty() ) #set( $data= "[]" ) #end #set( $evaluatable = "${esc.h}set( ${esc.d}parsedJson = " + $data + ")" ) #evaluate( $evaluatable )

 

 

More important though, you wouldn’t expect

 

${parsedJson["subject"]}

 

to have a value!

 

When correctly parsed, $parsedJson is going to be an array of objects — List of Maps, technically — since that’s what the JSON represents.

 

So the "subject" property of the first object is:

 

${parsedJson[0]["subject"]}

 

 

 

S
sg-3Author
New Participant
May 21, 2024

Thank you for the quick response.

After updating the script I am able to get the results for the first element as below

#set($data = ${registrar_cList.get(0).rData}) #if( $data.isEmpty() ) #set( $data= "[]" ) #end #set( $evaluatable = "${esc.h}set( ${esc.d}parsedJson = " + $data + ")" ) #evaluate( $evaluatable ) Term: ${parsedJson[0]["Term"]}, ID: ${parsedJson[0]["ID"]}, Last Name, First Name: ${parsedJson[0]["StuLast"]}, ${parsedJson[0]["StuFirst"]} <br/> Program: ${parsedJson[0]["Program"]}, Subject & Catalog#: ${parsedJson[0]["Subject"]} ${parsedJson[0]["Catalog"]}, Section#: ${parsedJson[0]["Section"]}

 

Data is displayed as 

 

Now If I want to get the Json data in a loop for the parsedJson object,  how does it work?

I have tried following an example, but did not get both the elements
Ref: followed the example posted on StackOverflow ( https://stackoverflow.com/questions/62713626/velocity-template-loop-through-array-to-create-string ), but no luck

#set($listOfWords = ${registrar_cList.get(0).rData}) #set($q = '"') #set($queryString = "") #foreach($word in $listOfWords) #if( $velocityCount == 1 ) #set($queryString = "$q$word$q") #else #set($queryString = "$queryString+$q$word$q") #end #set( $evaluatable = "${esc.h}set( ${esc.d}parsedJson = " + $queryString + ")" ) #evaluate( $evaluatable ) Term: ${parsedJson[0]["Term"]}, ID: ${parsedJson[0]["ID"]}, Last Name, First Name: ${parsedJson[0]["StuLast"]}, ${parsedJson[0]["StuFirst"]} <br/> Program: ${parsedJson[0]["Program"]}, Subject & Catalog#: ${parsedJson[0]["Subject"]} ${parsedJson[0]["Catalog"]}, Section#: ${parsedJson[0]["Section"]} #end

 

My goal is to get both the elements from this JSON to display

[ { "ID": 15158702, "StuLast": "Abram", "StuFirst": "Easton", "Program": "UGD", "Subject": "MUSC", "Term": "Spring 2023", "Catalog": "10A", "Section": 90 }, { "ID": 15158702, "StuLast": "StuLast2", "StuFirst": "StuFirst2", "Program": "UGD", "Subject": "Chemistry", "Term": "Spring 2023", "Catalog": "20B", "Section": 65 } ]

 

Help appreciated.

 

thanks

-Sree

SanfordWhiteman
SanfordWhitemanAccepted solution
New Participant
May 22, 2024

$parsedJson is a collection, you can just #foreach over it.

 

#foreach( $item in $parsedJson ) ${item.property1} ${item.property2} ## etc. #end

 

 

Terms & ConditionsAccessibility statement

Loading footer...