1

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!

1
  • Are you intending to loop and output data or just retrieve the specific formatted value? Jul 22 at 17:07

1 Answer 1

0

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

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.