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

Notes

  • The path values should be the generic path without numeric arrays, e.g. enter attributes.name and not attributes.1.name.
  • The number of levels need to match, e.g. you can't use attributes.name with attributes.value.count


3 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
  2. Hello Ana!

    First off, I want to thank you again for creating such a phenomenal API program. Your work has saved me - quite literally - HUNDREDS of hours of work (not to mention my sanity). And your knowledge base website is fantastic. I've learned so much and I can find an answer for everything here.

    My question: regarding flattening fields, is it possible to set up two different flattening parameters in the same API request call? TBH I don't even know whether this is even programmatically possible in G-Sheets in general. If it is possible, is there a way to use this function within your amazing application?

    I use the flatten function in your app regularly and it works beautifully; but I now have some extra column data in my GET calls, and specifying two different flattening values would greatly improve the readability.

    I experimented with two formats for the flatten fields params:
    i. path to name/path to value separated by a "," comma
    ii. same but separated with a "|" pipe.

    Neither format was successful. I assume the multiple values were just "cancelled out", because everything came back normally as if I did not specify any flattening.

    For reference:

    PARAM 1
    [Path to header] Products.DescriptiveElementGroup.DescriptiveElements.Name
    [Path to value] Products.DescriptiveElementGroup.DescriptiveElements.DisplayValue

    PARAM 2
    [Path to header] Products.Specifications.Name
    [Path to value] Products.Specifications.Value

    Entered as:

    Products.DescriptiveElementGroup.DescriptiveElements.Name | Products.Specifications.Name
    +
    Products.DescriptiveElementGroup.DescriptiveElements.Value | Products.Specifications.Value
    ------
    Each of the Descriptive.Elements.Name/Value and Products.Specifications.Name/Value work perfectly when used alone in the flattening.

    Thank you so much for all the amazing work you do!
    -Julia

    Reply

Leave a Comment

Jump To...