0

I've got some nested Json that's using a dictionary with dynamic keys and values - I don't know which key-value pairs will appear in the payload but I need to print them all in an email message.

The FOR IN loop in SSJS will handle this perfectly and my code below works (when I'm hard-coding in the array I need), but I need to dynamically pull in this nested 'MessageData' array that's being stored in my data extension and I'm unsure on how to do this.

<script runat="server">
Platform.Load("core","1");
var MessageData = {"UserId":12345678,"FirstName":"Example","Event":"SampleEvent"};
for (i in MessageData) {
  Write('' +i+ ' = ' +MessageData[i]+ '<br>');
}
</script>

//output
UserId = 12345678
FirstName = Example
Event = SampleEvent

My payload looks like this (this is stored in a Data Extension field with no character limit and is populated via Transactional Messaging API):

{"FirstName":"Joe","EventDescription":"Great Holiday","FaultDateUTC":"2023-03-09T00:00:00.000Z","MessageData":{"UserId":12345678,"FirstName":"Example","Event":"SampleEvent"}}

How do I best target just the MessageData section to use it in my SSJS code? I have previously been using Guide Template language successfully but trying to do a for in loop in GTL doesn't appear straightforward.

1 Answer 1

1

I believe you would need to parse the JSON first and then use a loop to write keys and values in the nested JSON.

Try something like this in the email:

(assuming the DE is using as the data source in the email send and you have a field called "json" in that DE storing the payload)

%%[
    set @json = AttributeValue("json")
]%%

<script runat="server">
Platform.Load("Core","1.1.1");

var data = Variable.GetValue("@json");

var parsedJSON = Platform.Function.ParseJSON(data);

var MessageData = parsedJSON.MessageData;
for (i in MessageData) {
  Write('' + i + ' = ' + MessageData[i]+ '<br>');
}

</script>
6
  • Thanks for this, but I get an error saying that 'Fields with MAX or unlimited length cannot be used as keys'. Commented Mar 10, 2023 at 13:51
  • I guess the error shows up due to the data extension json field with no character limit (unlimited length). Maybe you would need to add a specific length for that column
    – Duc Le
    Commented Mar 10, 2023 at 13:59
  • If you were using a filter column has unlimited length in the LookupRows function, this may cause this issue salesforce.stackexchange.com/a/352850/83900
    – Duc Le
    Commented Mar 10, 2023 at 14:02
  • I've changed Data Extension to not be unlimited characters (as for this payload, it won't ever exceed 4,000 characters anyway). But what would my filter value be? I am able to print a string for my entire payload using an AMPscript variable but am unsure on how to split it so I only reference the portion I need for the MessageData array as shown in question. Commented Mar 10, 2023 at 14:48
  • 1
    This updated code works a charm. Thanks so much. Commented Mar 10, 2023 at 18:49

You must log in to answer this question.

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