Flattening json to BigQuery


I am currently trying to store data from an API call in BigQuery. It is a use case as I am working with Power Bi and need to store data in a data warehouse in order to use it in the multiple reports.

For this exemple I am requesting a weather forecast API.

Basically my flow is the following for now :

My issue is that the json is composed of some collections as you can see in the following picture :

As a consequence, when I post the json into BigQuery I have only one row with all the collections records in it:

I understand that the json needs to be flatten in order to match with bigquery requirements.

Do you have any advice on how can we flat json in Make ? I thought about store the json on a drive, and use python with cloudfunction to flat the json.

I assume the json can be flatten directly in Make. Is it possible to transform the json into a flatten json table and then convert it into a json to send it to BigQuery.

Would be reallly gratefull for any help with this issue and possible solutions.

1 Like

Hey @Olivier_B have you tried and used the flatten function?