How to check if the value of key is ARRAY or STRUCT in BigQuery

user_8671089 0
Viewed 105

I am trying to extract values from JSON object but I don't know if the value is Array or an Object as i want to write a generic code that will check array or object and return value accordingly.

I have two JSON as below

JSON one:

"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]

JSON two:

"fruit":{"apples":5,"oranges":10}

I have to get value of oranges but the value of fruit may change. How to check type of value and extract value in BigQuery?

google-bigquery google-cloud-platform json
1 Answers

Consider below

#standardSQL
with `project.dataset.table` as (
  select 1 id, '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}' json union all 
  select 2, '{"fruit":{"apples":5,"oranges":10}}'
)
select id,
  json_extract(x, '$.apples') apples,
  json_extract(x, '$.oranges') oranges
from `project.dataset.table`, 
unnest(ifnull(json_extract_array(json, '$.fruit'), [json_extract(json, '$.fruit')])) x

with output

enter image description here

user_52219440
Related