Problems With JSON Layout

Saverian

I've just started working with the Klaviyo API to get some basic email metrics in however, this is my first time using a custom API. I've set the data stream up correctly and I've successfully pulled in the JSON file, using the JSON Path part of the file transformer for "results[0].data", but I'm having problems with the structure of the JSON and mapping.

The data is very basic for now, it's just date and count of email opens as a test, with an example below.

    "results": [

        {

            "data": [

                {

                    "date": "2020-09-23 00:00:00",

                    "values": [

                        0.0

                    ]

                },

                {

                    "date": "2020-09-24 00:00:00",

                    "values": [

                        5.0

                    ]

                },

                {

                    "date": "2020-09-25 00:00:00",

                    "values": [

                        5.0

                    ]

                }

The mapping is recognising the date field but doesn't show the values field at all. I have played around with the other JSON file transformer options and have discovered if I change the path to results[0].data[1] it returns date and "header" but "2020-09-24 00:00:00" and 5.0 are on separate rows.

Any help would be greatly appreciated.

jharris

Hi @Saverian,

Two suggestions:

1. Use the 'flatten nested objects' checkbox in the JSON file transformer options
2. The VALUE_FROM_JSON() function might be helpful

Thanks,
Josh

Saverian

Hi Josh,

Unfortunately the 'flatten nested objects' option only gives the dates, it doesn't seem to work with arrays. The other solution I believe would be useful if I can get the mapping to recognise that a value field even exists but currently it's either just date or a bunch of properties on the same level as "data".

Thanks,
Mat

jharris

Hi @Saverian,

Sorry I took a while to get back to you on this.

Unfortunately, I think this is impossible using the JSON file transformer. If you're retrieving the JSON data using HTTP retrieval, you could alternatively use Python retrieval in which you retrieve the JSON and transform it according to your needs.

I've done similar things, happy to send a simple template for the script.

Thanks,
Josh