Parsing a JSON string from a Custom object field in email

[sg]

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

[SanfordWhiteman]

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

[sg]

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 · Accepted solution]

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

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

[sg]

Thanks for your help. It worked now

#set($data = ${registrar_cList.get(0).rData})
#if( $data.isEmpty() )
#set( $data= "[]" )
#end
#set( $evaluatable = "${esc.h}set( ${esc.d}parsedJson = " + $data + ")" )
#evaluate( $evaluatable )
<b>List of Students</b>
<br/>
#foreach( $item in $parsedJson )
Term:${item.Term}, ID:${item.ID}, Name:${item.StuLast},${item.StuFirst}, Email:${item.StuEmail},
<br/>
Program:${item.Program}, Subject & Catalog#:${item.Subject}, ${item.Catalog}, Section:${item.Section}
<br/><p/>
#end