How to add null in ARRAY in BigQuery

Question

I am trying to extract values from JSON objects present in the BigQuery table. Problem Statement- I have a table with json values in one of the columns. I want to extract values from the json when there is value to the key is array and array has matching values. Output should be in ARRAY as per where condition given in the query written below and when there is an object it should return value in STRING. Please refer below output table and query. I have a table 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"}]

I am writing below query

SELECT json, ( SELECT ARRAY(JSON_EXTRACT_SCALAR(fruit, '$.oranges')) FROM UNNEST(ifnull(json_extract_array(json, '$.fruit'), [json_EXTRACT(json, '$.fruit')])) fruit WHERE JSON_EXTRACT_SCALAR(fruit, '$.apples') = "5") AS fruit_value FROM TABLE

It gives an error like Array cannot have a null element; error in writing field fruit_value I want output like below

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

Mikhail Berlyant · Accepted Answer · 2021-03-30 20:10:59Z

2

Consider below

#standardSQL
select json,
  array(
    select json_extract_scalar(x, '$.oranges') oranges,
    from unnest(ifnull(json_extract_array(json, '$.fruit'), 
    [json_extract(json, '$.fruit')])) x  
    where json_extract_scalar(x, '$.apples') = '5'
  ) as fruit_value
from `project.dataset.table`

if applied to sample data in your question

with `project.dataset.table` as (
  select '{"fruit":[{"apples":"5","oranges":"10"},{"apples":"5","oranges":"4"}]}' 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"}]}' 
)

the output is

answered Mar 30, 2021 at 20:10

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add null in ARRAY in BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related