Can't get GTL to loop through a JSON array of strings without keys (unordered)

Question

I've been having a lot of success parsing JSON with GTL for emails, but there's something that I just cannot get to work.

Given the following JSON

{
  "category1": "something",
  "category2": "something else",
  "people": [
    "Yehuda Katz",
    "Alan Johnson",
    "Charles Jolley"
  ]
}

I want to be able to iterate through the list of people. My sample code looks something like:

%%[ var @Json set @Json = '{"category1":"something","category2":"something else","people": ["Yehuda Katz","Alan Johnson","Charles Jolley"]}' ]%%
{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            { "target" : "@Json" }
  {{/data}}
            {{.datasource JSONpeople type=nested maxRows = 10}}
              {{.data}}
                        { "target" : "JsonVar.people" }
              {{/data}}
                  Json People is: ????????
            {{/datasource}}
{{/datasource}}

Currently I just cannot find any code that will display those names.

In particular, I've played around with the {{#each}} tag (https://developer.salesforce.com/docs/atlas.en-us.mc-programmatic-content.meta/mc-programmatic-content/gtlEach.htm) and it works when I have something to key off like in:

{{#each JsonVar}}

{{SomeValue}}

{{/each}}

That won't work, because there isn't a value to put in the loop.

The docs reference using either

{{.this}}

or

{{this}}

or

{{.}}

But none work. At best it will literally say {{.this}} and not treat it as a variable, but whenever I use {{.}} it completely blows up the email rendering with the error:

"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: 111111 JobID: 0 ListID: 48 Content Begins With: Invalid Content:
..."

I also do not have the ability to change the incoming JSON, and its frustrating because the docs definitely indicate this is possible. Anyone have any thoughts?

Answer 1

Since you don't have an option to change the structure of the json before it is saved in to a data extension. You would have to use Regex to convert the array of strings into an array of objects. It seems to me that SFMC only accept array of objects and each object of the arrays must have the same number of keys and the keys must be them same across all objects and that SFMC treat arrays of non objects as empty array. I changed your json a little for testing purpose as it technically has the same structure as the json you had in the question.

There are 2 limitations with the approach of using REGEX to convert the json in to a new structure:

• The json can not be multi-lined otherwise the regex won't work. Since the json is saved in a data extension from an external source, it would be unnecessary if the developer converts the json to multiline before sending to SFMC DE. As you notice I intentionally declare the json on 1 line instead of on multi lines.
• All the names in the array should not contain " and/or , otherwise the current regex will break. It can be solved with a more sophisticated regex pattern than the one I have.

Please see comments in my code and debugger section as my walk-through explanation.

%%[
var @Json 
set @Json = '{"city":"San Jose","State":"California","people":["Yehuda Katz","Alan Johnson","Charles Jolley"],"info":[{"num":30},{"num":31},{"num":32}]}'
output(concat('====== ', 'DEBUGGER', ' =======', '<br>'))
output(concat('<br>', @Json, '<br>'))
/* Use regex to get key "people" and its array */
set @people = RegExMatch(@Json, '"people"\s*:\s*\[(".*",*)\]', 0, "IgnoreCase", 'Multiline')
output(concat("<br>match people key value pair: ", @people, '<br>'))
/* Use regex to only get the array */
set @array = RegExMatch(@people, '\[.+\]', 0, "IgnoreCase")
/* Build row set using comma delimiter */
set @array = BuildRowsetFromString(@array,',')
/* Loop through the row set to replace the string name with an object with key "name" */
for @i = 1 to Rowcount(@array) do
  Set @row = Row(@array, @i)
  /* Use ".*" pattern to ignore [ and ] in first and last element of array respectively */
  Set @name = RegExMatch(Field(@row,1), '".*"', 0, "IgnoreCase")
  set @replacement = concat('{"name":', @name, '}')
  set @Json = replace(@Json, @name, @replacement)
  output(concat('<br>', 'Replace ', @name, ' with <b>', @replacement,'</b><br>'))
next @i
output(concat('<br>', 'Converted Json: <b>', @Json,'</b><br>'))
output(concat('<br>','====== ', 'Result', ' =======', '<br>'))
]%%
<br>
{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
    { "target" : "@Json" }
  {{/data}}
    City: {{city}} <br>
    State: {{state}} <br>
    People: <br>
    <ul>
      {{.datasource JSONPeople type=nested maxRows = 10}}
        {{.data}}
          { "target" : "JsonVar.people" }
        {{/data}}
          <li>Name: {{name}}</li>
      {{/datasource}}
    </ul>
    Info:<br>
    <ul>
      {{.datasource JSONAge type=nested maxRows = 10}}
        {{.data}}
          { "target" : "JsonVar.info" }
        {{/data}}
          <li>Number: {{num}}</li>
      {{/datasource}}
    </ul>
{{/datasource}}
<br><br>