Re: JSON and Velocity script structure

Kainelson · ‎04-10-2024

hi there,

My company is launching a new initiative that requires us to get personalized data sent to us through a content engine to Marketo viac custom object field and a JSON string. In past campaigns, we used pipes to separate the data points and then velocity scripting to piece it out and utilize it in emails. Here's an example JSON:

[{"Meteora boutique villa E (37622831)":{"oppies":[["37622831|BMLRCAVAILSEASONALITY|30|18 ","37622831|BMLRCAVAILOTHER|30|12|2ecd1db1d073f38ae93c7b8a3bd09ddb","37622831|BMLRCAVAILMISSINGREF|30|2|2ecd1db1d073f38ae93c7b8a3bd09ddb"]]}],"Meteora boutique villa A (37653217)":{"oppies":[["37653217|BMLRCAVAILSEASONALITY|30|15","37653217|BMLRCAVAILOTHER|30|7","37653217|BMLRCAVAILMISSINGREF|30|6"]]}}]

Example Velocity script:

#set( $translations = {
"FR" : ["Hébergement (N°)","Observation","Nombre de jours observés au cours des 30 derniers jours"],
"CS" : ["Ubytovací zařízení (ID)","Zjištění","Počet dní zjištěných za uplynulých 30 dní"],
"PT" : ["N.º de identificação do alojamento","Observação","Número de dias observados nos últimos 30 dias"],
"JA" : ["施設 (ID)","確認内容","過去 30 日間に確認された日数"],
"IT" : ["Struttura (ID)","Osservazione","Numero di giorni osservato negli ultimi 30 giorni"],
"EN-US" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"TH" : ["รหัสที่พัก","ข้อสังเกต","จำนวนวันที่พบในช่วง 30 วันที่ผ่านมา"],
"ZH-CN" : ["住宿 (ID)","监控结果","过去 30 天的监控天数"],
"ZH-TW" : ["旅宿 (編號)","觀察結果","過去 30 天內觀察到的天數"],
"Default" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN-AU" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN-CA" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN-GB" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN-NZ" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"EN-SG" : ["Property (ID)","Observation","Number of days observed in past 30 days"],
"HR" : ["Smještajni objekt (ID)","Opažanje","Broj dana u posljednjih 30 dana"],
"DA" : ["Overnatningssted (ID)","Observation","Antal dage, dette er observeret i de seneste 30 dage"],
"NL" : ["Accommodatie(nummer)","Observatie","Aantal dagen geobserveerd in de afgelopen 30 dagen"],
"FI" : ["Majoituspaikan tunnus","Havainto","Havaintopäivien määrä viimeisten 30 päivän aikana"],
"DE" : ["Unterkunft (ID)","Beobachtung","Anzahl der in den letzten 30 Tagen beobachteten Tagen"],
"DE-AT" : ["Unterkunft (ID)","Beobachtung","Anzahl der in den letzten 30 Tagen beobachteten Tagen"],
"EL" : ["Κατάλυμα (Κωδικός)","Παρατήρηση","Αριθμός ημερών που παρατηρήθηκε τις τελευταίες 30 ημέρες"],
"HU" : ["Szálláshely (azonosítószám)","Hasznos ismeret","Az elmúlt 30 napban megfigyelt napok száma"],
"KO" : ["숙박 시설(ID)","관찰 결과","지난 30일간 문제가 확인된 일 수"],
"NO" : ["Overnattingssted (ID)","Observasjon","Antall dager dette er observert de siste 30 dagene"],
"PL" : ["Identyfikator obiektu","Obserwacja","Liczba dni objętych obserwacją w ciągu ostatnich 30 dni"],
"PT-BR" : ["Propriedade (ID)","Observação","Número de dias observados nos últimos 30 dias"],
"RU" : ["Объект размещения (ID)","Наблюдение","Количество дней наблюдений за последние 30 дней"],
"SV" : ["Boende (ID)","Observation","Antal dagar som observerats under de senaste 30 dagarna"],
"TR" : ["Konaklama Yeri (Kimlik No.)","Gözlem","Son 30 gün içinde gözlemlendiği gün sayısı"],
"VI" : ["Nơi lưu trú (mã)","Vấn đề","Số ngày phát hiện trong 30 ngày gần nhất"],
"ES-ES" : ["N.º de alojamiento","Observación","Número de días en los últimos 30 días"],
"ES" : ["ID de la propiedad","Observaciones","Número de días en los últimos 30 días"],
"FR-CA" : ["Hébergement (identifiant)","Observation","Nombre de jours observés au cours des 30 derniers jours"],
"ID" : ["Properti (ID)","Observasi","Jumlah hari yang diobservasi dalam 30 hari terakhir"],
"AR" : ["عدد الأيام التي تمت ملاحظتها في آخر 30 يومًا","ملاحظة","(معرّف) المنشأة"],
"HE" : ["מספר הימים שבהם נצפה ב-30 הימים האחרונים","הערות","נכס (מספר זיהוי)"],
"ZH-HK" : ["住宿編號","觀察結果","過去 30 日的觀察日數"],
"IS" : ["Gististaðarnúmer","Athugasemd","Fjöldi daga þar sem þetta greindist á síðustu 30 dögum"],
"MS" : ["Hartanah (ID)","Pemerhatian","Bilangan hari yang diperhatikan selama 30 hari yang lalu"]})
<table class="compression" style="column-gap:10px;">
<thead>
<tr>
<th style="text-align:left; font-size:12px!important; padding-right: 10px!important;">$translations[$lead.Segmentation_Language_1004][0]</th>
<th style="text-align:left; font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">$translations[$lead.Segmentation_Language_1004][1]</th>
<th style="text-align:left; font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">$translations[$lead.Segmentation_Language_1004][2]</th>
</tr>
</thead>
#if( $brainACR_cList.get(0).rCBMLTrendedMPData.isEmpty() )
#set( ${brainACR_cList.get(0).rCBMLTrendedMPData}= '[]' )
#end
#if($brainACR_cList.get(0).rCBMLTrendedMPData.contains("null"))
#set( ${brainACR_cList.get(0).rCBMLTrendedMPData}= $brainACR_cList.get(0).rCBMLTrendedMPData.replace("null","[[]]"))
#end
#set( $counter = 0)
#set( $links = "apps.expediapartnercentral.com/lodging/competitive-opportunities/index.html?utm_source=marketo&utm_medium=email&utm_campaign=mid_1234&utm_content=cta&utm_term=advanced_email")
#set( $json = '#set( $json = ' + ${brainACR_cList.get(0).rCBMLTrendedMPData} +')' )
#evaluate( $json )
#set ($prop_array = [])
#foreach ($name in $json)
#foreach ($property in $name.keySet())
#set( $foo = $prop_array.add($property))
#end
#foreach ($name in $json)
#foreach ($property in $name)
#set($prop_index = $foreach.index)
#foreach ($oppies in $property.oppies)
#foreach ($oppy in $oppies)
#set( $var = $oppy.split("[|]"))
#set( $eid = $var[0])
#set( $oppyid = $var[1])
#set( $days = $var[2])
#set( $oppyoccuring = $var[3])
#set( $oppyfriendly = $oppytranslations[$oppyid])
#set( $url = {
"href":$links,
"text":$prop_array[$prop_index],
"eid":$eid,
"oppy":$oppyfriendly[$lead.Segmentation_Language_1004],
"days":$days,
"occuring":$oppyoccuring
})
#evaluate( "${esc.h}set( ${esc.d}final_link_${counter} = ${esc.d}url )" )
#set( $counter = $counter + 1)
#end
#end
#end
#end
#end
#if($final_link_0)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_0.href}&htid=${final_link_0.eid}" target="_blank">${final_link_0.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_0.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_0.occuring}</td></tr>
#end
#if($final_link_1)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_1.href}&htid=${final_link_1.eid}" target="_blank">${final_link_1.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_1.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_1.occuring}</td></tr>
#end
#if($final_link_2)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_2.href}&htid=${final_link_2.eid}" target="_blank">${final_link_2.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_2.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_2.occuring}</td></tr>
#end
#if($final_link_3)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_3.href}&htid=${final_link_3.eid}" target="_blank">${final_link_3.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_3.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_3.occuring}</td></tr>
#end
#if($final_link_4)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_4.href}&htid=${final_link_4.eid}" target="_blank">${final_link_4.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_4.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_4.occuring}</td></tr>
#end
#if($final_link_5)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_5.href}&htid=${final_link_5.eid}" target="_blank">${final_link_5.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_5.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_5.occuring}</td></tr>
#end
#if($final_link_6)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_6.href}&htid=${final_link_6.eid}" target="_blank">${final_link_6.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_6.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_6.occuring}</td></tr>
#end
#if($final_link_7)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_7.href}&htid=${final_link_7.eid}" target="_blank">${final_link_7.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_7.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_7.occuring}</td></tr>
#end
#if($final_link_8)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_8.href}&htid=${final_link_8.eid}" target="_blank">${final_link_8.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_8.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_8.occuring}</td></tr>
#end
#if($final_link_9)
<tr><td style="font-size:12px!important; padding-right: 10px!important;"><a href="https://${final_link_9.href}&htid=${final_link_9.eid}" target="_blank">${final_link_9.text}</a></td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_9.oppy}</td><td style="font-size:12px!important; padding-left: 10px!important; padding-right: 10px!important;">${final_link_9.occuring}</td></tr>
#end
</table>

