Below is the sample JSON and the AMPScript to extract the necessary fields from the JSON
{
"_logLevel": "info",
"msg": "attempting to send email to sendgrid",
"awsRegion": "ap-southeast-2",
"functionName": "processNotifications-prod",
"functionVersion": "$LATEST",
"functionMemorySize": "512",
"environment": "prod",
"email": {
"to": "[email protected]",
"from": "[email protected]",
"replyTo": "[email protected]",
"templateId": "d-06b21ede68a2495ab8f48b4ef5eb0e90",
"dynamicTemplateData": {
"user": {
"id": "0dd9fd9e-79a7-4feb-b794-1152fa179737",
"firstName": "FirstName",
"middleName": "",
"lastName": "Lastname",
"addressStructured": {
"unitType": "House",
"streetName": "Suburb",
"streetType": "Road",
"premise": null,
"streetNumber": "4",
"unitNumber": "4",
"block": null,
"floor": null
},
"address": "House 4 4 My Road",
"city": "Dublin",
"postCode": "D15N40T",
"state": "Dublin",
"email": "[email protected]",
"country": "Ireland",
"countryCode": "IE",
"countryAreaCode": "+353",
"dateOfBirth": "01/01/1981",
"mobile": "+353812390316",
"fromCurrency": "AUD",
"toCurrency": "USD",
"transferRange": "0 - 500,000",
"createdAt": "2024-10-31T17:06:06.000Z",
"idVerified": true,
"roleName": "User",
"marketingAgreement": true,
"ofxUserAgreement": true,
"devices": [
"apns-116b3943-4ae9-4bba-aff3-c4ca33f948ca"
]
},
"company": {
"CompanyId": "4e710f61-5574-460f-a26e-5f153036b5b0",
"Name": "1stName LastName"
},
"payload": {
"reference": "Transfer NCP test funding",
"amount": 4.62,
"currency": "EUR",
"userName": "1stName LastName",
"creditedBy": "Jason Xxxxx"
}
}
},
"_tags": []
}
%%[
/*--- assign JSON input---*/
SET @json = AttributeValue("JSON_Data")
/* SET @json = CONCAT('[',@json,']') */
OutputLine(v(@json))
/* get the transactional data */
var @UsernameX, @referenceX, @amountX, @FromCurrencyX, @creditedByX
/* Decompose the payload data into individual attributes */
SET @ToCurrencyX = BuildRowsetFromJSON(@json,'$.email.dynamicTemplateData.user.toCurrency',1)
OutputLine(v(@ToCurrencyX))
SET @ContactGUID = BuildRowsetFromJSON(@json,'$.email.dynamicTemplateData.user.id',1)
OutputLine(v(@ContactGUID))
SET @AccountGUID = BuildRowsetFromJSON(@json,'$.email.dynamicTemplateData.company.CompanyId',1)
OutputLine(v(@AccountGUID))
/* SET @payload = BuildRowsetFromJSON(@json,'$.email',1) */
/* set @rows = BuildRowsetFromJson(@json, '$.[*]', 1) */
/* OutputLine(v(@rows)) */
/* set @rowCount = rowcount(@rows) */
/* OutputLine(v(@rowcount)) */
/* set @payload = field(row(@rows,1),"payload") */
SET @payload = BuildRowsetFromJSON(@json,'$.email.dynamicTemplateData.payload',1)
OutputLine(v(@payload))
/* SET @payload = CONCAT('[',@payload,']') */
/* SET @payloadRS = BuildRowsetFromJSON(@payload,'$.[*]',1) */
/* SET @payloadRowCount = RowCount(@payloadRS) */
/* OutputLine(v(@payloadRowCount)) *
/* SET @Row = Row(@payloadRS,1) */
/* SET @UsernameX = Field(@Row,"Username") */
/* SET @referenceX = Field(@Row,"Reference") */
/* SET @amountX = Field(@Row,"amount") */
/* SET @FromCurrencyX = Field(@Row,"currency") */
/* SET @creditedByX = Field(@Row,"creditedBy") */
]%%
The commented code in the AMPScript are the various attempts to try different options.
Objective is to get subset of the values under the $.email, $.email.dynamicTemplateData.user and $.email.dynamicTemplateData.company nodes and all of the attribute values from the $.email.dynamicTemplateData.payload and I have attempted variations (some commented) in the function call but have not been successful with the responses.
The JSON has no arrays and is uploaded to a DE Text column. The DE is the entry source to test the templated EMail and the AMPScript is a content block referenced inside the email.
I have used the same JSON with and without escape char in different rows in the DE. But the results were the same
I have also tried wrapping the json between [ .. ] but no success (code commented above)
OutputLine(v(@json)) does display the full JSON value as below.
{ "_logLevel": "info", "msg": "attempting to send email to sendgrid", "awsRegion": "ap-southeast-2", "functionName": "processNotifications-prod", "functionVersion": "$LATEST", "functionMemorySize": "512", "environment": "prod", "email": { "to": "[email protected]", "from": "[email protected]", "replyTo": "[email protected]", "templateId": "d-06b21ede68a2495ab8f48b4ef5eb0e90", "dynamicTemplateData": { "user": { "id": "0dd9fd9e-79a7-4feb-b794-1152fa179737", "firstName": "FirstName", "middleName": "", "lastName": "Lastname", "addressStructured": { "unitType": "House", "streetName": "Suburb", "streetType": "Road", "premise": null, "streetNumber": "4", "unitNumber": "4", "block": null, "floor": null }, "address": "House 4 4 My Road", "city": "Dublin", "postCode": "D15N40T", "state": "Dublin", "email": "[email protected]", "country": "Ireland", "countryCode": "IE", "countryAreaCode": "+353", "dateOfBirth": "01/01/1981", "mobile": "+353812390316", "fromCurrency": "AUD", "toCurrency": "USD", "transferRange": "0 - 500,000", "createdAt": "2024-10-31T17:06:06.000Z", "idVerified": true, "roleName": "User", "marketingAgreement": true, "ofxUserAgreement": true, "devices": [ "apns-116b3943-4ae9-4bba-aff3-c4ca33f948ca" ] }, "company": { "CompanyId": "4e710f61-5574-460f-a26e-5f153036b5b0", "Name": "1stName LastName" }, "payload": { "reference": "Transfer NCP test funding", "amount": 4.62, "currency": "EUR", "userName": "1stName LastName", "creditedBy": "Jason Xxxxx" } } }, "_tags": [] }
- Can I use the BuildRowsetFromJSON function to traverse nodes and return the value under the node? In the code above, it does not work for ToCurrency, ContactGUID, etc.
- Can I use the function to return the node sub-structure as a string (e.g. $.email.dynamicTemplateData.payload) and then execute the function over the result to use the Field function to extract the child items?
Update
This is how it is stored in the DE
Thanks.
Yes, I have tested the SSJS option also.
<script runat='server'>
// Load the core library
Platform.Load('Core', '1.1.5');
var jsonDE = Platform.Variable.GetValue('@json');
// parse JSON response
var jsonParsed = Platform.Function.ParseJSON(jsonDE);
Variable.SetValue("@ToCurrencyX", jsonParsed.email.dynamicTemplateData.user.toCurrency);
Variable.SetValue("@FromCurrencyX", jsonParsed.email.dynamicTemplateData.payload.currency);
Variable.SetValue("@referenceX", jsonParsed.email.dynamicTemplateData.payload.reference);
Variable.SetValue("@amountX", jsonParsed.email.dynamicTemplateData.payload.amount);
Variable.SetValue("@UsernameX", jsonParsed.email.dynamicTemplateData.payload.userName);
Variable.SetValue("@creditedByX", jsonParsed.email.dynamicTemplateData.payload.creditedBy);
Variable.SetValue("@AccountGUID", jsonParsed.email.dynamicTemplateData.company.CompanyId);
</script>
I couldn't figure out (searched SF docs and Stackexchange posts) how to assign the JSON column from DE which is the entry source for the journey within the SSJS script. The @json is set up in ane AMPScript block that precedes the SSJS block
