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?

by Nik in StackOverflow on July 01, 2022.

Answer

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

by Mikhail Berlyant on July 01, 2022.

Other helpful answers

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:

by Jaytiger on July 02, 2022.