Tokenising json field in Marketo

[Anonymous]

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

[SanfordWhiteman]

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?

[Anonymous]

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]

<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.

[Anonymous]

The code you linked above is not inside the velocity token, all that is contained in the 'jsonProduct' is the id,booking_id, etc.

However if i just paste "${Bookings2_cList.get(0).jsonProduct}" inside the velocity token, and then in the email {{my.jsonProduct}}

It pastes the entire thing, its just a matter of getting each one so i can use them individually, i was hoping i could turn this into {{my.id}}, {{my.booking_id}} so i can use them where i want.

[SanfordWhiteman]

The code you linked above is not inside the velocity token

All your Velocity code to break down the Custom Object record must be inside the Velocity token.

Then the name of the Velocity token, like {{my.velocityParsedProduct}} or whatever you decide to call it, goes into the surrounding email.