0

I have figured out how to use HttpGet to display JSON directly in an email body, but instead I want to store this JSON data from an external URL in a data extension, and then use AMPscript lookups to populate the email.

I have been able to populate this data extension by hardcoding in the values, using the code below. But I want the values to populate in dynamically from the JSON/Get URL. How do I create variables so that the data extension populates with values from the URL? Example of a field: subcategories[0].activities[0].description

 <script runat="server">
        Platform.Load("Core", "1")
        var response = HTTP.Get('https://myclientURL');
        var jsonObject = eval("(" + response.Content + ")");
            var rows = Platform.Function.InsertData("JSON_Test",["Description","name"]),["?","?"]);
    </script>

Thank you, I am a complete beginner with SSJS.

  • do you mean you want to retrieve a JSON from the URL and then parse through and get values from that JSON to put inside of a DE via an InsertData? – Gortonington Dec 17 '18 at 20:47
  • Hi Gortonington, that's exactly what I am looking to do. Do I need a landing page to do this, in combination with my Script activity? – Kat_43364 Dec 17 '18 at 21:22
1

It is completely possible, but it all depends on how your JSON is set up.

Most of the time you can use the vanilla JS to retrieve values/properties from your JSON

myJSON.Result[0].MyAttr

// myJSON is your JSON that you retrieved
// Result is the first 'category'
// [0] shows it wants to grab the first child (mostly only used in arrays)
// MyAttr is the name of the value you want to grab. E.g. {"MyAttr":"MyValue"}

or similar, but if your JSON is set up differently, the syntax might need to be altered. Check here for a good resource on JSON Objects and interaction via JS. And here for JSON Arrays.

Please note that SFMC SSJS will only let you use vanilla JS and not any add on libraries, etc. Outside of Platform and Core, which are SFMC only libraries.

So, long story short, let's say the below is your returned JSON:

{
    "Results": [{
            "Description": "It looks like a banana that is 90% bruised",
            "name": "The Black Banana of Bermuda"
        },
        {
            "Description": "It shines like justice",
            "name": "Silver Surfer Statue"
        }]
}

You would then do the following to get Description/name from it in SFMC SSJS:

<script runat="server">
  // myJSON is var containing above JSON

    var description = myJSON.Results[0].Description
    var name = myJSON.Results[0].name

    var rows = Platform.Function.InsertData("JSON_Test",["Description","name"]),[description,name]);
</script>

and this will return:

description = "It looks like a banana that is 90% bruised"
name = "The Black Banana of Bermuda"

The [0] selects the first object inside of the array. So if it were changed to [1] then it would instead return:

description = "It shines like justice"
name = "Silver Surfer Statue"

This can be dynamic by using a FOR loop if you need to process more than 1 object in the array.

| improve this answer | |
  • Thank you very much! I like this approach and would like to avoid the landing page if possible. I’ll test and Mark your answer correct. – Kat_43364 Dec 17 '18 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.