How to efficiently ingest JSON and write it to a data extension? Marketing Cloud

Question

I have a cloud page which call a REST API. I need to ingest it and storing in a DE using SSJS. Shall I stringify it and store it to my DE or is better to parse it using parseJSON? Thank you

Answer 1

The question is what you want to do with it. Sometimes it might be better to have it in a DE in parsed fashion, maybe so it is more "Human readable" or can be used with AMPScript, sometimes the full JSON might be handier, and you could run GTL (Guide Template Language) on it to parse inside e.g. an email.

For the full JSON solution, stringify it to write into the DE. Create the DE via API with no field length specified in order to circumvent the 4000 char maxlength.

In case you do want to parse and not just ingest the full JSON - this example worked for me:

sample JSON

{ 
   "page":[ 
      { 
         "storeID":"1007",
         "name":"myStore"
      }
   ]
}

parser function:

<script runat="server">
Platform.Load("Core", "1")
var url = "JSON FILE HOSTED ON A URL";
var headerNames = ["MyTestHeader1", "MyTestHeader2"];
var headerValues = ["MyTestValue1", "MyTestValue2"];
var response = HTTP.Get(url, headerNames, headerValues);
var array = Platform.Function.ParseJSON(response.Content);


var storesDE = "THIS IS A DATA EXTENSION NAME WHICH CONTAINS THE FIELDS: storeID,Name";

var numberofStores = array.page.length;
//Write(array.page.length);

var i = 0;
for (var i = 0; i < numberofStores; i++) {
    var storeID = array.page[i].storeID;
    var name = array.page[i].name;
    //Write(img_url);
    var rows = Platform.Function.UpsertData(storesDE,["storeID"],[storeID],["name"],[name]);
}

//Write(response.Status + '<br />');
//Write(response.Content);

</script>

Hope this works for you. :)