Those are examples of a previous campaign. For an upcoming project, we want the content engine to send us information like:

Oppy ID
Localized Proof Point Paragraph
Localized Subject Line
EID

This is going to be less complex, but overtime, there might be more information added. My question is, is there a better way to receive this information though JSON rather than separating by pipes? What if we have a campaign where they don't want to include the subject line, so therefor there would be a piece of the JSON that said "| |". Would it still hold its position? Is there a way to set these variables in the JSON by saying OPPID is _____ then Localization Proofpoint is ______ so that it doesn't need to be done with pipes?

Sorry if this is not articulated well. I'm just sort of trying to learn as I go

SanfordWhiteman · ‎04-11-2024

Meaning a regular JSON object:

{
  "prop1" : "value1",
  "prop2" : "value2",
  "prop3" : "value3"
}

Instead of trying to use "positional" logic in a string like value1|value2|value3.

View solution in original post

SanfordWhiteman · ‎04-11-2024

If you’re asking whether Velocity supports JSON-like literals with more than one level: Yes, it does.

View solution in original post

SanfordWhiteman · ‎04-10-2024

Please make sure you use the Syntax Highlighter (“Insert/Edit Code Sample”). Otherwise your code is not readable. I edited your post this time.

There’s no reason to be using a non-standard format like pipe-delimited values, that’s incredibly prone to error. Simply use separate named JSON properties, nothing to reinvent.

Kainelson · ‎04-11-2024

So the format we were using in the previous campaign is prone to errors? Can you explain the separate named JSON properties?

SanfordWhiteman · ‎04-11-2024

Meaning a regular JSON object:

{
  "prop1" : "value1",
  "prop2" : "value2",
  "prop3" : "value3"
}

Instead of trying to use "positional" logic in a string like value1|value2|value3.

Kainelson · ‎04-11-2024

Does that work if we are having it sent in a string to one field which will then have velocity script to take it apart in an email?

SanfordWhiteman · ‎04-11-2024

If you’re asking whether Velocity supports JSON-like literals with more than one level: Yes, it does.