Using Marketo JSON fields in Velocity (and beyond)

I'm a big fan of using Text type fields (a.k.a. textareas) to store blocks of JSON. Such fields become a sort of poor rich man's custom object, able to store a bunch of related properties about an event, a lead interest, even all the answers to a survey, in just one field. They're a goldmine for productivity if used well.

Here's the example JSON field Uploaded_Documents__c at one of my clients:

[
{
  "type" : "creditapp",
  "url" : "/storage/applications/938vs7.pdf",
  "ts" : "2015-01-15T12:29:31Z"
},
{
  "type" : "guarantor",
  "url" : "/storage/applications/a8fiu7.pdf",
  "ts" : "2015-01-15T15:20:12Z"
},
{
  "type" : "creditapp",
  "url" : "/storage/applications/jd8394.pdf",
  "ts" : "2016-03-10T10:12:48Z"
}
]

And here's the JSON field Clients_Referrals__c at another client:

[
{
  "Email" : "joe@example.com",
  "FullName" : "Joe Exton",
  "DateReferred" : "2016-08-01",
  "ReferralProgram" : "Season Pass Holders"
},
{
  "Email" : "jill@example2.com",
  "FullName" : "Jill Towson",
  "DateReferred" : "2016-08-03",
  "ReferralProgram" : "Gold Donors"
}
]

These are each custom fields on the lead, not {{my.tokens}}. And each one represents an extensible array (the outer []) of objects (the inner {}s). I showed 3 inner Document objects in the first example for brevity, but you could easily have 20 or 30 discrete objects all attached to the same lead, without having to mess with anything in Field Management, let alone using the Custom Objects API.

One key reality (don't call it a drawback!) is that even though JSON fields are text fields on the lead, they are effectively opaque to Smart Lists. “Opaque” meaning a black box: you can't filter on the inner data-within-the-data. SLs can check whether the field is empty or not, but you shouldn't try to go further than that. (Don't try to parse JSON contents using Contains. Just don't.)

The more exciting realities are that JSON fields aren't opaque anywhere else! They are fully parseable and readable (as Objects with keys and values) in SFDC using Apex code; you can pass them to webhooks like the JSON-fluent FlowBoost that can read them as data (and update other regular fields as necessary or kick off triggers); you can output them into Landing Pages and manipulate them completely with JavaScript; you can replicate them to BI tools and data warehouses and report on their inner properties and, our topic today, you can parse them with Velocity scripts and use the unwrapped objects to create dynamic content in emails.

So JSON fields are a great fit for dynamic email content or for parsing in outside systems. You just can't use them for, let's call it, “first-level” Smart List filtering in Marketo. Think of a detailed list of previously purchased products that appears in an email body. Or a record of the new leads a partner has referred, with timestamps and plenty of other info, that will be crunched on the CRM side after it syncs over. Or (as I'm working on right now for a client) a form that puts an SFDC Custom-Object-to-be to a single Marketo field: each form post syncs over to SFDC, is expanded into a true object, and then syncs back to Marketo for a many-to-one relationship. Without the JSON field to transport the info, you couldn't make this work.

Using JSON fields as Velocity objects

The true tech tip for today is how to parse JSON string fields into Velocity Maps (a.k.a. Java Maps) so they can be used for all kinds of fun stuff in Marketo emails.

We already know that lead fields show up as strings in VTL and you can use ConvertTool to convert date-y strings to real Date objects, number-y strings to Integers, etc.

here isn't an explicit converter for map-y strings to Maps, but luckily a valid JSON string can be read as a Velocity map literal. (So JSON is also VON, or “Velocity Object Notation.”)

Here's how you create a live Velocity Map from a map-y string field (including checking if the field is empty). Let's use the string field Clients_Referrals__c from above.

#if( $lead.ClientReferrals__c.isEmpty() )
#set( $lead.ClientReferrals__c = '[]' )
#end
#set( $ClientReferrals = '#set( $ClientReferrals = ' + $lead.ClientReferrals__c + ' )' )
#evaluate( $ClientReferrals )

${ClientReferrals[0].ReferralProgram}       ## outputs "Season Pass Holders"
${ClientReferrals[1].Email}                 ## outputs "jill@example2.com"

As you can see, you get a live, deeply nestable object. You can iterate over array indexes, seek directly to value names, everything you could ever want! And it's all stored in a single Marketo field.

How about in Landing Pages?

Of course! There you don't need to do such complex evaluation. In a quick follow-up post I'll show how it's done.