CODEWITHSUNDEEP
sqljsongoogle-bigquery
Nik
Nik

Reputation: 381

BigQuery - Extract first non-null value from JSON collection

Here is how my collection looks:

What I would like to is to get a first non-null value from it, something like json-coalesce(my_collection,"$") returing 5 in the first case and true in the second.

What's the best way to do this without handling each key individually?

Upvotes: 0

Views: 580

Answers (2)

Jaytiger
Jaytiger

Reputation: 12274

Not a general solution, but using a regex,

SELECT TRIM(REGEXP_EXTRACT(json, r'(?U):\s*([^(null)].*)\s*[,}]'), '"') non_null 
  FROM sample;

assumption: a value is a primitive type, i.e. integer, string.

output will be:

enter image description here

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Consider below approach

create temp function values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
""";
select my_collection,
  ( select val
    from unnest(values(my_collection)) val with offset
    where not val is null
    order by offset
    limit 1
  ) as first_not_null_value
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions