< BACK TO QUESTIONS

How can I convert JSON keys to columns and values to rows in BigQuery?

Faraz

By Faraz - June 08, 2023

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!

Thanks!
Faraz 😊

End of the article

Subscribe to my Newsletter

Get the latest posts delivered right to your inbox


Latest Post