-
Based on the format of your current table in BigQuery and your desired table, it seems like you are trying to unnest JSON into individual columns. To achieve this, you will need to use some SQL functionality.
Firstly, extracting JSON data into columns in BigQuery can be done using the JSON_EXTRACT_SCALAR function in SQL. The function requires two string-type parameters – the JSON string and the JSONPath query.
For instance, you can create a view in BigQuery that extracts the parameters from device, geo, and traffic_source and represents them as separate columns. The code will look similar to this:
SELECT
event_name,
JSON_EXTRACT_SCALAR(device, ‘$.v.f[0].v’) AS device_type,
JSON_EXTRACT_SCALAR(device, ‘$.v.f[1].v’) AS device_brand,
— Add more fields as needed with correct path to JSON fields
JSON_EXTRACT_SCALAR(geo, ‘$.v.f[0].v’) AS geo_continent,
JSON_EXTRACT_SCALAR(geo, ‘$.v.f[1].v’) AS geo_sub_continent,
— Add more fields as needed
FROM your_table;In the sample SQL code, event_name is a given column, the other columns are extracted from the JSON inside device and geo.
Please note that this assumes a consistency in the order of the nested JSON data. If the order isn’t consistent, you might run into issue. Also, you need to replace ‘your_table’ with the name of your table.
Once you have this setup, querying the data in PowerBI should be pretty straightforward. From PowerBI’s navigation menu, go to ‘Get Data’ and select BigQuery as your data source. Then, use Power BI’s interface to write the SQL query, or select the view you created earlier, and load the data.
Finally, JSON data and its extraction can prove tricky and you might encounter some adjustments to be made according to the specific structure of your JSON data. But this should provide a decent start. If there are still issues, it might be worth investing time in understanding JSON structure more profoundly or consider reaching out for expert help.
