Bigquery Select * Except Nested Column

BigQuery - Select multiple columns and want to exclude two double nested columns

Like @martinus noticed, your except syntax is not correct. If you take a look to the BigQuery Documentation, you will see that the correct way to run a Query with an except is:

SELECT 
   field.* EXCEPT (nested_field1, nested_field2)
FROM `my_table`

Nevertheless, you can not directly use EXCEPT on a nested field directly. As a workaround you can exclude all the hits.customDimensions values from hits.*, and then SELECT for only hits.customDimensions.* and then exclude the nested elements that you need to remove, like index and value.

A query like the following should work:

SELECT fullVisitorId,
   visitId,
   visitNumber,
   cd.value as PCF_CUST_ID,
   date,
   TIMESTAMP_SECONDS(visitStartTime) as visitStartTime,
   totals.visits as visits,
   totals.hits as total_hits,
   hits.* EXCEPT (hits.customDimensions),
   hits.customDimensions.* EXCEPT (index, value)
FROM `lt-pcf-analytics-exp.90676036.ga_sessions_*` as t
          left join unnest(customDimensions) as cd
          left join unnest(hits) as hits
WHERE _TABLE_SUFFIX between '20210101' and '20210131' 
   and cd.index = 4 and cd.value is not null
ORDER BY PCF_CUST_ID, visitStartTime, hitNumber

BigQuery select * except nested column

The way to think of the problem is that you still want a payload column in the result, but you want it to have a different structure, namely to exclude comment. In this case, you can use SELECT * REPLACE to make the modification. For example,

#standardSQL
SELECT * REPLACE ((SELECT AS STRUCT payload.* EXCEPT (comment)) AS payload)
FROM `bigquery-public-data.samples.github_nested`
LIMIT 1000;

Select rows from array of nested objects and remove rows with some matched columns

Use below approach

select
  order_id,
  rate.base_currency,
  rate.target_currency,
  rate.rate
from order_rates,
  unnest(rates) as rate with offset
where true
qualify row_number() over(partition by order_id, base_currency, target_currency order by offset) = 1    

if applide to sample data in your question - output is

Select all columns, but replace some with expression in Google BigQuery?

In addition to SELECT * EXCEPT, Google BigQuery also supports SELECT * REPLACE clause in Standard SQL dialect. Documentation can be found here: https://cloud.google.com/bigquery/sql-reference/query-syntax#select-list
Your example will become:

SELECT * REPLACE(
  CAST(start_date AS DATE) AS start_date,
  CAST(end_date AS DATE) AS end_date)
FROM T

BigQuery except double nested column

Below are my few cents on this:

I think it answers your exact question / case

#standardSQL
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT hit.* 
      REPLACE((SELECT AS STRUCT latencyTracking.* EXCEPT(userTimingLabel)) AS latencyTracking)
    FROM UNNEST(hits) AS hit
  ) AS hits)
FROM `yourProject.yourDataset.yourTable`    

I tested it on public table as below:

#standardSQL
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT hit.* 
      REPLACE((SELECT AS STRUCT page.* EXCEPT(hostname)) AS page)
    FROM UNNEST(hits) AS hit
  ) AS hits)
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`

BigQuery select expect double nested column

Below is for BigQuery Standard SQL

#standardSQL
SELECT * REPLACE(
  (SELECT AS STRUCT(SELECT AS STRUCT a.b.* EXCEPT (field_name)) b)
  AS a) 
FROM `project.dataset.table`  

you can test, play with it using dummy data as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT STRUCT<b STRUCT<field_name STRING, other_field_name STRING>>(STRUCT('1', '2')) a
)
SELECT * REPLACE(
  (SELECT AS STRUCT(SELECT AS STRUCT a.b.* EXCEPT (field_name)) b)
  AS a) 
FROM `project.dataset.table`

Select All Columns Except Some in Google BigQuery?

There is nothing in current BigQuery SQL dialect that will allow it. But since this is recurring request, we have added work item to support

SELECT * EXCEPT (a, b, c) FROM ...

Update: This functionality is now available in BigQuery standard SQL. Details at https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql
Example using public wikipedia table - select all columns except title and comment:

select * except(title, comment) from publicdata.samples.wikipedia limit 10

How to filter nested column in Bigquery?

Oh, you are just missing the alias for your unnest field:

See if following works:

WITH T AS (
SELECT 
123 as CUST_ID, ["JOHN", "DOE"]  as NAME
)
SELECT * EXCEPT(NAME)
FROM T, UNNEST(NAME) as name_value
WHERE name_value LIKE '%JOHN%'

Dynamically exclude some fields from select using EXCEPT

I know, you most likely expect something more sexy, but I feel this is the only option so far

DECLARE hide_city  bool DEFAULT TRUE;

IF hide_city THEN
  SELECT * EXCEPT(city) FROM table;
ELSE 
  SELECT * FROM table;
END IF;

how to combine this with a CTE that goes before?

This can be something like below - so you "transform" your CTE into TEMP table

DECLARE hide_city  bool DEFAULT TRUE;

CREATE TEMP TABLE myTable AS 
WITH myCTE AS (
  SELECT ... UNION ALL
  SELECT ... UNION ALL
  ...
  SELECT ...
)
SELECT * FROM myCTE;

IF hide_city THEN
  SELECT * EXCEPT(city) FROM myTable;
ELSE 
  SELECT * FROM myTable;
END IF;    

Obviously you don't need CTE at all and rather can do

CREATE TEMP TABLE myTable AS 
SELECT ... UNION ALL
SELECT ... UNION ALL
...
SELECT ...;

---

Related Topics