Search API Connector Documentation

Print

Flatten Fields to Columns

Contents

Example Data Structure

You may encounter an inconvenient pattern where key-value pairs are listed within a parent header, like this:
filter-fields-key-column

In this example, we have multiple keys like “Store name” and “Order number” grouped together under attributes.name, which can make the data hard to work with. Our desired output is to pull out each attribute key and give it its own column, like this:
flattendata-flattened

How to Flatten Data

API Connector provides an option to automatically extract these keys and convert them into headers.

flattendata-option

Facebook Ads actions

The Facebook Ads /insights endpoint returns “actions” (aka conversions or results) in an unflattened list. Selecting Facebook Ads actions will automatically flatten these actions into separate columns. This option is automatically selected for any requests that connect to Facebook Ads via OAuth.

Facebook Pages data

The Facebook Pages /insights endpoint returns page metrics in an unflattened list. Selecting Facebook Pages data will automatically flatten these page metrics into separate columns. This option is automatically selected for any requests that connect to Facebook Pages via OAuth.

Custom

The custom option enables flattening any data set through the use of two configuration settings: Path to header and Path to value. In Path to header, enter the field that should be converted into headers. In Path to value, enter the location of the associated value.
flattendata-config

The path values should be the generic path without numeric arrays, e.g. enter attributes.name and not attributes.1.name.


Previous Create a Custom OAuth Connection
Next ImportAPI Custom Function

2 thoughts on “Flatten Fields to Columns”

  1. Hi Ana/team!
    I couldn’t be happier to have found this API connector! However, there’s one thing is intriguing me 🙁
    I have the following section on my JSON file (OMDB API) and the Flatten Fields option is working, but is retrieving the very last value (in this example: ‘Metacritic’)…Since I already have columns such as imdb and metascore , I would need to transform and display the name/value for ‘Rotten Tomatoes’ (2nd in the array).
    PS: Path to header/value are Ratings.name & Ratings.value, respectively. THANK YOU VERY MUCH IN ADVANCE, you’d made my day, I have had no success. 🙁
    Thanks! Marcos
    (have tried this approach here, too: https://mixedanalytics.com/knowledge-base/filter-specific-fields-values/ but no success either)


    "Ratings": [
    {
    "Source": "Internet Movie Database",
    "Value": "6.2/10"
    },
    {
    "Source": "Rotten Tomatoes",
    "Value": "63%"
    },
    {
    "Source": "Metacritic",
    "Value": "59/100"
    }
    ]

    Reply
    • Hey Marcos, based on that snippet the path to header should be Ratings.Source, not Ratings.Name, and the path to value should be Ratings.Value, not Ratings.value (it’s case-sensitive). Can you please check if that resolves the issue?

      Reply

Leave a Reply to Marcos Cancel reply

Table of Contents