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

Improve this question
1
  • 2
    Ssjs is just a bad implementation. I think it's based on ECMAscript 3 and mostly slows down sending.
    – Kelly J Andrews
    Commented Dec 12, 2014 at 22:22

3 Answers 3

Reset to default
1

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

Improve this answer
1

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": "[email protected]"},{"name": "jane doe","email": "[email protected]"}]'

]%%
{{.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: [email protected] 
        
Name: jane doe 
Email: [email protected]
Improve this answer
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
    Commented Dec 12, 2014 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
    Commented Dec 12, 2014 at 18:27
  • Gtl is still limited in its parsing ability,
    – Kelly J Andrews
    Commented Dec 12, 2014 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
    Commented Dec 12, 2014 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
    Commented Dec 12, 2014 at 21:15
0

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

"[{\"name\":\"john doe\",\"email\":\"[email protected]\"},{\"name\":\"jane doe\",\"email\":\"[email protected]\"}]"

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}}
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .