SOLVED

Re: Parsing a JSON string from a Custom object field in email

Go to solution
sg
Level 2
Level 2

Parsing a JSON string from a Custom object field in email

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

 

sg_1-1716314811047.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

Re: Parsing a JSON string from a Custom object field in email

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

 

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

 

 

View solution in original post

4 REPLIES 4
SanfordWhiteman
Level 10 - Community Moderator

Re: Parsing a JSON string from a Custom object field in email

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

Re: Parsing a JSON string from a Custom object field in email

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 

sg_2-1716329907158.png

 

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
Level 10 - Community Moderator

Re: Parsing a JSON string from a Custom object field in email

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

 

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

 

 

sg
Level 2
Level 2

Re: Parsing a JSON string from a Custom object field in email

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