3

I would like to extract the data from a Data Extension column and parse it as JSON and use the data as attributes in an email. How would I go about doing this? Is there a combination of AMPScript and Server-Side JavaScript that would allow this?

I've seen the old XML/XSL support function TransformXML(S1,S2) but I really don't want to step back in time by 10-15 years.

I'm also curious why Server-Side JavaScript is essentially deprecated when the industry seems to be trending to that language (node.js, mongodb, etc.)?

  • 2
    Ssjs is just a bad implementation. I think it's based on ECMAscript 3 and mostly slows down sending. – Kelly J Andrews Dec 12 '14 at 22:22
0

This worked for me. Passing Recipient_List as a JSON string attribute.

"[{\"name\":\"john doe\",\"email\":\"john.doe@mail.com\"},{\"name\":\"jane doe\",\"email\":\"jane.doe@mail.com\"}]"

With the JSON string input shown above.

%%[
   var @json
   set @json = AttributeValue("Recipient_List")
]%%

{{.dataobject JsonVar type=variable source=@json maxrows=20}}
   {{.data}}
        {"target":"@Json"}
   {{/data}}
{{/dataobject}}
{{#each JsonVar}}
   <p>{{name}}, {{email}}</p>
{{/each}}
0

I haven't done much with it, but you can use Guide Template Language (GTL) in SFMC emails to parse JSON -- at least according to this page.

Guide Template Language can work with JSON data sources supplied by script or by a REST API service.

Here's a simple example:

%%[

var @Json
set @Json = '[{"name": "john doe","email": "john.doe@mail.com"},{"name": "jane doe","email": "jane.doe@mail.com"}]'

]%%
{{.dataobject JsonVar type=variable source=@Json maxrows=20}}
     {{.data}}
          {"target":"@Json"}
     {{/data}}
{{/dataobject}}
{{#each JsonVar}}
  <br><br>Name: {{name}}
  <br>Email: {{email}}
{{/each}}

Output

Name: john doe 
Email: john.doe@mail.com 

Name: jane doe 
Email: jane.doe@mail.com 
  • I'm really new to this, but yes this looks like it is on the right track. Are there any better examples? Would I be able to use a Data Extension field inside of a {{.datasource .. }} {{.data}} %%MyJSON%% {{/data}} {{/datasource}} – k s Dec 12 '14 at 17:34
  • GTL is new to ET. It'd probably be more like {{.datasource .. }} {{.data}} {{MyJSON}} {{/data}} {{/datasource}} if MyJSON is in the sending data extension. There are some examples here: help.exacttarget.com/en/documentation/guide_template_language/… – Adam Spriggs Dec 12 '14 at 18:27
  • Gtl is still limited in its parsing ability, – Kelly J Andrews Dec 12 '14 at 19:23
  • Getting there. This works (hard coded data): {{.datasource myData type=inline}}{{.data}}{"first":"k","last":"s"}{{/data}}{{first}} {{last}}{{/datasource}}, while this {{.datasource myData type=inline}}{{.data}}{{testField}}{{/data}}{{first}} {{last}}{{/datasource}} (where testField is a field in the data extension containing JSON text) just gives an error when hooked up to a data extension and triggered send. No idea what the error is or how to find out, just says "Error" under status in My Tracking (even though synchronous triggered send call returned 'ok'). – k s Dec 12 '14 at 20:33
  • 1
    it seems that anything besides raw JSON between {{.data}} and {{/data}} causes an error. tried {{variable}}, {{variable [format=json]}}, {{>var:testField}}, %%testField%%, <script runat="server">..., %%=v(@myData)=%%, and others. There are no example of using type=inline or type=query, only type=list. – k s Dec 12 '14 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.