Iterate over ARRAY<JSON> in BigQuery

Question

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?

Answer 1

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