[ADAP-1073] [Bug] Not able preview structs when the struct is null

[jeremyyeo]

Is this a new bug in dbt-bigquery?

• I believe this is a new bug in dbt-bigquery
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

If we have a struct column that contains null then dbt show runs into an unhandled error.

Expected Behavior

We should gracefully handle this error.

Steps To Reproduce

1. Create 2 tables:

create or replace table dbt_jyeo.json_error as select struct<k json>(to_json(struct(1 as a))) as v;
insert into dbt_jyeo.json_error values (struct<k json>(null));
select * from dbt_jyeo.json_error;

create or replace table dbt_jyeo.json_okay as select struct<k json>(to_json(struct(1 as a))) as v;
insert into dbt_jyeo.json_okay values (struct<k json>(to_json(null)));
select * from dbt_jyeo.json_okay;

2. Try to preview with dbt:

$ dbt show --inline "select * from dbt_jyeo.json_error"

22:18:25  Running with dbt=1.7.3
22:18:26  Registered adapter: bigquery=1.7.2
22:18:27  Found 0 sources, 0 exposures, 0 metrics, 447 macros, 0 groups, 0 semantic models
22:18:27  
22:18:27  Concurrency: 1 threads (target='bq')
22:18:27  
22:18:30  Unhandled error while executing 
the JSON object must be str, bytes or bytearray, not NoneType
22:18:30  Encountered an error:
Runtime Error
  the JSON object must be str, bytes or bytearray, not NoneType

$ dbt show --inline "select * from dbt_jyeo.json_okay"

22:18:54  Running with dbt=1.7.3
22:18:55  Registered adapter: bigquery=1.7.2
22:18:56  Found 0 sources, 0 exposures, 0 metrics, 447 macros, 0 groups, 0 semantic models
22:18:56  
22:18:56  Concurrency: 1 threads (target='bq')
22:18:56  
22:18:59  Previewing inline node:
| v               |
| --------------- |
| {"k": {"a": 1}} |
| {"k": null}     |

3. Try previewing in BQ:

Notice the null in italic in the first screenshot.

So it depends how the null gets into the data - if it's on the outer struct level itself (i.e. the data is literally null) vs a null that is within a json object itself (i.e. the data is a json type of which the value is null) is when we run into this error.

Relevant log output

No response

Environment

- OS: macOS
- Python: Python 3.11.2
- dbt-core: 1.7.3
- dbt-bigquery: 1.7.2

Additional Context

Last time we ran into something similar (#972) - it was due to the underlying bigquery python client so wont surprise me if it's more of the same.

Potential workaround for now is to check if null and coerce it into to_json(null):

$ dbt show --inline "select ifnull(v.k, to_json(null)) from dbt_jyeo.json_error"
22:28:53  Running with dbt=1.7.3
22:28:54  Registered adapter: bigquery=1.7.2
22:28:55  Found 0 sources, 0 exposures, 0 metrics, 447 macros, 0 groups, 0 semantic models
22:28:55  
22:28:55  Concurrency: 1 threads (target='bq')
22:28:55  
22:28:58  Previewing inline node:
| f0_      |
| -------- |
| {"a": 1} |
|          |

[colin-rogers-dbt]

Underlying issue is with bigquery python client: googleapis/python-bigquery#1500