Handling inconsistent JSON object

Ask a question
1

The JSON object I GET from my vendor has inconsistent properties. Sometimes “state” exists and sometimes it doesn’t. I’ve posted data below. The first branch doesn’t have “state” and the other three do.

How can I handle when it doesn’t and add it to the data with an empty string, so it doesn’t break the flow when I POST to my destination?

{
    "_embedded": {
        "branches": [
            {
                "branchId": "33qIH0XRXrJua-OxMoj8dA",
                "name": "abcd",
                "timeZone": "Europe/Dublin",
                "latitude": 53.35433160,
                "longitude": -6.54643000,
                "streetAddress1": "74 Crodaun Forest Park",
                "streetAddress2": "Celbridge",
                "city": "County Kildare",
                "postalCode": "W23 WR26",
                "country": "Ireland(Rep.)"
            },
            {
                "branchId": "ZgLJBmQNHr1EsadecLyo9Q",
                "name": "nDevor Systems Ltd",
                "timeZone": "Europe/Dublin",
                "latitude": 53.34886460,
                "longitude": -6.26943790,
                "streetAddress1": "Anglesea Mills",
                "streetAddress2": "Anglesea Row",
                "city": "Dublin",
                "state": "County Dublin",
                "postalCode": "D07 W5NE",
                "country": "ireland"
            },
            {
                "branchId": "7cUnzIvMb3vm-S4k6BxXTQ",
                "name": "Ops Salon",
                "timeZone": "UTC",
                "streetAddress1": "100 Capel St",
                "streetAddress2": "Dublin",
                "city": "Dublin",
                "state": "",
                "postalCode": "",
                "country": "Ireland"
            },
            {
                "branchId": "F4bDLgtaqjhoH1xN4SY5cg",
                "name": "Salon IT",
                "timeZone": "UTC",
                "streetAddress1": "100 Capel St",
                "streetAddress2": "",
                "city": "Dublin",
                "state": "",
                "postalCode": "",
                "country": "Ireland"
            }
        ]
    },
    "page": {
        "size": 4,
        "totalElements": 4,
        "totalPages": 1,
        "number": 0
    }
}
2

Hey @Tim_Stadtlander :wave:

These are definitely tricky. The key here is to have the top JSON object contain all keys that are present in subsequent objects to ensure that all columns are pulled in. For instance, if your top object does not contain state, than that value will get lost when unpacking subsequent rows that do contain state.

To ensure that your top row contains all relevant keys, we can insert a blank JSON object that contains all keys as your top JSON object within branches.

We can do that with a “Find and Replace” step, directly manipulating the column that contains your full JSON string (unexpanded).

Here is an example “Find and Replace” step:

Screen Shot 2021-10-25 at 11.28.00 AM
Screen Shot 2021-10-25 at 11.28.00 AM420×556 76.9 KB

This will insert a top JSON object within branches that contains all of your relevant columns, with either blank or 0 as the value (kindly note that this is just an example, so I did not look to see all of the columns that need to be included).

Here is the JSON used in the ‘Replace’ section:

"branches": [{ "branchId": "", "name": "", "timeZone": "", "latitude": 0, "longitude": 0, "streetAddress1": "", "streetAddress2": "", "city": "County Kildare", "state": "", "postalCode": "", "country": "" },

Let me know if this resolves the issue!

1 Like