Retrieving a third level nested JSON subobject in SFMC

Question

We are trying to retrieve a piece of content from a JSON call but we are unable to successfully retrieve it from a third level nested subjobject.

The following code successfully retrieves from the second subobject but we cannot figure out how to get the third.

%%[ var @Json2 set @Json2 = HTTPGet("https://mywebsite/productid",false,0,@CallStatus) ]%%

{{.datasource JSONVar type=variable maxRows = 20}}
     {{.data}}
    { "target" : "@Json2" }
     {{/data}}
     Name: {{name}}
     productcode: {{baseProductCode}} {{/datasource}}

<script runat=server> function set(key, val) {   var newVal = val != null ? Stringify(val) : "{}";   Variable.SetValue(key, newVal); }
    Platform.Load("Core","1");
    var Json=Variable.GetValue("@Json2");
    var _JsonObject = eval('(' + Json + ')');
    set("@colourVariants", _JsonObject.colourVariants); </script>

{{.dataobject ColorVar type=variable source=@colourVariants maxrows=20}}
     {{.data}} 
          {"target": "@colourVariants"} 
     {{/data}} {{/dataobject}} Here is the price: {{#ColorVar}} {{Price}} {{/ColorVar}}  {{/datasource}}

We are trying to pull out the standard tag which is in the media object, which is a subobject of colorvariants. JSON code is below:

{"baseProductCode":"P52789915","displayColour":"#00AB84","name":"Men's Slip On Safety Shoe Burke - Black","description":"Men's slip on work shoe  featuring a steel toe, a leather upper for durability and a technical cushioned insock to absorb shock when walking.","ratings":2.3333333333333335,"colourVariants":[{"code":"52789915","name":"Men's Slip On Safety Shoe Burke - Black","sizeVariants":[{"code":"52790010","name":"6","price":"$69","inStock":true},{"code":"52790027","name":"7","price":"$69","inStock":true},{"code":"52790034","name":"8","price":"$69","inStock":false},{"code":"52790041","name":"9","price":"$69","inStock":true},{"code":"52790058","name":"10","price":"$69","inStock":true},{"code":"52790065","name":"11","price":"$69","inStock":true},{"code":"52790072","name":"12","price":"$69","inStock":true}],"media":[{"standard":"http://www.mywebsite.com/medias/static_content/product/images/large/41/21/A814121.jpg","thumbnail":"http://www.mywebsite.com/medias/static_content/product/images/thumb/41/21/A814121.jpg"},{"standard":"http://www.mywebsite.com/medias/static_content/product/images/large/41/27/A814127.jpg","thumbnail":"http://www.mywebsite.com/medias/static_content/product/images/thumb/41/27/A814127.jpg"}],"price":"$69","inStock":true}],"features":[{"name":"Materials and Composition","value":["Upper: Leather","Lining: Synthetic","Sole: Synthetic"]},{"name":"Product Features","value":["Leather upper","Steel toe","Technical cushioned insock to absorb shock when walking"]},{"name":"Care Instructions","value":["Machine Washable"]}],"sizeChartUrl":"http://www.mywebsite.com/size-chart/menshoe","deliveryModes":[{"available":true,"description":"Free for orders over $19. Not available to all stores. Allow 4-10 business days before pickup.","name":"Click + Collect","excludesCountryStore":false},{"available":true,"description":"Free for orders over $75 (excluding large items). Allow 3-10 business days for delivery.","name":"Home Delivery","excludesCountryStore":false},{"available":false,"description":"For apparel only orders.  Not available to all locations. Available for orders placed by 12pm Mon to Fri","name":"Express Delivery","excludesCountryStore":false}],"infoLinks":[{"name":"FAQs","url":"http://www.mywebsite.com/modal/faqs"},{"name":"Payments + Delivery","url":"http://www.mywebsite.com/modal/payment-delivery"},{"name":"Refunds + Returns","url":"http://www.mywebsite.com/modal/refunds-returns"}],"code":"52790027"}

Answer 1

I solved this by using Java Server side scripting and looping through the JSON response.

    if(evaluatedJSON.length>1 && path=='True')
    {
        Variable.SetValue(product.concat(count),evaluatedJSON['code']);
        for(var i=0; evaluatedJSON['colourVariants'].length>i; i++)
        {
          Variable.SetValue(name.concat(count),evaluatedJSON['colourVariants'][i].name);
          Variable.SetValue(price.concat(count),evaluatedJSON['colourVariants'][i].price);

            for(var k=0; evaluatedJSON['colourVariants'][i]['media'].length>k; k++)
            {
                 Variable.SetValue(standard.concat(count),evaluatedJSON['colourVariants'][i]['media'][k].standard);
                 Variable.SetValue(thumbnail.concat(count),evaluatedJSON['colourVariants'][i]['media'][k].thumbnail);
                 k = evaluatedJSON['colourVariants'][i]['media'].length;
            }
        }
    }