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.
4
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()