DBT erroring on query that works in BigQuery (JSON_QUERY)

[gilby216]

Here is a basic example query to illustrate my issue:

Select parsed_json_data.ex1 from ( Select parse_json('{"ex2":"example"}') AS parsed_json_data ) AS t1

When I run this query directly in the BigQuery console it correctly outputs NULL.

But when I run that same query in dbt Cloud I get:

Encountered an error: Runtime Error the JSON object must be str, bytes or bytearray, not NoneType

This is just a token example, but it’s preventing me from querying JSON where some fields are missing some of the time. Seems like this would be a somewhat frequent issue for others.

Anyone have any more info or workarounds for this?

[gilby216]

Update: Filed a bug to the github dbt-bigquery project as I think this is a somewhat broader issue with how NULLs are handled in JSON columns

[Bug] Error when loading NULL result from JSON column

[a_slack_user]

I don’t think the adapter page is the right place to post this since it’s a Cloud IDE problem rather than being a direct problem with the adapter

_{Note: @Mike Stanley originally posted this reply in Slack. It might not have transferred perfectly.}