dbeatty10
commented
6 months ago Thanks for reporting this @Gilby216!
TLDR
I think https://github.com/dbt-labs/dbt-bigquery/pull/1061 will fix this.
Root cause
We've had a couple issues that look similar to this, namely https://github.com/dbt-labs/dbt-bigquery/issues/972 and https://github.com/dbt-labs/dbt-bigquery/issues/1055.
It looks like the underlying issue for this one is within our fix added in https://github.com/dbt-labs/dbt-bigquery/pull/974.
When I tried the following monkey patch instead, it worked:
def _json_from_json(value, _):
"""NOOP string -> string coercion"""
return None if value is None else json.loads(value)Solution
The fix might be covered by https://github.com/dbt-labs/dbt-bigquery/pull/1061, which removes our monkey patch in favor of relying on https://github.com/googleapis/python-bigquery/pull/1587 instead.
We should add the following reproduction case to a test case within https://github.com/dbt-labs/dbt-bigquery/pull/1061 to confirm.
When I removed the monkey patch in my local environment, it worked.
Reprex
Run the following while using dbt-bigquery:
dbt show --inline 'select parse_json(null)'Get this error:
01:36:38 Unhandled error while executing
the JSON object must be str, bytes or bytearray, not NoneType
01:36:38 Encountered an error:
Runtime Error
the JSON object must be str, bytes or bytearray, not NoneTypeWorkaround
In the meantime, @jeremyyeo mentioned within https://github.com/dbt-labs/dbt-bigquery/issues/1055 that you can coerce it into to_json(null) to handle this edge case:
Select ifnull(parse_json(NULL), to_json(null))
github-actions[bot]
Is this a new bug in dbt-bigquery?
Current Behavior
When I run the following query in dbt Cloud I get an error:
Select parse_json(NULL)Error encountered:
Runtime Error the JSON object must be str, bytes or bytearray, not NoneTypeHowever that same query runs successfully when I run it directly in the BigQuery console. It outputs a SQL NULL value.
The query above is the simplest reproduce case. If it's useful, the actual situation I am trying to solve for here is to query into a JSON column and extract certain fields, but sometimes those fields are missing. To illustrate this more concretely, the following SQL also produces the same error in DBT Cloud:
Select parsed_json_data.ex1 from ( Select parse_json('{"ex2":"example"}') AS parsed_json_data ) AS t1But again, that query above runs successfully directly in BigQuery console and outputs a NULL value.
If you change this query to extract the field that does exist in the JSON then everything works fine in both DBT Cloud and BigQuery console:
Select parsed_json_data.ex2 from ( Select parse_json('{"ex2":"example"}') AS parsed_json_data ) AS t1Expected Behavior
NULL values returned from JSON columns should not error, they should output NULL.
Steps To Reproduce
In DBT Cloud, preview the following query/model:
Select parse_json(NULL)Relevant log output
Environment
Additional Context
No response