0

We have succesfully queries our clients site and are pulling JSON product data. However the product price is stored within a child node called media and we do not know how to extract it - we can extract fields from the parent node though. We need to pull out >>media>>price.

Successfully pulling data from parent node:

%%[ 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}}
{{/datasource}}

Here is the JSON data

{"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/image
s/large/41/21/A814121.jpg","thumbnail":"http://www.ourwebsite.com/medias/st
atic_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/sta
tic_content/product/images/thumb/41/27/A814127.jpg"}],"price":"$69","inStoc
k":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"}

Anyone know how to extract the price from the media node?

Thanks for your help Bryan

1

Are you trying to process this in Apex?

If so, dump it into JSON2Apex:

http://pastebin.com/vN64jFsP

Then pull out the fields you want from the class instances.

Price isn't in the media node. (Unless I failed at pasting). Media is just a url, which makes sense really.

Anyway. You can do this:

WebsiteJSON webjson = WebsiteJSON.parse('<your json>');
for (WebsiteJSON.ColourVariants cv : webjson.colourVariants) {
    System.debug('Name: '+cv.name+' Price: '+cv.price);
    for (WebsiteJSON.SizeVariants sv : cv.sizeVariants) {
        System.debug('----Name: '+sv.name+' Price: '+sv.price);
    }
}

Which gives this:

|DEBUG|Name: Men's Slip On Safety Shoe Burke - Black Price: $69
|DEBUG|----Name: 6 Price: $69
|DEBUG|----Name: 7 Price: $69
|DEBUG|----Name: 8 Price: $69
|DEBUG|----Name: 9 Price: $69
|DEBUG|----Name: 10 Price: $69
|DEBUG|----Name: 11 Price: $69
|DEBUG|----Name: 12 Price: $69
  • No I am trying to process this in SFMC (Exact Target) unfortunately. – BDOB83 Oct 25 '15 at 22:08
1

As of the January 2016 release there is a way to parse nested JSON data with GTL using the new "nested" datasource type.

Here is an example that pulls data from 3 different layers of the JSON structure:

%%[
var @Json2 set @Json2 = '{"baseProductCode":"P52789915","displayColour":"#00AB84","name":"Mens Slip On Safety Shoe Burke - Black","description":"Mens 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.33","colourVariants":[{"code":"52789915","name":"Mens 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"}'
]%%

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

    {{.datasource colourVariants type=nested maxRows = 20}}
    {{.data}}
    { "target" : "JsonVar.colourVariants" }
    {{/data}}

        Here is the price: {{Price}}<br>
        Sizes available:
        <ul>

        {{.datasource sizeVariants type=nested maxRows = 20}}
        {{.data}}
        { "target" : "colourVariants.sizeVariants" }
        {{/data}}
            <li>{{code}}: (size {{name}})</li>
        {{/datasource}}

        </ul>

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

In my environment a test send of this email yields the following HTML output:

<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
Name: Mens Slip On Safety Shoe Burke - Black<br>
productcode: P52789915<br>
Here is the price: $69<br>
Sizes available:
<ul>
    <li>52790010: (size 6)</li>
    <li>52790027: (size 7)</li>
    <li>52790034: (size 8)</li>
    <li>52790041: (size 9)</li>
    <li>52790058: (size 10)</li>
    <li>52790065: (size 11)</li>
    <li>52790072: (size 12)</li>
</ul>
0

OK, so I managed to work this out. It doesn't look like there is a standard way of returning a sub object from the JSON data. After some research I found this way from another thread. Is there an easier way?

%%[ 
  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"> 

  Platform.Load("Core","1");

  function set(key, val) {   
    var newVal = val != null ? Stringify(val) : "{}";   
    Variable.SetValue(key, newVal); 
  }

  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}}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.