Cannot store JSON object in Data Extension

Question

We are storing a JSON object in a Data Extension field, then using GTL to parse the JSON and retrieve nested values in the object. It's working really well. However, the challenge is to get the JSON into the Data Extension field in the first place.

I'm using the putDataExtensionRowByKeyAsync method to upsert my data. My request payload looks like this:

PUT https://www.exacttargetapis.com/hub/v1/dataeventsasync/key:3FB7705B-D4D7-40AB-8FA4-596E47C8C627/rows/id:a9714aff-7b96-4164-8139-49df92606c67
Content-Type: application/json
Authorization: Bearer insertAccessTokenHere
{ 
   "values":{ 
      "contactKey":"a0d1a000001OclAAAS",
      "letterId":1000123,
      "statusCode":200,
      "response":"{\"responseContext\":{\"operationStatus\":\"OK\",\"schemaType\":\"Contacts\",\"schemaVersionNumber\":82,\"schemaContextId\":\"56fb9e2b-9937-e511-ac41-b4b52f5ca704\",\"populateInternalProperties\":false},\"eventInstanceID\":\"6d1dc66b-e34d-43f4-88b7-dbee93829d49\",\"asyncRequestID\":3408,\"requestServiceMessageID\":\"ad9b3e54-73f8-4141-a329-dca7da110912\",\"resultMessages\":[],\"serviceMessageID\":\"0e3cffb4-c0b8-4d1c-890e-674ff1c203f2\"}"
   }
}

I would expect to see the escaped JSON object appearing in the response field in my Data Extension:

{"responseContext":{"operationStatus":"OK","schemaType":"Contacts","schemaVersionNumber":82,"schemaContextId":"56fb9e2b-9937-e511-ac41-b4b52f5ca704","populateInternalProperties":false},"eventInstanceID":"6d1dc66b-e34d-43f4-88b7-dbee93829d49","asyncRequestID":3408,"requestServiceMessageID":"ad9b3e54-73f8-4141-a329-dca7da110912","resultMessages":[],"serviceMessageID":"0e3cffb4-c0b8-4d1c-890e-674ff1c203f2"}

But they are not escaped. Instead the following is stored in the Data Extension:

{\"responseContext\":{\"operationStatus\":\"OK\",\"schemaType\":\"Contacts\",\"schemaVersionNumber\":82,\"schemaContextId\":\"56fb9e2b-9937-e511-ac41-b4b52f5ca704\",\"populateInternalProperties\":false},\"eventInstanceID\":\"6d1dc66b-e34d-43f4-88b7-dbee93829d49\",\"asyncRequestID\":3408,\"requestServiceMessageID\":\"ad9b3e54-73f8-4141-a329-dca7da110912\",\"resultMessages\":[],\"serviceMessageID\":\"0e3cffb4-c0b8-4d1c-890e-674ff1c203f2\"}

Obviously we need to escape the name/value pairs in the payload request otherwise my request won't be valid. Any suggestions?

Answer 1

I'm not sure why this method does not work. It should. However, I have found a workaround.

Instead of using the putDataExtensionRowByKeyAsync PUT method, you can achieve the same upsert function using the postDataExtensionRowsetByKey.html POST method. While this method is designed for upserting to a batch of Data Extensions, you can just include a single Data Extension in the array.

Here's my request payload:

POST https://www.exacttargetapis.com/hub/v1/dataevents/key:3FB7705B-D4D7-40AB-8FA4-596E47C8C627/rowset
Content-Type: application/json
Authorization: Bearer insertAccessTokenHere

[{ 
    "keys":{
       "id": "41658c06-088e-45cd-a24f-959dfe9a0db9"
       },
   "values":{  
      "contactKey":"a0d1a000001OclAAAS",
      "letterId":1000999,
      "statusCode":200,
      "response":"{\"responseContext\":{\"operationStatus\":\"OK\",\"schemaType\":\"Contacts\",\"schemaVersionNumber\":82,\"schemaContextId\":\"56fb9e2b-9937-e511-ac41-b4b52f5ca704\",\"populateInternalProperties\":false},\"eventInstanceID\":\"6d1dc66b-e34d-43f4-88b7-dbee93829d49\",\"asyncRequestID\":3408,\"requestServiceMessageID\":\"ad9b3e54-73f8-4141-a329-dca7da110912\",\"resultMessages\":[],\"serviceMessageID\":\"0e3cffb4-c0b8-4d1c-890e-674ff1c203f2\"}"
   }
}]

When I use this method, the JSON data is serialized into the response field in the Data Extension without escape characters.