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.

2
  • 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? Dec 17, 2018 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, 2018 at 21:22

1 Answer 1

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.

1
  • 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, 2018 at 22:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .