Flattening a nested JSON structure

Below is a short sample of the JSON output I am working with.

{
    "total": 41,
    "first": "https://api.idxbroker.com/clients/soldpending?offset=0",
    "last": "https://api.idxbroker.com/clients/soldpending?offset=36",
    "next": "https://api.idxbroker.com/clients/soldpending?offset=6",
    "previous": null,
    "data": {
        "c151!%187557": {
            "address": "138 E 50th Street",
            "cityName": "Savannah",
            "detailsURL": "c151/187557",
            "listingID": "187557",
            "state": "Georgia",
            "zipcode": "31405"
        },
        "c151!%203935": {
            "address": "4 Mallard Drive",
            "cityName": "Savannah",
            "detailsURL": "c151/203935",
            "listingID": "203935",
            "state": "Georgia",
            "zipcode": "31419"
        },
        "c151!%182876": {
            "address": "2 Lake Heron Court W",
            "cityName": "Pooler",
            "detailsURL": "c151/182876",
            "listingID": "182876",
            "state": "Georgia",
            "zipcode": "31322"
        },
        "c151!%201014": {
            "address": "2 Pipers Pond Lane",
            "cityName": "Savannah",
            "detailsURL": "c151/201014",
            "listingID": "201014",
            "state": "Georgia",
            "zipcode": "31404"
        },
        "c151!%217162": {
            "address": "52 Litchfield Drive",
            "cityName": "Savannah",
            "detailsURL": "c151/217162",
            "listingID": "217162",
            "state": "Georgia",
            "zipcode": "31419"
        },
        "c151!%206778": {
            "address": "330 Kelsall Drive",
            "cityName": "Richmond Hill",
            "detailsURL": "c151/206778",
            "listingID": "206778",
            "state": "Georgia",
            "zipcode": "31324"
        }
    }
}

I’ve tried flip to convert columns to rows, but the 1st row ends up being the column headers.

Any suggestions as to how I can flatten this so that I have columns for address, cityName, detailsURL, listingID, state, zipcode?

The resulting data should look like the following except with proper column headings:

Thanks so much in advance for any suggestions.

Hey @Brian_Brewer,

Good question! The schema of that JSON is a bit unusual, but here’s a workaround that should nicely parse your data:

In this example, you’ll likely receive your JSON from an API. The webhook step was used to help map this out. Here’s how it works:

  1. The “Use Regex” step will find all instances of "c151!%...": and replace them with a blank value. This will help ensure your columns will remain stable down the line when we try to rename them.

  2. The “Find and replace” step will create a top-level key called information and formats the rest of your data as an object array.

  3. The “Expand JSON” step can now target the array as a dataset and will parse out your columns with the headers data information address, data information cityName etc.

  4. The “Rename columns” step can rename your columns to the desired address, cityName etc.

Copy and paste this snippet into your flow to duplicate the exact step settings shown above:
parabola:cb:d16707ab033248a6976f862aa7df8f96

Hope that helps!

I can’t thank you enough, that approach worked perfectly! I so appreciate you taking the time to create the snippet and the detailed explanation!

1 Like