BigQuery - Extract first non-null value from JSON collection-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Not a general solaution, 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: