Storing JSON API payload in triggered send data extension field

Question

I need to send an order confirmation email using the Transactional Messaging API. The email references the array of ordered items. Rather than store the ordered items as rows in another data extension that we lookup using AMPscript and loop through for each order ID, I'm parsing the JSON using Guide Template Language.

I have created a triggered send data extension and have included an extra text field called Attributes. I've manually added a record to this data extension for testing purposes and the value in the Attributes field is:

{
   "Environment":"Local",
   "FirstName":"Firstname",
   "DeliveryLastName":"Lastname",
   "PurchaseInfo":[
      {
         "ProductCode":"DB001",
         "ProductName":"Book",
         "ProductImageUrl":"https://gallery.mailchimp.com/8aef41944e1b62c25146a4016/images/83e0f82b-7ee8-4903-b2ca-069e919cf693.png",
         "Quantity":4,
         "ProductAmount":7.95
      },
      {
         "ProductCode":"AB001",
         "ProductName":"Magazine",
         "ProductImageUrl":"https://gallery.mailchimp.com/8aef41944e1b62c25146a4016/images/83e0f82b-7ee8-4903-b2ca-069e919cf693.png",
         "Quantity":1,
         "ProductAmount":6.95
      }
   ],
   "ShippingCost":6.6,
   "TotalSavings":3.0,
   "TotalPrice":81.75,
   "VoucherCode":"20PERCENTOFF",
   "DiscountDescriptions":[
      "20% On Books",
      "10% On Magazines"
   ],
   "IncludeShippingAddress":true,
   "CurrencyCode":"GBP",
   "OrderReference":"368517588278312960123",
   "OrderDateUTC":"2021-10-20T13:49:18.037Z",
   "ShippingAddress":{
      "AddressLine1":"AddressLine1",
      "AddressLine2":"AddressLine2",
      "City":"City",
      "State":"State",
      "County":"County",
      "Zip":"Zip",
      "Country":"Country"
   }
}
}
}

I can target the various objects and arrays successfully with Guide Template Language.

%%[
var @json
set @json = Attributes
]%%
{{.datasource Json type=variable}}
  {{.data}}
            { "target" : "@Json" }
  {{/data}}
SOME CONTENT with some {{attribute}}
{{/datasource}}

However:

How can I get the JSON from the API call to populate my triggered data extension field? Do I just need to create an attribute called 'Attributes' and pass the JSON as an attribute?

Bonus question - should I be using SSJS to parse the JSON instead?

Answer 1

There are may be better ways to achieve your requirement, this is how I would tackle.

Key Challenges and solution

1. Fields which needs to store JSON require more than 4000 chars which you can overcome using this data extension: maximum characters of text field

2. In order to store JSON in Data Extension field you MAY have to Escape your JSON. I used This tool

---

Note: There are many sub-JSON in your API call, for this demo I have only used PurchaseInfo, so please replicate this to accomplish your complete solution.

1. Create Data Extension using TSD template with field length blank for the field PurchaseInfo from EmailStudio. Create DE as below

2. Create sample email with below code to Output PurchaseInfo

%%[
var @json
set @json = AttributeValue('PurchaseInfo')
]%%
{{.datasource Json type=variable}}
  {{.data}}
            { "target" : "@Json" }
  {{/data}}

%%[
  SET @ProductCode = TreatAsContent("{{ProductCode}}")
  SET @ProductName = TreatAsContent("{{ProductName}}")
  SET @ProductImageUrl = TreatAsContent("{{ProductImageUrl}}")
  SET @Quantity = TreatAsContent("{{Quantity}}")
  SET @ProductAmount = TreatAsContent("{{ProductAmount}}")
]%%

<p>ProductCode = %%=v(@ProductCode)=%% </p>
<p>ProductName = %%=v(@ProductName)=%% </p>
<p>ProductImageUrl = %%=v(@ProductImageUrl)=%% </p>
<p>Quantity = %%=v(@Quantity)=%% </p>
<p>ProductAmount = %%=v(@ProductAmount)=%% </p>
<hr><hr>
{{/datasource}}

3. Make your REST API call using the escaped JSON string as below.

{
    "definitionKey": "POC_TSD_JSON",
    "recipient": {
        "contactKey": "[email protected]",
        "to": "[email protected]",
        "attributes": {
            "Environment":"Local",
   "FirstName":"Firstname",
   "DeliveryLastName":"Lastname",
   "PurchaseInfo": "[\r\n{\r\n\"ProductCode\":\"DB001\",\r\n\"ProductName\":\"Book\",\r\n\"ProductImageUrl\":\"https:\/\/gallery.mailchimp.com\/8aef41944e1b62c25146a4016\/images\/83e0f82b-7ee8-4903-b2ca-069e919cf693.png\",\r\n\"Quantity\":4,\r\n\"ProductAmount\":7.95\r\n},\r\n{\r\n\"ProductCode\":\"AB001\",\r\n\"ProductName\":\"Magazine\",\r\n\"ProductImageUrl\":\"https:\/\/gallery.mailchimp.com\/8aef41944e1b62c25146a4016\/images\/83e0f82b-7ee8-4903-b2ca-069e919cf693.png\",\r\n\"Quantity\":1,\r\n\"ProductAmount\":6.95\r\n}\r\n]"

        }
    }
}