CODEWITHSUNDEEP
google-bigquerynull
Kumar
Kumar

Reputation: 52

How to check if null values present in JSON object column in BigQuery Table

I referred GCP Big query documentation as shown below link but there is no luck achieving the objective about using these functions to retrieve the non-null valued JSON data type column values from a Big Query Table.

Ref help: https://cloud.google.com/bigquery/docs/reference/standard-sql/json-data#json_nulls

Example Select Query: Select column1 from EMP where column1 != 'null'

Upvotes: 1

Views: 5303

Answers (2)

Nagesh Susarla
Nagesh Susarla

Reputation: 1700

Use JSON_TYPE assuming you want to find all values that are not explicit JSON NULLs?

Select column1 from EMP where JSON_TYPE(column1) != 'null'

Upvotes: 0

Jaytiger
Jaytiger

Reputation: 12254

When you try to compare JSON type with other type, you'll get

SELECT JSON "null" != 'null'

No matching signature for operator != for argument types: JSON, STRING. Supported signature: ANY != ANY at [1:8]

Simple workaround for this is to convert JSON data to BigQuery String and compare it with others.

WITH json_data AS (
  SELECT CAST(null AS JSON) AS col1 UNION ALL
  SELECT JSON 'null' UNION ALL
  SELECT JSON '{"a":null}' UNION ALL
  SELECT JSON '{"b":"hello"}'
)
SELECT *, FORMAT('%t', col1) format, JSON_TYPE(col1) json_type,
  FROM json_data;

enter image description here

FORMAT() and JSON_TYPE() function convert JSON 'null' to BigQuery string respectively. Utilizing one of these output, you can retrieve the non-null valued JSON data type column values

SELECT * FROM json_data WHERE JSON_TYPE(col1) != 'null';

or,

SELECT * FROM json_data WHERE LOWER(FORMAT('%t', col1)) != 'null';

Note FORMAT() turns null into NULL string.

Upvotes: 4

Related Questions