15

Using AMPScript to parse an XML feed. Client is switching to JSON. Is there something similar to BuildrowsetfromXML() for JSON in AMPScript? I am confined to using the members login area and not an external API application.

12

I did this using SSJS and eval(). I could not find a way to parse JSON in AMPscript. Also if I recall correctly JSON.parse() is not implemented in ExactTarget.

There are security concerns with eval() so you should only use it with a trusted source.

Here is my example, where I first use HttpGET() in AMPscript to retrieve JSON from my trusted source.

%%[ set @JSON = HttpGET("http://some-url-that-returns-json") ]%% 

<script runat="server">
    Platform.Load("Core", "1")
    // get JSON from ampscript
    var jsonObj = Variable.GetValue("@JSON");
    var evaluatedJSON = eval("(" + jsonObj + ")");
</script>
  • Thanks for the reply @jsakas. Seems like SSJS is the way to go, will experiment with this. – xtianjs May 13 '14 at 13:04
  • 2
    Something of interest: while you cannot build the equivalent of buildrowsetfromXML for JSON, you can make you landing pages (at creation time) to be of type JSON. You can then use an HTTPGet on the ET landing page to get your JSON. – Timothy May 13 '14 at 14:47
  • Hi, I ended up avoiding an extra layer of complexity with the SSJS. I would probably give it a shot on a landing page, but my paste html email already has an ampscript and DE layer. I ended up using the buildrowsetfromstring function and was able to loop through and get the content I needed using the regexmatch function. Thanks for the replies. – xtianjs May 14 '14 at 0:30
  • You should always avoid SSJS in an email. To parse JSON, use GTL as Adam explains in his answer. There is a JSON.parse() equivalent in SFMC SSJS you can use in landing pages and script activities that you call like: Platform.Function.ParseJSON(str) developer.salesforce.com/docs/… – Sam Whitmore Aug 5 '18 at 18:35
9

Here's a simple Guide Template Language 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 

NOTE: This doesn't appear to work in Content Builder.

4

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

"[{\"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}}
2

I use the SSJS function Platform.Function.ParseJSON.

<script runat="server">
     var str = '{ "prop1": "propVal" }';
     var obj = Platform.Function.ParseJSON(str);
     var val = obj.prop1;
</script>

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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