How to handle null in dynamic execute statement in bigquery?

Question

I have Input Table 1 :

a_firstname   a_middlename  a_lastname    b_Tag_state   b_Tag_country
Jhon                          smith         DC           US
Mary            Jhon         watson        Seattle       US
Harry                        potter                      US
Output:

Output table:

             Name                                                         Tag
{'a_firstname':'Jhon','a_middlename':'',   'a_lastname':'smith'}   {'b_Tag_state': 'DC','b_Tag_country':'US'}
{'a_firstname':'Mary','a_middlename':'Jhon,'a_lastname':'watson'}  {'b_Tag_state': 'Seattle','b_Tag_country':'US'}
{'a_firstname':'Harry','a_a_middlename':'', 'a_lastname':'potter'} {'b_Tag_state': '','b_Tag_country':'US'}

I have made a Dynamic BigQuery :

Declare a_column STRING;
Declare b_column STRING; 

SET
  a_column=(
 
    SELECT
      STRING_AGG(DISTINCT column_name)
    FROM
      test.dataset1.INFORMATION_SCHEMA.COLUMNS
    WHERE
      table_name = "table1"
      AND column_name LIKE 'a_%');

SET
  b_column=(
 
    SELECT
     STRING_AGG( DISTINCT column_name)
    FROM
      test.dataset1.INFORMATION_SCHEMA.COLUMNS
    WHERE
      table_name = "table1"
      AND column_name LIKE 'b_%');

EXECUTE IMMEDIATE(select TO_JSON_STRING(STRUCT("||a_column||")) as Name,TO_JSON_STRING(STRUCT("||b_column||")) as Tags, from  `test.dataset1.table1`")

I am using above query to transform data but I am stuck when there is no column that starts with b_. If I can running query at below dataset:

a_firstname   a_middlename  a_lastname    
    Jhon                          smith       
    Mary            Jhon         watson       
    Harry                        potter

I am getting error :

EXECUTE IMMEDIATE sql string cannot be NULL at [156:19]

How can I error handle that to make it dynamic and get a output. I Note :I am running query on TB of data.

Mikhail Berlyant · Answer

I would rather use below approach

select 
  ( select '{' || string_agg(kv) || '}' from a.kvs kv 
    where starts_with(kv, '"a_')
  ) name,
  ( select '{' || string_agg(kv) || '}' from a.kvs kv 
    where starts_with(kv, '"b_')
  ) tag  
from your_table t, 
unnest([struct(trim(to_json_string(t), '{}') as t_json)]), 
unnest([struct(regexp_extract_all(t_json, r'"\w+":"\w*"') as kvs)]) a

if applied to sample data in your question - output is

How to handle null in dynamic execute statement in bigquery?

1 Answers