4

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

1
  • 2
    Ssjs is just a bad implementation. I think it's based on ECMAscript 3 and mostly slows down sending. 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 
6
  • 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/… Dec 12 '14 at 18:27
  • Gtl is still limited in its parsing ability, 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
0

Having the following DE as example:

Test DE with JSON Data

I recommend using the attributes with single quotes

You could add this Script to your Email Message:

%%[
 VAR @color, @skey
 set @skey = _subscriberkey
]%%

<script runat=server>
  Platform.Load('Core', '1.1.5');
  var skey = Variable.GetValue("@skey");
  var DEName = 'TEST JSON';

  var records =  Platform.Function.Lookup(DEName,'JSON','Email', skey);
  var parsedResult = Platform.Function.ParseJSON( records );
  Variable.SetValue("@color", Stringify(records) );
</script>

color = %%=v(@color)=%%

this example Script gets the SubscriberKey and pass it to the SSJS (using the Variable.GetValue) so the SSJS takes care of retrieving and parsing the field to JSON.

This script filter the row (in this case, for simplicity the field Email correspond to the SubscriberKey used in the send context.

After retrieving and parsing (using Platform.Function.ParseJSON) the field JSON, SSJS changes the value of the AMP variable (Variable.SetValue)

You can change the DEName to your desired Data Extension, and use a different logic to rerieve the field.

Result in sending

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.