Transforming Marketo-incompatible webhook responses using AWS API Gateway + JSONPath + Velocity (!)

Sanford Whiteman -

Velocity knowledge is useful even outside of Marketo. I’ve been waiting for a ripped-from-the-headlines example, and along came this Marketo Nation question:

I’m calling a remote service via webhook. But the results aren’t coming back in a format the Response Mappings can understand (at least not from what I’ve tried). The response JSON looks like this:

     {
        "car_count" : 123456,
        "item_count" : 117733,
        "item_types" : [
          {
             "item_type" : "Blue Car",
             "item_type_count" : 1115,
             "item_type_ratio" : 12
          },
          {
             "item_type" : "Red Car",
             "item_type_count" : 1225,
             "item_type_ratio" : 14
          },
          {
             "item_type" : "Yellow Car",
             "item_type_count" : 1345,
             "item_type_ratio" : 6
          }
       ],
       "item_types_ratio" : 22
    }

But I need to get the item_type_count for each item_type to write to different fields. This would be easy if the JSON were like:

    {
      "Blue Car" : 1115,
      "Red Car" : 1225,
      "Yellow Car" : 1345
    }

In case it’s not clear, the reason the original JSON response won’t work with webhook Response Mappings is that a Response Attribute entry always uses a string property name (which could be a numeric string array index[1]):

That is, you can reference “the first member of the item_types array” (item_types[0]) and get its item_type_count: that’s item_types[0].item_type_count.

But you can’t reference “the (first) member of the item_types array that has the property item_type set to Blue Car” and get its item_type_count. That would amount to seeking the value of a specific property in an object (not the name) and back-referencing the object. Not possible!

If you only had JSONPath

A value-based seek is possible in JSONPath, where you use script expressions, which are like callbacks. In JSONPath, you’d do $.item_types[?(@.item_type == "Blue Car")].item_type_count.

Only Response Mappings don’t support JSONPath.

This is where you need an API gateway sitting between you and the original remote service:

  • Marketo’s Call Webhook connects to the gateway
  • the gateway seamlessly connects to the original service and gets the original response
  • the gateway transforms/translates/trims the response, using JSONPath or some other language, on-the-fly
  • the gateway returns transformed content to Marketo
  • now, Marketo’s Response Mappings can parse the content as needed

Transformers are key parts of API gateways

API gateways typically emphasize their enterprise features: security, rate limits, load balancing, multiplexing, et al. But buried in nearly every feature list is something basic: a content transformer. (You can see it under “Manipulation - Transform” on KrakenD’s infographic.)

Transformers are usually marketed in terms of transforming XML to JSON or other such big promises. But sometimes you just need to transform JSON into slightly different JSON. The transformer can do that, too.

There are several players offering API-gateway-as-a-service, but I’m most familiar with Amazon’s. AWS API Gateway is cheap — free tier is a million requests per month — and it’s, well, easy enough to set up. (Not really easy, I concede.)

We’re going to build an AWS API GW definition for the remote service at https://originalendpoint.example.com. The assumption for today is you typically GET that URL using a Marketo webhook.

First, Build a new REST API:

Confirm this is REST, not WebSocket:

Next, Create Method at the top level:

Choose GET from the dropdown:

Set the remote Integration Type to HTTP, and type the original Endpoint URL.

Now, the good stuff. Click Integration Response:

Add this simple Velocity script as the Mapping Template for the Content-Type application/json:

#set( $item_types = $input.path("$.item_types") )
{
#foreach( $type in $item_types )
  "${util.escapeJavaScript($type.item_type).replaceAll("\\'","'")}" : ${type.item_type_count}#if( $foreach.hasNext ),#end

#end
}

Finally, Deploy API generates your unique Invoke URL. That’s what you swap in in the Marketo webhook definition.

Reviewing the Velocity code

The Velocity transformer code is very simple, as long as you have a little bit of Velocity experience to start with!

  1. Use JSONPath via AWS API Gateway’s built-in $input.path() method to get a list representation of the item_types objects. (ArrayList[2] of LinkedHashMaps, if you wanna be technical.)
  2. Print the opening { of a top-level JSON object.
  3. Iterate over the list. For each $type object in the list, output the quoted, JSON-encoded value of its item_type property, then a colon :, then the unquoted (since it’s a number) value of its item_type_count property.
  4. Print a comma , if necessary.
  5. Print the closing } of the JSON object

You’ve thus transformed this original response:

{
   "car_count" : 123456,
   "item_count" : 117733,
   "item_types" : [
      {
         "item_type" : "Blue Car",
         "item_type_count" : 1115,
         "item_type_ratio" : 12
      },
      {
         "item_type" : "Red Car",
         "item_type_count" : 1225,
         "item_type_ratio" : 14
      },
      {
         "item_type" : "Yellow Car",
         "item_type_count" : 1345,
         "item_type_ratio" : 6
      }
   ],
   "item_types_ratio" : 22
}

Into this:

{
  "Blue Car" : 1115,
  "Red Car" : 1225,
  "Yellow Car" : 1345
}

The new JSON “shape” lets you use Response Mappings to capture the count for each color:

Caveat: Your AWS API Gateway setup isn’t really done

This post is meant to take you, say, 85% of the way toward a production API gateway hosted on AWS. There are some parts I couldn’t get to because the main thrust is about transformation using JSONPath + Velocity.

You should also require an API Key for authentication and add the corresponding X-Api-Key header in Marketo » Webhooks Actions » Set Custom Header. And you’ll most likely need to declare URL Query String Parameters so they’re passed to the remote service.

Both of the above options are under Method Execution in the AWS Console.

Notes

[1] For you see, array indexes are actually string property names, even though we express them as integers.

[2] Even more technically, it’s a net.minidev.json.JSONArray of java.util.LinkedHashMap objects. JSONArray is a subclass of java.util.ArrayList.