Tokenising json field in Marketo | Community
Skip to main content
A
Anonymous
December 6, 2017
Question

Tokenising json field in Marketo

  • December 6, 2017
  • 1 reply
  • 3474 views

Hi all,

Forgive me  if this is the incorrect place to post!

Background note:

We are wanting to pull from a custom field in an custom object

We work in a peer to peer system where the lead can be (customer and seller)

Basically long story short, we have a field called 'jsonProduct' which I know is currently filled with info such as

    "id":
    "booking_id": 
    "total_tax_inclusive": 
    "total_tax_exlusive": 
    "tax": 
    "duration": 
    "rate_type": 
    "type": 
    "code": ,
    "name": 
    "supplier": 
    "price": 
    "quantity":
    "explanation": "
    "currency": ",
    "country": "
    "data":

 

and so on! I understand that I need to be using velocity to pull these out from the field.

I have been following this guide below

Link here​

#if( ${Bookings2_cList.get(0).jsonProduct}).isEmpty() )
#set( ${Bookings2_cList.get(0).jsonProduct} = '[]' )
#end
#set( $jsonProduct = '#set( $jsonProduct = ' + (${Bookings2_cList.get(0).jsonProduct}) + ' )' ) 
#evaluate( $jsonProduct )
===

I have written something similar (I understand copying it won't necessarily work) however I have tried a lot of different ways and syntax to go around it and also seem to find it hard to find documentation in finding the solution to this problem. It is probably fairly simple fix and I am just not possibly looking hard enough however I was hoping maybe some of you could point me in the correct direction Or may know the solution!

Thank you so much!

Andrew

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

1 reply

SanfordWhiteman
SanfordWhiteman
Level 10
December 6, 2017

Hi Andrew,

If $Bookings2_cList[0] has a single jsonProduct property that's indeed a serialized JSON string like

{ "id" : 123, "booking_id" : "ABC","tax" : 1.875 }

then after running this code

#set( $jsonProduct = '#set( $jsonProduct = ' + $Bookings2_cList[0].jsonProduct + ' )' )
#evaluate( $jsonProduct )

the Velocity variable $jsonProduct will be a live Velocity (i.e. Java) Map with properties:

{id=123, booking_id=ABC, tax=1.875}

which you can access as $jsonProduct.booking_id, etc.

But the way you're representing the jsonProduct in your excerpt looks more like a live JavaScript object (based on the line breaks) or something else (based on the lack of commas). What's an actual, full example of the string value of jsonProduct?

A
Anonymous
December 6, 2017

Hi Sanford,

Thank you for your help!

Currently when I do that, it gives me an error of

------------------------------------

Cannot get email content-

An error occurred when procesing the email Body!

Encountered "feeId" near

    <div id="body" class="mktoText" style="font-family: Helvetica, Arial, Sans-Serif; padding:10px;"> 
      <p>#set( $jsonProduct = '#set( $jsonProduct = ' + $Bookings2_cList[0].jsonProduct + ' )' )  
#evaluate( $jsonProduct )</p> 
    </div>  
-----------------------------

Also under the custom object, 'jsonProduct' is a text type currently, I was wondering if that was why?
Sorry I am a bit inexperienced with this, but this is an example of what is contained in a 'jsonProduct' in the Bookings2 custom object.
There are multiple that are within the [] and each one is considered a product.

[
  {
    "id": "X7eh2uih329he",
    "booking_id": "X7eh2uih329he",
    "total_tax_inclusive": "100",
    "total_tax_exlusive": "50",
    "tax": "10",
    "duration": 10,
    "rate_type": "l",
    "type": "fee",
    "code": "CLE_N_F",
    "name": "Cleaning Fee",
    "supplier": "Owner",
    "price": "100",
    "quantity": 1,
    "explanation": "100 USD",
    "currency": "USD",
    "country": "US",
    "data": "{\"feeId\": \"kdjeijr784\"}"
  },
]

Thank you so much for your help so far!

SanfordWhiteman
SanfordWhiteman
Level 10
December 6, 2017
<div id="body" class="mktoText" style="font-family: Helvetica, Arial, Sans-Serif; padding:10px;"> 
<p>#set( $jsonProduct = '#set( $jsonProduct = ' + $Bookings2_cList[0].jsonProduct + ' )' ) 
#evaluate( $jsonProduct )</p>

Is this code all inside a Velocity token? Otherwise, if it's outside in the body of an email, it won't work -- and inside a Velocity token, mktoText won't work. So something is off here.

With Velocity tokens, you set up the token to output text or HTML. Then you insert the token (by its token name, like {{my.productDump}}) in the email.

In your example string, I see a possible danger zone with the embedded JSON property "data" (you aren't just using JSON, you in fact have JSON inside JSON, and that might need us to bring out some crazy tricks).

But let's make sure you're deploying this the right way first.

Terms & ConditionsAccessibility statement

Loading footer...