Filtering or Targeting Specific Node in JSON using GTL

Question

I'm running into an issue where I have to parse through a JSON payload for an email using GTL, and I have issues trying to target a specific node in the payload.

If this were the example of the payload:

[{"Table1": [
    {
        "Table2": {
            "Table3": {
                "Table4": {
                    "Groupings": [
                        {
                            "name": "Grouping1",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$2.00"
                                        }
                                    }
                                }
                            ]
                        },
                        {
                            "name": "Grouping2",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$4.00"
                                        }
                                    }
                                }
                            ]
                        },
                        {
                            "name": "Grouping3",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$1.00"
                                        }
                                    }
                                }
                            ]
                        }
                    ]
                }
            }
        }
    }
]}]

My need would be specifically target Grouping2's formatted amount.

I've tried applying a filter to the target at the level I think, but I don't yield the results I want:

%%[var @Json set @Json = '[{"Table1": [
    {
        "Table2": {
            "Table3": {
                "Table4": {
                    "Groupings": [
                        {
                            "name": "Grouping1",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$2.00"
                                        }
                                    }
                                }
                            ]
                        },
                        {
                            "name": "Grouping2",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$4.00"
                                        }
                                    }
                                }
                            ]
                        },
                        {
                            "name": "Grouping3",
                            "pricing": [
                                {
                                    "total": {
                                        "amount": {
                                        "formatted": "$1.00"
                                        }
                                    }
                                }
                            ]
                        }
                    ]
                }
            }
        }
    }
]}]']%%

{{.datasource JSONVar type=variable source=@Json}}
  {{.data}}
  {"target":"@Json"}
  {{/data}}

  {{.datasource Table1 type=nested}}
    {{.data}}
    {"target":"JSONVar.Table1"}
    {{/data}}
    
        {{.datasource Table2 type=nested}}
        {{.data}}
        {"target":"Table1.Table2"}
        {{/data}}

            {{.datasource Table3 type=nested}}
                {{.data}}
                {"target":"Table2.Table3"}
                {{/data}}

                {{.datasource Table4 type=nested}}
                    {{.data}}
                    {"target":"Table3.Table4"}
                    {{/data}}

                    {{.datasource Groupings type=nested}}
                        {{.data}}
                        {"target":"Table4.Groupings",
                    "filter":"name == Grouping2"}
                        {{/data}}

                        {{.datasource pricing type=nested}}
                            {{.data}}
                            {"target":"Groupings.pricing"}
                            {{/data}}

                            {{.datasource total type=nested}}
                                {{.data}}
                                {"target":"pricing.total"}
                                {{/data}}

                                {{.datasource amount type=nested}}
                                    {{.data}}
                                    {"target":"total.amount"}
                                    {{/data}}

                                    %%[set @finalamount = TreatAsContent('{{formatted}}')]%%

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

%%=v(@finalamount)=%%

The outcome of @finalamount is $1.00, when it should be $4.00. I've moved the filter up and down a level in the nested datasources, and nothing seems to yield me the outcome I need. Anyone have an idea of what I may be doing wrong (or if it's even possible)?

I know that what's essentially happening is that it iterates through the 3 and is setting the variable and resetting until it lands on the last one ($1.00), which is why that's the output, but I need to target Grouping2 consistently, and can't.

• No, the value of 2 won't always be the largest
• Unfortunately, there is going to be this potential with unused groups would be provided, so that's why I need to be able to target specifically Grouping2.

Thanks!

Answer 1

Even though there are several GTL filter examples floating around in the official SF docs and around here, I couldn't get them to work.

Plus, every time I try to iterate or manipulate a complex JSON object with GTL, I get an hour in and realize I can solve it with SSJS much quicker.

I used jsonformatter.com to identify the path of the target node.

%%[

set @Json = '[{"Table1":[{"Table2":{"Table3":{"Table4":{"Groupings":[{"name":"Grouping1","pricing":[{"total":{"amount":{"formatted":"$2.00"}}}]},{"name":"Grouping2","pricing":[{"total":{"amount":{"formatted":"$4.00"}}}]},{"name":"Grouping3","pricing":[{"total":{"amount":{"formatted":"$1.00"}}}]}]}}}}]}]'

]%%

<script runat="server" language="javascript">

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

  var JsonStr = Variable.GetValue("@Json");
  var Json = [];

  try {

     Json = Platform.Function.ParseJSON(JsonStr);

  } catch (e) {

    Write("<br><br>e: " + Stringify(e))
    // Platform.Function.RaiseError("payload parse failed",false,"statusCode","3");
    
  }
  
  // Json[0].Table1[0].Table2.Table3.Table4.Groupings[1].pricing[0].total.amount.formatted
  // via https://jsonpathfinder.com

  var Groupings = Json[0].Table1[0].Table2.Table3.Table4.Groupings;
  var finalamount = "";

  if (Groupings.length > 0) {

    for (var i = 0; i < Groupings.length; i++) {

        var name = Groupings[i].name;
        if (name == "Grouping2") {
            finalamount = Groupings[i].pricing[0].total.amount.formatted;
        }
    }

  }
  Variable.SetValue("finalamount", finalamount)

</script>
finalamount: %%=v(@finalamount)=%%

Output

finalamount: $4.00