Big Query first non null value record type

Question

I'm working with Big Query and I have a record field 'funnels_informations' containing two subfields: 'partnership_title' and 'voucher_code'.

I want to have the first non-null value of partnership_title and the corresponding value of voucher_code.

For example here, I want to have partnership_title=indep and voucher_code=null:

Any solution please?

Thanks in advance.

Jaytiger · Answer

You may consider below scalar subquery for your purpose.

WITH sample_table AS (
  SELECT [STRUCT(STRING(null) AS partnership_title, STRING(NULL) AS voucher_code),
          ('indep', NULL), ('Le', 'LB')
         ] AS funnels_informations
)
SELECT (SELECT AS STRUCT * EXCEPT(offset) 
          FROM UNNEST(t.funnels_informations) WITH OFFSET
         WHERE partnership_title IS NOT NULL 
         ORDER BY offset LIMIT 1
       ).*
  FROM sample_table t;

Mikhail Berlyant · Answer

Consider below option

select fi.*
from your_table t, t.funnels_informations fi with offset
where not partnership_title is null
qualify 1 = row_number() over(partition by to_json_string(t) order by offset)

if applied to sample data in your question - output is

Big Query first non null value record type

2 Answers