Query JSON string in Google BigQuery

user_15390837 0
Viewed 67

i have the following JSON value in a column inside BigQuery and i am struggling to pull out the data into columns.

The value us:

[{"availableSlots":0,"date":"03/16/2021","day":"Tue","slotType":"AM","totalSlots":5},{"availableSlots":2,"date":"03/16/2021","day":"Tue","slotType":"PM","totalSlots":5},{"availableSlots":5,"date":"03/17/2021","day":"Wed","slotType":"AM","totalSlots":5},{"availableSlots":5,"date":"03/17/2021","day":"Wed","slotType":"PM","totalSlots":5},{"availableSlots":5,"date":"03/18/2021","day":"Thu","slotType":"AM","totalSlots":5}]

And i am trying to get the first and second value of availableSlots.

I did try with both JSON_EXTRACT_SCALAR and JSON_EXTRACT

THank you for any help.

google-bigquery
1 Answers

See below example

with `project.dataset.table` as (
  select '[{"availableSlots":0,"date":"03/16/2021","day":"Tue","slotType":"AM","totalSlots":5},{"availableSlots":2,"date":"03/16/2021","day":"Tue","slotType":"PM","totalSlots":5},{"availableSlots":5,"date":"03/17/2021","day":"Wed","slotType":"AM","totalSlots":5},{"availableSlots":5,"date":"03/17/2021","day":"Wed","slotType":"PM","totalSlots":5},{"availableSlots":5,"date":"03/18/2021","day":"Thu","slotType":"AM","totalSlots":5}]' col 
)
select 
  json_extract_scalar(json, '$.availableSlots') as availableSlots,
  json_extract_scalar(json, '$.date') as date,
  json_extract_scalar(json, '$.day') as day,
  json_extract_scalar(json, '$.slotType') as slotType,
  json_extract_scalar(json, '$.totalSlots') as totalSlots
from `project.dataset.table`, 
unnest(json_extract_array(col, '$')) json

with output

enter image description here

user_52219440
Related