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.