Reading from the Gong API - How to Handle JSON objects nested inside arrays

When working with APIs such as Gong, you may encounter JSON responses that are made up of arrays. These arrays contain multiple sub-objects and need to be transformed into multiple rows. Techniques like flattening and extracting nested data allow Matillion to efficiently process these responses and transform them to a row/column format that can be used in downstream pipelines. 

Using the Gong API in a Data Pipeline

An example of this type of response is received when using the Gong API component. This API allows users to access Gong endpoints and retrieve the desired response data. When utilizing this component, developers can seamlessly call a Gong endpoint by simply configuring the appropriate properties. One of the key advantages of the Gong API component is its ability to automatically generate the result table based on the metadata of the Gong source. This streamlined approach eliminates the need for developers to manually define the structure of the result table, saving time and reducing the potential for errors.

Here's an example Orchestration pipeline to extract Gong data and run the required transformations for flattening the data:

Flatten Variant and Extract Nested Data for JSON: 

Developers need tools and techniques to streamline the data extraction process. One such technique is to use the Flatten Variant component, which plays a crucial role in transforming array structures into rows of JSON objects. Once each element is a JSON row the data can then be passed to the next step in the transformation pipeline. 

Extracting nested data from JSON responses is a common task in data processing pipelines that include API responses. That’s where the Extract Nested Data component comes into play. By leveraging this powerful technique in conjunction with the Flatten Variant component, developers can quickly extract nested data from JSON responses and incorporate it into their data pipelines with ease

Here's part of a sample partial response from the Gong API call:

[
  {
    "active": true,
    "created": "2022-10-04T10:29:54.644-06:00"
  },
  {
    "active": true,
    "created": "2021-03-12T07:57:12.530-07:00"
  },
  {
    "active": false,
    "created": "2021-03-12T07:57:12.530-07:00"
  },
  {
    "active": true,
    "created": "2022-04-13T07:14:17.480-06:00"
  },
  {
    "active": true,
    "created": "2021-03-12T07:57:12.530-07:00"
  },
  …
]

The Flatten Variant component converts this into multiple JSON objects (one per row) looking like this:

 {
  "active": true,
  "created": "2022-10-04T10:29:54.644-06:00"
}

The Extract Nested Data component will then transform the JSON object to a row/column format.

In this case, I am just selecting the lastname field:

Now it's an ordinary relational column, the lastname field can be used with components in the remainder of the transformation pipeline.

Here's how it looks end to end.

Example Transformation job that Flattens the Gong Array Data into rows/columns:

As you can see, utilizing Matillion’s Data Productivity Cloud to address navigating complex API responses allows users to easily retrieve and process API responses. To see more of what Matillion can do for your organization, sign up for a free trial. 

Mike Terrell
Mike Terrell

Sales Engineer