24

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.

0

5 Answers 5

16

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>
4
  • Thanks for the reply @jsakas. Seems like SSJS is the way to go, will experiment with this.
    – xtianjs
    May 13, 2014 at 13:04
  • 3
    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, 2014 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, 2014 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/… Aug 5, 2018 at 18:35
16

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

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

2020210 EDIT: I don't recommend using GTL in SFMC. My preferred method is what SebOB described.

20210629 EDIT: Does work in CloudPages with a hack.

1
  • Guide Template Language generally works for me in Content Builder on an initial configuration, but if you go back into the same content block to edit in Content Builder, you'll find pieces of GTL code missing. So you need to save all your code elsewhere, in a text editor, and copy and paste it all in each time and save. That's the only issue I've had with GTL and Content Builder. Not sure if there was some other issue. My use case was very similar to the example given here - I used this code to display a list of products stored in a JSON object, in an order dispatch email. May 26, 2022 at 12:41
10

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>
2
  • 1
    This is my preferred method for parsing JSON. Apr 1, 2020 at 17:58
  • 1
    Not that in certain contexts (like if you're developing your script in CloudPages), you'll need to add Platform.Load("core","1.1.5") (version number may differ) under the opening script tag. Sep 9, 2020 at 18:39
6

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

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

In the Marketing Cloud Summer '23 release, there is a new function named BuildRowsetFromJson(1,2,3). It works very similar to BuildRowsetFromXml.

Parameter 1 - Source string

Parameter 2 - JSON Path string which specifies what to build the rowset from

Parameter 3 - If true, returns an empty set on error instead of throwing

%%[

var @json, @rowCount

set @json = ""
set @json = concat(@json, '{ "store": {')
set @json = concat(@json, '    "book": [')
set @json = concat(@json, '        { "category": "reference",')
set @json = concat(@json, '        "author": "Nigel Rees",')
set @json = concat(@json, '        "title": "Sayings of the Century",')
set @json = concat(@json, '        "price": 8.95')
set @json = concat(@json, '        },')
set @json = concat(@json, '        { "category": "fiction",')
set @json = concat(@json, '        "author": "Evelyn Waugh",')
set @json = concat(@json, '        "title": "Sword of Honour",')
set @json = concat(@json, '        "price": 12.99')
set @json = concat(@json, '        }')
set @json = concat(@json, '    ]')
set @json = concat(@json, '}}')

  set @books = BuildRowsetFromJSON(@json,"$.store[0].book[*]",1)
  set @rowCount = rowcount(@books)

  if @rowCount > 0 then

    for @i = 1 to @rowCount do

      SET @title = Field(Row(books,@i),'title')

      ]%%

       <br><p>Title: %%=v(@title)=%%</p>

      %%[

    next @i

  else

   output(concat("<br>no books found"))

  endif

]%%
3
  • Hi Logan, it looks cool! It hasn't been released yet right?
    – Duc Le
    May 19, 2023 at 0:54
  • Not yet - it is in the Summer '23 release, which rolls out June 3rd through June 23rd. Release notes for this function are here: help.salesforce.com/s/…
    – Logan B.
    May 19, 2023 at 12:41
  • 1
    var @title = Field(Row(books,@i),'title') should be set @title = Field(Row(@books,@i),'title') Jun 7, 2023 at 14:39

You must log in to answer this question.

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