Learn how to transform JSON data into a tabular format in BigQuery. Easily convert JSON keys into columns and values into rows for efficient analysis.
To convert JSON keys to columns and values to rows in BigQuery using SQL, you can use the JSON_EXTRACT_SCALAR and UNNEST functions. Here's an example:
Let's say you have a table called my_table in BigQuery with a column json_data containing JSON data. You want to convert the JSON keys to columns and the corresponding values to rows.
Here's a sample query to achieve this:
SELECT key AS column_name, value AS row_value FROM `my_table`, UNNEST(JSON_EXTRACT_SCALAR(json_data, '$')) AS key WITH OFFSET JOIN UNNEST([JSON_EXTRACT_SCALAR(json_data, '$.' || key)]) AS value WITH OFFSET ON key = value
In this query, JSON_EXTRACT_SCALAR(json_data, '$') extracts the top-level keys from the JSON object, and UNNEST is used to create a row for each key. The key column is joined with the corresponding value column using the WITH OFFSET clause.
The result of this query will give you two columns: column_name and row_value. Each row will represent a key-value pair from the JSON data.
Note: If your JSON data has nested structures, you may need to modify the query accordingly to handle those structures.
I hope you found the above information helpful. Please let me know in the comment box if you have an alternate answer 🔥 and you can support me by buying me a coffee.
And don’t forget to sign up to our email newsletter so you can get useful content like this sent right to your inbox!