Help with saving JSON nested array into a single text field in a Data Extension when using the Transactional API in Marketing Cloud

Question

I'm testing out email sends using the Transactional API in Marketing Cloud. I have configured a data extension to save the payload to. Everything is working, however I have run into a problem when trying to save a JSON nested array into a single text field in the data extension. It seems the nest array has to be serialized in order to save correctly to the data extension.

The data extension has a field called Products which is a text field with no character limit. I am using Postman to trigger the sends.

Scenarios:

• If I send the first payload below (non-serialised), the send works and I get the email. Everything saved correctly to the data extension EXCEPT the Products field is empty.
• If I send the second payload below (serialised), the send works and I get the email. Everything saved correctly to the data extension and the Products field is populated.

I've tried to find a reference to this behaviour in the documentation but not had much luck.

Is anyone able to please confirm if JSON nested arrays have to be serialized in order to save to a data extension field?

Thanks

---

Non-serialized Array - Does Not Work

{
  "definitionKey": "Test_Definition_Key",
  "recipient":
  {
   "contactKey": "12345",
    "to": "test@email.com",
    "attributes": {
      "FirstName": "Joe",
      "LastName": "Bloggs",
      "Products": [
        {
            "productUrl":"https://www.google.com",
            "productName":"Product 1",
            "voucherNumber":"ABCDE12345"
        },
        {
            "productUrl":"https://www.google.com",
            "productName":"Product 2",
            "voucherNumber":"ABCDE12346"
        },
        {
            "productUrl":"https://www.google.com",
            "productName":"Product 3",
            "voucherNumber":"ABCDE12347"
        }
    ]
    }
  }
}

Serialized Array - This Works

{
  "definitionKey": "Test_Definition_Key",
  "recipient":
  {
   "contactKey": "12345",
    "to": "test@email.com",
    "attributes": {
      "FirstName": "Joe",
      "LastName": "Bloggs",
      "Products": "[{\"productUrl\":\"https://www.google.com.au/\",\"productName\":\"Test Product 1\",\"voucherNumber\":\"ABCDE12345\"},{\"productUrl\":\"https://www.google.com.au/\",\"productName\":\"Test Product 2\",\"voucherNumber\":\"ABCDE12346\"},{\"productUrl\":\"https://www.google.com.au/\",\"productName\":\"Test Product 3\",\"voucherNumber\":\"ABCDE12347\"}]"
    
    }
  }
}

Answer 1

The issue is that all attributes and 'fields' passed into SFMC need to be non-object data types (strings, numbers, etc.). This is why in order for it to be accepted, the array needs to be escaped to a string data type via your Serialized Array.

This is not explicitly stated anywhere in the documents, but it is essentially the same thing as if you try to 'Write' or output directly an object on a page, it will return null value as there is nothing that is visible to be displayed unless you Stringify (or otherwise turn it into a String data type) first.

Long story short, object data types are not read as 'visual' values that can be stored inside the SFMC data storage unless it is translated to a string data type first. It is instead viewed as null or empty.