How to access nested JSON (sub-objects) in data

Question

I'm using the dataobject tag to parse JSON that is in a variable as described by:

https://help.exacttarget.com/en-US/documentation/guide_template_language/parsing_json/dataobject_tag_examples/

How do I access nested objects?

%%[
 var @Json2
 set @Json2 = ' { "workInfo" : {"emailaddress":"[email protected] ","Region":"East","State":"North Carolina","City":"Charlotte" }, "homeInfo" : { "emailaddress":"[email protected]", "Region":"East", "State":"North C", "City": "Charlotte" } }' 
]%%
{{.dataobject JsonVar2 type=variable source=@Json2 maxrows=20}}
     {{.data}} 
          {"target": "@Json2"} 
     {{/data}}
{{/dataobject}}
{{#jsonvar2}}
work info : {{workInfo.emailaddress}}

{{/jsonvar2}}

I tried using {{workInfo.emailaddress}} but was not successful.

Answer 1

As part of the January 2016 release SFMC added a "nested" datasource type that can be used to extract nested data objects as you have done here with SSJS. The syntax is a little clunky as it requires you to create a new datasource for each layer of the JSON data that you access. Here is an example of how to use GTL to extract the data from your sample:

%%[
var @Json2
set @Json2 = ' { "workInfo" : {"emailaddress":"[email protected] ","Region":"East","State":"North Carolina","City":"Charlotte" }, "homeInfo" : { "emailaddress":"[email protected]", "Region":"East", "State":"North C", "City": "Charlotte" } }'
]%%

{{.datasource JsonVar2 type=variable source=@Json2 maxrows=20}}
{{.data}}
{"target": "@Json2"}
{{/data}}

    {{.datasource workInfo type=nested maxRows = 20}}
    {{.data}}
    { "target" : "JsonVar2.workInfo" }
    {{/data}}
        work info : {{emailaddress}}<br>
    {{/datasource}}

    {{.datasource homeInfo type=nested maxRows = 20}}
    {{.data}}
    { "target" : "JsonVar2.homeInfo" }
    {{/data}}
        home info : {{emailaddress}}<br>
    {{/datasource}}

{{/datasource}}

And yields the following output:

work info : [email protected] 
home info : [email protected]

Answer 2

I solved this using server side javascript to extract the nested JSON objects and place them into their own AMPScript variables. Once there, I could use dataobject tags. E.g.

<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("@WorkInfo", _JsonObject.workInfo);
    set("@HomeInfo", _JsonObject.homeInfo);
</script>

Then to access them, first declare the dataobject WorkInfoVar

{{.dataobject WorkInfoVar type=variable source=@WorkInfo maxrows=20}}
     {{.data}} 
          {"target": "@WorkInfo"} 
     {{/data}}
{{/dataobject}}

And then use it

{{#WorkInfoVar}} {{emailaddress}} {{/WorkInfoVar}} 

Ditto for HomeInfo.

References: Sharing Variables between Javascript and AMPScript: http://help.exacttarget.com/en/documentation/exacttarget/content/server_side_javascript/using_server_side_javascript_with_ampscript/