Can AMPScript parse JSON?

Question

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.

Answer 1

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>

Answer 2

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.

Answer 3

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>

Answer 4

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}}

Answer 5

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

]%%