import sqlalchemy as sa
from pybigquery import array, struct
from pybigquery.sqlalchemy_bigquery import BigQueryDialect

engine = sa.create_engine("bigquery://<your connstr here>")
engine.execute(
    """
    create table tmp.test_struct as (
        select 'a' as id, struct(1 as x__count, 2 as y__count, 3 as z__count) as dimensions
    )
    """
)

table = sa.Table("tmp.test_struct", sa.MetaData(bind=engine), autoload=True)
# This would obviously be cleaner if the RECORD built these natively.
dimensions = struct([], table.c.dimensions.name)
query = sa.select([table.c.id, (dimensions["x__count"] + 5).label("x")])

print(query.compile(dialect=BigQueryDialect(), compile_kwargs={"literal_binds": True}))

import sqlalchemy as sa
from pybigquery import array, struct
from pybigquery.sqlalchemy_bigquery import BigQueryDialect

engine = sa.create_engine("bigquery://<your connstr here>")
engine.execute(
    """
    create table tmp.test as (
        select 1 as x__count, 2 as y__count, 3 as z__count
    )
    """
)

table = sa.Table("tmp.test", sa.MetaData(bind=engine), autoload=True)
pivot_unnest = sa.func.unnest(
    array(
        [
            struct([sa.literal(dim).label("dimension"), col.label("count")])
            for dim, col in {
                "x": table.c.x__count,
                "y": table.c.y__count,
                "z": table.c.z__count,
            }.items()
        ]
    )
).alias("pivot")
pivot = struct([sa.column("dimension"), sa.column("count")], field=pivot_unnest.name)
query = sa.select(
    [pivot["dimension"].label("dimension"), pivot["count"].label("count")]
).select_from(table.join(pivot_unnest, sa.literal(True)))

print(query.compile(dialect=BigQueryDialect(), compile_kwargs={"literal_binds": True}))

import sqlalchemy as sa
from pybigquery import array, struct
from pybigquery.sqlalchemy_bigquery import BigQueryDialect

engine = sa.create_engine("bigquery://")
engine.execute(
    """
    create or replace table tmp.test_struct as (
        select 'a' as id, struct(1 as x__count, 2 as y__count, 3 as z__count) as dimensions
    )
    """
)

table = sa.Table("tmp.test_struct", sa.MetaData(bind=engine), autoload=True)

print(list(engine.execute(sa.select([sa.literal(["a"])]))))
# [(['a'],)]
engine.execute(sa.select([sa.literal([table.c.id])]))
# ProgrammingError: (google.cloud.bigquery.dbapi.exceptions.ProgrammingError) Encountered unexpected first array element of parameter param_1, cannot determine array elements type.
# [SQL: SELECT %(param_1)s AS `anon_1`]
# [parameters: {'param_1': [Column('id', String(), table=<tmp.test_struct>)]}]
# (Background on this error at: http://sqlalche.me/e/13/f405)

print(list(engine.execute(sa.select([array(["a"])]))))
# [(['a'],)]
print(list(engine.execute(sa.select([array([table.c.id])]))))
# [(['a'],)]