Converting JSON to Columns in BigQuery

Question

I'm trying to parse a JSON column and map it into individual columns based on key-value pairs. Here's what my input would look like, and I've added the sample output. I am doing this in GCP Bigquery.

Input: JSON column

{"id":"1","timestamp":"2022-09-05", "data":{"fruits":"apple", "name":"abc"}},
{"id":"2","timestamp":"2022-09-06", "data":{"vegetables":"tomato", "name":"def"}},
{"id":"3","timestamp":"2022-09-07", "data":{"fruits":"banana", "name":"ghi"}}

Sample Output:

id  timestamp   fruits  vegetables  name
1   2022-09-05  apple   null        abc
2   2022-09-06  null    tomato      def
3   2022-09-07  banana  null        ghi

P.S. -> I've tried going through a few of the answers on similar use cases, but it didn't quite work for me.

Thanks in advance!

Your JSON is not valid JSON. 1) JSON does not use single quotes. 2) All keys must have a value. This is invalid: 'fruits':{'apple'} 3) Use a JSON validator to see the other errors. — John Hanley
– John Hanley, Commented Sep 7, 2022 at 2:12
@MazlumTosun Yes, I want to load it to a BigQuery table. I would be reading the JSON from a table and then want to parse this json and load it as columns in the final table. — Umang
– Umang, Commented Sep 7, 2022 at 18:03

Mikhail Berlyant · Accepted Answer · 2022-09-07 19:09:18Z

2

to parse a JSON column and map it into individual columns based on key-value pairs

Consider below

select 
  json_value(json, '$.id') id,
  json_value(json, '$.timestamp') timestamp,
  json_value(json, '$.data.fruits') fruits,
  json_value(json, '$.data.vegetables') vegetables,
  json_value(json, '$.data.name') name
from your_table

if applied to sample data in your question - output is

answered Sep 7, 2022 at 19:09

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Umang Over a year ago

Mikhail - This is what I'm using right now. However, my json won't have the said keys every time and may change. So, I was looking for a dynamic solution that could break the keys into column headers & its values as records.

Mikhail Berlyant Over a year ago

check out stackoverflow.com/a/73505124/5221944 as an example of how to make it dynamic. there are plenty more posts with different variations of that technique answered by myself alone, not to mention many more answered by others. Hope this will help :o)

Umang Over a year ago

This works! Thank you so much, Mikhail. Just curious, I see that the bytes shuffled and slot time consumed is quite high, even for a small table. Is there a more inexpensive way to achieve this?

Mikhail Berlyant Over a year ago

this is the easiest and least expensive option i know of :o)

Collectives™ on Stack Overflow

Converting JSON to Columns in BigQuery

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related