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'
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;
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.
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'
