I want to fetch the value from JSON object in BigQuery. I have a JSON like below
{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}
and the condition to fetch json object value is : if apples = 2 then return value of oranges is 4
How do i iterate through ARRAY in BigQuery?
Consider below example
#standardSQL
with `project.dataset.table` as (
select '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}]}' json
)
select
json_extract(x, '$.oranges') oranges
from `project.dataset.table`,
unnest(json_extract_array(json, '$.fruit')) x
where json_extract(x, '$.apples') = '2'
with output
