How do I exclude NULL values in a subquery? (BigQuery - SQL

Here is my query so far:

SELECT 
  event_date, 
  event_timestamp, 
  user_pseudo_id, 
  geo.country, 
  geo.region, 
  geo.city, 
  geo.sub_continent,
  (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = "search_term") AS search_term,
FROM `GoogleAnlyticsDatabase`

I am trying to exclude all NULL values in the 'search_term' column.

I am struggling to identify where I need to include IS NOT NULL in my code.

Everything I have tried so far has thrown up errors.

Does anyone have any ideas?

CodePudding user response：

Is this query giving you the expected result besides the NULL problem?

If yes, you can just wrap your query to an CTE aand filter this CTE

WITH

SRC AS (
SELECT 
  event_date, 
  event_timestamp, 
  user_pseudo_id, 
  geo.country, 
  geo.region, 
  geo.city, 
  geo.sub_continent,
  (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = "search_term") AS search_term
FROM `GoogleAnlyticsDatabase`
)

SELECT * FROM SRC WHERE search_term IS NOT NULL