JSON Parsing in marketing cloud email AMPScript

Question

I have a JSON string which contains a 'body' key as a string, which is an escaped JSON that has keys and arrays. I want to read the JSON and extract the 'body' key and then extract the arrays inside the 'body' key and display the data from 'addresses' inside the email. How can I do this using AMPScript? If not possible in an elegant way in AMPScript, can I do it in SSJS?

{
  "To": {
    "Address": "[email protected]",
    "SubscriberKey": "[email protected]",
    "ContactAttributes": {
      "SubscriberAttributes": {
        "Body": "{\"country\":\"gb\",\"language\":\"en\",\"addresses\":[{\"type\":\"billing\",\"addrees\":\"BillingFirststreet\",\"City\":\"Amsterdam\",\"Country\":\"Netherlands\"},{\"type\":\"shipping\",\"addrees\":\"ShippingFirstlinestreet\",\"City\":\"DenHaag\",\"Country\":\"Netherlands\"}],\"type\":\"Order\",\"items\":[{\"productDisplayName\":\"TEST PRODUCT 1\",\"quantity\":1,\"code\":\"AB-45-EF\",\"price\":29.34,\"currencyCode\":\"EUR\"},{\"productDisplayName\":\"TEST PRODUCT 2\",\"quantity\":1,\"code\":\"AB-48-AF\",\"price\":192.4,\"currencyCode\":\"EUR\"}]}"
      }
    }
  }
}

Unescaped Body string looks like below.

{
  "country": "gb",
  "language": "en",
  "addresses": [
    {
      "type": "billing",
      "addrees": "BillingFirststreet",
      "City": "Amsterdam",
      "Country": "Netherlands"
    },
    {
      "type": "shipping",
      "addrees": "ShippingFirstlinestreet",
      "City": "DenHaag",
      "Country": "Netherlands"
    }
  ],
  "type": "Order",
  "items": [
    {
      "productDisplayName": "TEST PRODUCT 1",
      "quantity": 1,
      "code": "AB-45-EF",
      "price": 29.34,
      "currencyCode": "EUR"
    },
    {
      "productDisplayName": "TEST PRODUCT 2",
      "quantity": 1,
      "code": "AB-48-AF",
      "price": 192.4,
      "currencyCode": "EUR"
    }
  ]
}

I have the below code, Is there a better way to do this?

{{.datasource JSONVar source=@Json type=variable}}
    {{.data}}
        { "target" : "@Json" }
    {{/data}}
    {{.datasource to_var type=nested}}
        {{.data}}
            { "target" : "JSONVar.To" }
        {{/data}}

        {{.datasource cont_attr type=nested}}
            {{.data}}
                { "target" : "to_var.ContactAttributes" }
            {{/data}}
            {{.datasource sub_attr type=nested}}
                {{.data}}
                    { "target" : "cont_attr.SubscriberAttributes" }
                {{/data}}              
%%[
set @payload =TreatAsContent('{{Body}}')
set @payload = ReplaceList(@payload, '', '\')
]%%
                
            {{/datasource}}        
        {{/datasource}}
    {{/datasource}}
{{/datasource}}

<b>Process Payload</b><br>  
{{.datasource body_var type=variable}}
    {{.data}}
        { "target" : "@payload" }
    {{/data}}
    country: {{country}}<br>
    language: {{language}}<br>    
        
    {{.datasource items_var type=nested}}
        {{.data}}
            { "target" : "body_var.addresses" }
        {{/data}}    
      <br><b>Addresses</b><br>
      <br>type: {{type}}
      <br>addrees: {{addrees}}     
      <br>City: {{City}}
      <br>Country: {{Country}} <br>
    {{/datasource}}
{{/datasource}}

Answer 1

As I understand your code, you were doing something in AMPscript to get the JSON response before and this whole JSON response didn't come from the data extension?

Then you can use SSJS to read, process and parse the JSON. For me, SSJS is more flexible and a great choice when doing with JSON.

<script runat="server">
Platform.Load('core', '1.1.1');

var response = Variable.GetValue("@Json");
    response =  Platform.Function.ParseJSON(response)  

var body = response.To.ContactAttributes.SubscriberAttributes.Body;
    body = Platform.Function.ParseJSON(body);

var addresses = body.addresses;
if (addresses && addresses.length > 0) {
    Write("<br><b>Addresses:</b><br>")
    for (var i = 0; i < addresses.length; i ++) {
        Write("<br>type: " + body.addresses[i].type);
        Write("<br>addrees: " + body.addresses[i].addrees);
        Write("<br>City: " + body.addresses[i].City);
        Write("<br>Country: " + body.addresses[i].Country + "<br>");
    }
}
</script>

Answer 2

There is no straightforward method to process JSON in AMPscript, making SSJS a preferable language. Your current code utilizes GTL, which is a Marketing Cloud language that specifically handles JSON parsing, however, I prefer to use SSJS.

This code gets the body variable from your send context using AMPscript, then passes it to SSJS to parse the JSON and output in your email.

%%[
    SET @body = AttributeValue('Body')
]%%
<script runat="server">
    Platform.Load("core", "1.1.1");
    var body = Variable.GetValue("@body");
    var addresses = body.addresses;
</script>
<table>
  <tr>
    <td>Type</td>
    <td>Address</td>
    <td>City</td>
    <td>Country</td>
  </tr>
  <script runat="server">
    for (var i = 0; i < addresses.length; i++) {
      var address = addresses[i];
      var type = address.type;
      var street = address.addrees;
      var city = address.City;
      var country = address.Country;
      Write("<tr><td>" + type + "</td><td>" + street + "</td><td>" + city + "</td><td>" + country + "</td></tr>");
    }
  </script>
</table>