Parsing through JSON

Question

I have submitted a Json string to a data extension under the field name of "products" and can see the data within Marketing Cloud:

Example data:

[{"name": "Lunch1", "quantity": "1", "bonus_products": []}, {"name": "Lunch2", "quantity": "1", "bonus_products": []}, {"name": "Lunch3", "quantity": "1", "bonus_products": [{"name": "Baked Beans"}, {"name": "Potatoes"}]}]

What I'd like to do is output the name, quantity, and also all the bonus products in my email. I'm able to output the name, and quantity, but am struggling to identify the best way to iterate through the bonus_products, which needs to be decoded

This is my code:

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

{{.dataobject JsonVar type=variable source=@json maxrows=20}}
       {{.data}}
            {"target":"@Json"}
       {{/data}}
    {{/dataobject}}
    {{#each JsonVar}}
       <p>{{name}}, {{quantity}}, {{bonus_products}}</p>
    {{/each}}

Any advice would be greatly appreciated. Thanks,

Answer 1

You've got the right idea. Here's some working code based on your example.

%%[
var @json
set @json = '[
   {
      "name":"Lunch1",
      "quantity":1,
      "bonus_products":[]
   },
   {
      "name":"Lunch2",
      "quantity":2,
      "bonus_products":[]
   },
   {
      "name":"Lunch3",
      "quantity":3,
      "bonus_products":[
         {
            "name":"Baked Beans"
         },
         {
            "name":"Potatoes"
         }
      ]
   }
]'
]%%

{{.datasource obj type=variable}}
   {{.data}}
     {"target":"@json"}
   {{/data}}

<p>{{name}}, {{quantity}}
    {{.datasource bonus_products type=nested}}
       {{.data}}
          { "target": "obj.bonus_products" }
       {{/data}}, {{name}}
    {{/datasource}}
{{/datasource}}</p>

This code will produce the following output:

Lunch1, 1

Lunch2, 2

Lunch3, 3 , Baked Beans , Potatoes

To trim the commas, you'll need to strip the spaces from your GTL code. Changing the p element to a single line:

<p>{{name}}, {{quantity}}{{.datasource bonus_products type=nested}}{{.data}}{"target": "obj.bonus_products"}{{/data}}, {{name}}{{/datasource}}
{{/datasource}}</p>

...will produce the following output:

Lunch1, 1

Lunch2, 2

Lunch3, 3, Baked Beans, Potatoes

Answer 2

You will need to do a 'nested datasource' as seen in the docs.

See this example they give in the docs for reference:

%%[ var @Json set @Json = ' [{ "emailaddress": "[email protected]", "Region": "West", "State": "California", "City": "San Francisco", "PhoneNumbers": [{ "Type": "Home", "Number": "555-555-1111" }, { "Type": "Cell", "Number": "555-555-2222" }]}, { "emailaddress": "[email protected]", "Region": "Central", "State": "Indiana", "City": "Indianapolis", "PhoneNumbers": [{ "Type": "Home", "Number": "555-555-4444" }, { "Type": "Cell", "Number": "555-555-5555" }]}]' ]%%

{{.datasource JSONVar type=variable maxRows = 20}}
  {{.data}}
            { "target" : "@Json" }
  {{/data}}
            Email Address: {{emailaddress}}
            Region: {{region}}
            State: {{STATE}}
            City: {{JSONVar.City}}
            {{.datasource JSONPhone type=nested maxRows = 10}}
              {{.data}}
                        { "target" : "JsonVar.PhoneNumbers" }
              {{/data}}
                        {{JSONPhone.Type}}: {{JSONPhone.Number}}           
            {{/datasource}}
{{/datasource}}

Guessing yours would wind up being something like:

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

{{.datasource JsonVar type=variable maxrows=20}}
       {{.data}}
            {"target":"@Json"}
       {{/data}}
       <p>{{name}}, {{quantity}} 
      {{.datasource JSONBonusProducts type=nested maxRows = 10}}
         {{.data}}
                 { "target": "JSONVar.bonus_products" }
         {{/data}}
          , {{JSONBonusProducts.name}}
      {{/datasource}}
</p>
{{/datasource}}

Might need to fiddle with the formatting to get it to display right, but should be something like that.