[SalesForce] GTL and multi nested JSON

I need to send an email and update a data extension based on JSON content. I've written code combining GTL and AMPscript but I keep getting errors. And also since this is my first time using GTL and second time using AMPscript I am not sure that the code is correct.

I need to use GTL and if is the case to use also AMPscript.

This is my error:

The subscriber preview failed to generate. Review the details, correct all issues, and try again.
Text Version
There is an error in your email. Please contact your customer service representative.
Error 1: Error Loading Email Content
An error occurred when attempting to parse HtmlEmailBody content for HTML content.
MemberID: 10985802
JobID: 0
ListID: 3344
Content Begins With: %%[ var @Json set @Json = ' [{"Customer": [{"CustomerID": "1657"

This is my code:

%%[
var @Json set @Json = ' [{"Customer": [{"CustomerID": "1657","CompanyName": "AMO Food Market","Industry": "Retail","ContactName": "Anne Mollin","ContactTitle": "Marketing Manager","Email": "anne.mollin@AMOFoodMarket.com","FullAddress": {"Address": "2732 Daver Str.","City": "Bandie","Region": "OR","PostalCode": "94215"}},{"CustomerID": "1986","CompanyName": "Folimi Tires","Industry": "Cars","ContactName": "Adrian Moods","ContactTitle": "Sales Representative","Email": "adrian.moods@folimitires.com","FullAddress": {"Address": "City Mall 100 Main St.","City": "Dondie","Region": "OR","PostalCode": "93210"}}]}}] '
]%%

{{.datasource JSONVar type=variable source=@Json maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}
            {{.datasource Customer type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.Customer" }
              {{/data}}
                        {{Customer.CompanyName}}<br>
                        {{Customer.CustomerID}}<br>
                        {{Customer.Industry}}<br>
                        {{Customer.ContactName}}<br>
                        {{Customer.ContactTitle}}<br>
                        {{Customer.Email}}<br>

                 {{.datasource Address type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "JsonVar.FullAddress" }
                   {{/data}}
                        {{FullAddress.Address}}<br>
                        {{FullAddress.City}}<br>
                        {{FullAddress.Region}}<br>
                        {{FullAddress.PostalCode}}<br>

            {{/datasource}}
{{/datasource}}

%%[
  var @CustomerID, @CompanyName, @Industry, @ContactName, @ContactTitle, @Email, @Address, @City
  var @Region, @PostalCode, @FirstName, @LastName, @index 
  set @ContactName = {{JSONCustomer.ContactName}}


  if indexOf(@ContactName, " ") > 0 THEN

        set @FirstName = substring(@ContactName,1,subtract(indexOf(@ContactName," "),1))
        set @LastName = substring(@ContactName, add(indexOf(@ContactName," "),1),length(@ContactName))
        UpsertDE("AMPscript Functions 3 - v3",1,"CustomerID", @CustomerID, "CompanyName", @CompanyName, "Industry", @Industry, "FirstName", @FirstName, "LastName", @LastName, "ContactTitle", @ContactTitle, "Email", @Email, "Address", @Address, "City", @City, "Region", @Region, "PostalCode", @PostalCode)

  endif
]%%

GTL - Hello 2 %%=v(@FirstName)=%%.

Welcome to our community.

Have a great day!

This email was sent by: %%Member_Busname%%, %%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%
%%profile_center_url%%
{{> _footer}}

This is the updated version of my code

%%[
var @Json 
set @Json = ' {
   "Customers": {
      "CustomerCustomerID1657":
      [{
            "CompanyName": "AMO Food Market",
            "Industry": "Retail",
            "ContactName": "Anne Mollin",
            "ContactTitle": "Marketing Manager",
            "Email": "calin.bostan@osf-global.com",
            "FullAddress": {
               "Address": "2732 Daver Str.",
               "City": "Bandie",
               "Region": "OR",
               "PostalCode": "94215"
               }
         }],
      "CustomerCustomerID1657":
      [{
            "CompanyName": "Folimi Tires",
            "Industry": "Cars",
            "ContactName": "Adrian Moods",
            "ContactTitle": "Sales Representative",
            "Email": "adrian.moods@folimitires.com",
            "FullAddress": {
               "Address": "City Mall 100 Main St.",
               "City": "Dondie",
               "Region": "OR",
               "PostalCode": "93210"
            }
         }]
   }
} '
]%%

{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}


            {{.datasource JsonCustomerID type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.CustomerID" }
              {{/data}}
                    <br>JsonCustomer.CompanyName: {{CustomerID.CompanyName}}
                    <br>JsonCustomer.CustomerID: {{[CustomerID.-CustomerID]}}
                    <br>JsonCustomer.Industry: {{CustomerID.Industry}}
                    <br>JsonCustomer.ContactName: {{CustomerID.ContactName}}
                    <br>JsonCustomer.ContactTitle: {{CustomerID.ContactTitle}}
                    <br>JsonCustomer.Email: {{CustomerID.Email}}

                 {{.datasource JsonFullAddress type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "JsonVar.FullAddress" }
                   {{/data}}
                    <br>JsonFullAddress.Address: {{FullAddress.Address}}
                    <br>JsonFullAddress.City: {{FullAddress.City}}
                    <br>JsonFullAddress.Region: {{FullAddress.Region}}
                    <br>JsonFullAddress.PostalCode: {{FullAddress.PostalCode}}

%%[
  var @CustomerID, @CompanyName, @Industry, @ContactName, @ContactTitle, @Email, @Address, @City, @Region, @PostalCode, @FirstName, @LastName

  SET @CompanyName = TreatAsContent('{{[CustomerID.CompanyName]}}')
  SET @Industry = TreatAsContent('{{[CustomerID.Industry]}}')
  SET @ContactTitle = TreatAsContent('{{[CustomerID.ContactTitle]}}')
  SET @Email = TreatAsContent('{{[CustomerID.Email]}}')
  SET @Address = TreatAsContent('{{[FullAddress.Address]}}')
  SET @City = TreatAsContent('{{[FullAddress.City]}}')
  SET @Region = TreatAsContent('{{[FullAddress.Region]}}')
  SET @PostalCode = TreatAsContent('{{[FullAddress.PostalCode]}}')


  set @index = IndexOf(@ContactName,' ')
        set @FirstName = Substring(@ContactName,1,@index)
        set @LastName = Substring(@ContactName,@index,Length(@ContactName)) 

]%%

                  {{/datasource}}
            {{/datasource}}
{{/datasource}}





GTL - Hello 2 %%=v(@ContactName)=%%.



Welcome to our community.

Have a great day!

This email was sent by: %%Member_Busname%%, %%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%
%%profile_center_url%%
{{> _footer}}   

Best Answer

I'm not sure what you are trying to achieve here. Note that GTL will loop through all JSON objects in an array, just like an AMPscript process loop does. Your objects in the array don't look quite right. However, if you modify your code to read:

%%[
var @Json 
set @Json = '{
"Customers":[
  {
     "CustomerID":1657,
     "CompanyName":"AMO Food Market",
     "Industry":"Retail",
     "ContactName":"Anne Mollin",
     "ContactTitle":"Marketing Manager",
     "Email":"calin.bostan@osf-global.com",
     "FullAddress":{
        "Address":"2732 Daver Str.",
        "City":"Bandie",
        "Region":"OR",
        "PostalCode":"94215"
     }
  },
  {
     "CustomerID":1658,
     "CompanyName":"Folimi Tires",
     "Industry":"Cars",
     "ContactName":"Adrian Moods",
     "ContactTitle":"Sales Representative",
     "Email":"adrian.moods@folimitires.com",
     "FullAddress":{
        "Address":"City Mall 100 Main St.",
        "City":"Dondie",
        "Region":"OR",
        "PostalCode":"93210"
     }
   }
  ]
}'

]%%

{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            {"target" : "@Json"}
  {{/data}}


            {{.datasource Customers type=nested maxRows = 20}}
              {{.data}}
                        { "target" : "JsonVar.Customers" }
              {{/data}}
                    <br>JsonCustomer.CompanyName: {{CompanyName}}
                    <br>JsonCustomer.CustomerID: {{[CustomerID.-CustomerID]}}
                    <br>JsonCustomer.Industry: {{Industry}}
                    <br>JsonCustomer.ContactName: {{ContactName}}
                    <br>JsonCustomer.ContactTitle: {{ContactTitle}}
                    <br>JsonCustomer.Email: {{Email}}

                 {{.datasource JsonFullAddress type=nested maxRows = 20}}
                   {{.data}}
                        { "target" : "Customers.FullAddress" }
                   {{/data}}
                    <br>JsonFullAddress.Address: {{Address}}
                    <br>JsonFullAddress.City: {{City}}
                    <br>JsonFullAddress.Region: {{Region}}
                    <br>JsonFullAddress.PostalCode: {{PostalCode}}



%%[
  var @ContactName, @index, @FirstName, @LastName

  set @contactName = TreatAsContent('{{ContactName}}')

  set @index = IndexOf(@ContactName,' ')
        set @FirstName = Substring(@ContactName,1,@index)
        set @LastName = Substring(@ContactName,@index,Length(@ContactName)) 

]%%

                    <br>Hello %%=v(@FirstName)=%% %%=v(@LastName)=%%.
                    <hr />

                  {{/datasource}}
            {{/datasource}}
{{/datasource}}

...Then this will loop through all objects in your array and display them as:

JsonCustomer.CompanyName: AMO Food Market 
JsonCustomer.CustomerID: 
JsonCustomer.Industry: Retail 
JsonCustomer.ContactName: Anne Mollin 
JsonCustomer.ContactTitle: Marketing Manager 
JsonCustomer.Email: calin.bostan@osf-global.com 
JsonFullAddress.Address: 2732 Daver Str. 
JsonFullAddress.City: Bandie 
JsonFullAddress.Region: OR 
JsonFullAddress.PostalCode: 94215 
Hello Anne Mollin.

----------------------------------------------------

JsonCustomer.CompanyName: Folimi Tires 
JsonCustomer.CustomerID: 
JsonCustomer.Industry: Cars 
JsonCustomer.ContactName: Adrian Moods 
JsonCustomer.ContactTitle: Sales Representative 
JsonCustomer.Email: adrian.moods@folimitires.com 
JsonFullAddress.Address: City Mall 100 Main St. 
JsonFullAddress.City: Dondie 
JsonFullAddress.Region: OR 
JsonFullAddress.PostalCode: 93210 
Hello Adrian Moods.
Related Topic