Check if JSON key exist in BigQuery

Question

I am trying to check if the key is exist in JSON if it is exist return the whole JSON.

I have a JSON as below:

json    
 -------- 
 "fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}] 
 "fruit":{"apples":"1","oranges":"15"}   
 "fruit":{"apples":"5","oranges":"1"}  
 "fruit":[{"lettuce":"7","kale": "8"}] 

Output: If the JSON ARRAY or JSON Object has key apples then return value of key

json                                                                    |Output 
 -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
 "fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]  |[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]
 "fruit":{"apples":"1","oranges":"15"}                                 |{"apples":"1","oranges":"15"}
 "fruit":{"apples":"5","oranges":"1"}                                  |{"apples":"5","oranges":"1"}
 "fruit":[{"lettuce":"7","kale": "8"}]                                 | null

Answer 1

Consider below example

with `project.dataset.table` as (
  select '{"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]}' as json union all
  select '{"fruit":{"apples":"1","oranges":"15"}}' union all
  select '{"fruit":{"apples":"5","oranges":"1"}}' union all
  select '{"fruit":{"pineapples":"5","oranges":"1"}}' union all
  select '{"fruit":[{"lettuce":"7","kale": "8"}]}' 
)
select json, if(regexp_contains(json, '"apples"'), json_extract(json, '$.fruit'), null) as output 
from `project.dataset.table`         

with output

Answer 2

Just use if:

with mytable as (
  select '{"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]}' as json union all
  select '{"fruit":{"apples":"1","oranges":"15"}}' union all
  select '{"fruit":{"apples":"5","oranges":"1"}}' union all
  select '{"fruit":[{"lettuce":"7","kale": "8"}]}' 
)
select if(json like '%apples%', json_extract(json, '$.fruit'), null) as output 
from mytable