CODEWITHSUNDEEP
jsongoogle-cloud-platformgoogle-bigquery
Panda
Panda

Reputation: 643

How to add null in ARRAY in BigQuery

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|

Upvotes: 3

Views: 1468

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173200

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

enter image description here

Upvotes: 2

Related Questions