UNNEST used to SELECT columns

Question

Is it possible to use UNNEST in a SELECT statement for selecting column names?

Consider the groceries table below:

CREATE OR REPLACE TABLE tmp.groceries AS
(
SELECT 
  "milk" AS dairy,
  "eggs" AS protein,
  "bread" AS grain
)

I want to select the protein and dairy columns. Of course, I could do this via SELECT protein, dairy FROM tmp.groceries.

My question: can I do the above query using UNNEST as below:

SELECT UNNEST(['dairy','protein']) 
FROM tmp.groceries

---

Motivation: This query is part of a python script. The table in question is massive and the columns I want differ depending on the application. In my query `['dairy','protein'] will be a variable to be unnested.

Answer 1

As @eshirvana noted in the comments, this is not possible. If you have the same motivation as myself - wanting to dynamically select columns with a python list variable - this is quite possible. The code below does this.

#inside your python script
import pandas as pd
from google.cloud import bigquery

col_names = ['dairy','protein'] 

query= f"""
    SELECT {', '.join(col_names)}
    FROM tmp.groceries
"""

#get the query as a pandas dataframe
df = bigquery.Client().query(query, location="US").to_dataframe()