Using SFMC to pull content from a API with JSON response

Question

Our client has setup a URL which contains product number which returns the below JSON for each product.

Is it possible in SFMC to simply pass into the email creative the productid and it submits an API to grab this JSON data and then only display basic parts (product name, product price, product picture etc?)

Here is the entire code that is pulled back from the URL:

{"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.ourwebsite.com/medias/static_content/product/images/large/41/21/A814121.jpg","thumbnail":"http://www.ourwebsite.com/medias/static_content/product/images/thumb/41/21/A814121.jpg"},{"standard":"http://www.ourwebsite.com/medias/static_content/product/images/large/41/27/A814127.jpg","thumbnail":"http://www.ourwebsite.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.ourwebsite.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.ourwebsite.com/modal/faqs"},{"name":"Payments + Delivery","url":"http://www.ourwebsite.com/modal/payment-delivery"},{"name":"Refunds + Returns","url":"http://www.ourwebsite.com/modal/refunds-returns"}],"code":"52790027"}

Any help is much appreciated - I can only get it to retrieve the entire HTML page and it doesn't recognise it contains any data - just that it is a HTML string.

Answer 1

So I have managed to scrape the page off the content whilst ensuring SFMC knows that it is JSON data.

%%[ var @Json
set @Json = HTTPGet("https://www.ourwebiste/productid",false,0,@CallStatus) ]%%

{{.datasource JSONVar type=variable maxRows = 20}}
     {{.data}}
    { "target" : "@Json" }
     {{/data}}
     Name: {{name}}
     productcode: {{baseProductCode}}
Price: {{price}}
THumbnail: {{thumbnail}}

{{/datasource}}

Answer 2

var @dataRow
set @dataRow = AttributeValue("Row1")
set @row2 = AttributeValue("Row2")
set @row3 = AttributeValue("Row3")
set @row4 = AttributeValue("Row4")
set @row5 = AttributeValue("Row5")


]%%

{{.dataobject dataRowVar type=variable}}
   {{.data}}
      {"target":"@dataRow"}
   {{/data}}
{{/dataobject}}

{{.dataobject dataRowVar2 type=variable}}
   {{.data}}
      {"target":"@row2"}
   {{/data}}
{{/dataobject}}

This is pulling in the data:

{{#each dataRowVar}} {{.datasource dataRowVar type=inline}} 
{{.data}} { "target" : "dataRow" } {{/data}}

{{title}}<br>
{{dateCreated}}<br>
{{description}}

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


{{#each dataRowVar2}} {{.datasource dataRowVar2 type=inline}} 
{{.data}} { "target" : "Row2" } {{/data}}
{{title}}


{{title}}<br>
{{dateCreated}}<br>
{{description}}


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