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!

Mikhail Berlyant · Answer

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

Converting JSON to Columns in BigQuery

1 Answers