Json parse in emails with ampscript

Question

Im trying to parse some weather json data in my email. But I cant quite fiqure out the syntax. With this code I'm trying to get the main.temp value printed. Below is my code so far, but it dosnt work :-(

%%[ 
var @Json 
set @Json = '{
"weather": [
    {
        "id": 800,
        "main": "Clear",
        "description": "clear sky",
        "icon": "01n"
    }
],
"main": {
    "temp": 275.66,
    "pressure": 1022,
    "humidity": 80,
    "temp_min": 272.15,
    "temp_max": 278.15
},
"sys": {
    "type": 1,
    "id": 5245,
    "message": 0.0065,
    "country": "DK",
    "sunrise": 1459485462,
    "sunset": 1459532996
 }
}'
]%%
{{.dataobject JsonVar type=variable source=@Json maxrows=20}}
   {{.data}} 
      {"target":"@Json"} 
   {{/data}}
{{/dataobject}}
{{#jsonvar}}
   {{#main}}
      {{temp}}
   {{/main}}
{{/jsonvar}}

Answer 1

You have to define another dataSource for the sub-object:

%%[
var @Json
set @Json = '{
   "weather":[
      {
         "id":800,
         "main":"Clear",
         "description":"clear sky",
         "icon":"01n"
      }
   ],
   "main":{
      "temp":"275.66",
      "pressure":1022,
      "humidity":80,
      "temp_min":272.15,
      "temp_max":278.15
   },
   "sys":{
      "type":1,
      "id":5245,
      "message":0.0065,
      "country":"DK",
      "sunrise":1459485462,
      "sunset":1459532996
   }
}'

]%%
{{.dataobject JsonVar type=variable maxrows=20}}
   {{.data}}
      {"target":"@Json"}
   {{/data}}
{{/dataobject}}
<tt>
@Json: %%=v(@Json)=%%
{{#each JsonVar}}

  {{.datasource JsonMain type=nested maxRows = 10}}
    {{.data}}
              { "target" : "JsonVar.main" }
    {{/data}}
    <br><br>JsonMain.temp: {{JsonMain.temp}}
    <br>JsonMain.pressure: {{JsonMain.pressure}}
    <br>JsonMain.humidity: {{JsonMain.humidity}}
    <br>JsonMain.temp_min: {{JsonMain.temp_min}}
    <br>JsonMain.temp_max: {{JsonMain.temp_max}}
  {{/datasource}}
{{/each}}
</tt>

Output:

@Json: { "weather":[ { "id":800, "main":"Clear", "description":"clear sky", "icon":"01n" } ], "main":{ "temp":"275.66", "pressure":1022, "humidity":80, "temp_min":272.15, "temp_max":278.15 }, "sys":{ "type":1, "id":5245, "message":0.0065, "country":"DK", "sunrise":1459485462, "sunset":1459532996 } }

JsonMain.temp: 275.66
JsonMain.pressure: 1022
JsonMain.humidity: 80
JsonMain.temp_min: 272.15
JsonMain.temp_max: 278.15

Answer 2

I found a solution using server side javascript :

%%[ @JSON,@ListID,lname1
set @JSON = HttpGET('https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA')

]%%
%%=v(@JSON)=%%


<br>
<br>
<br>

<script runat="server">
    Platform.Load("Core", "1")
    // get JSON from ampscript
    var jsonObj = Variable.GetValue("@JSON");
    var obj = Platform.Function.ParseJSON(jsonObj);
    var Adresse = obj.results[0].formatted_address ;
    var val = obj.results[0].address_components[0].long_name ;
    Variable.SetValue("@ListID",Adresse);
    Variable.SetValue("@lname1",val);
   // var evaluatedJSON = eval("(" + address_components + ")");
  // %%=v(@JSON)=%%

</script>
Adresse : %%=v(@ListID)=%%
<br>
<br>
longName 1 : %%=v(@lname1)=%%

<br>
<br>
<br>