CODEWITHSUNDEEP
sqlgoogle-bigquerydeclare
Lev
Lev

Reputation: 663

How to declare a struct type variable in Big Query?

I have a query with a couple of nested tables and all of them use a filter like this:

WHERE
    DATE(my_time) IN ("2017-08-12", "2017-08-13", "2017-08-14", ..., "2017-08-30")

I am trying to declare a STRUCT type variable in the start of the query with all the dates so that I can later replace all filters with:

WHERE
    DATE(my_time) IN struct_var

I am looking for something like this: 

DECLARE struct_var STRUCT <"2017-08-12", "2017-08-13", "2017-08-14", ..., "2017-08-30">;

but I keep getting errors and can't figure out the syntax.

Anyone knows how to fix this?

Thanks

Upvotes: 1

Views: 4273

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below example is for scripting in BigQuery Standard SQL 

#standardSQL
DECLARE dates ARRAY<DATE>;
SET dates = [DATE '2017-08-12', '2017-08-13', '2017-08-14', '2017-08-30'];

WITH sample_table AS (
  SELECT 1 id, DATE '2017-08-12' dt UNION ALL
  SELECT 2, '2017-09-13' UNION ALL
  SELECT 3, '2017-09-14' UNION ALL
  SELECT 4, '2017-08-30'
)
SELECT * FROM sample_table
WHERE dt IN UNNEST(dates);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You don't want a struct. You want an array:

declare date_array array<date>;

set date_array = [date('2017-08-12'), date('2017-08-13'), date('2017-08-14')] ;

Of course, you wouldn't use in for this purpose. You could use:

where date(my_time) in (select d from unnest(date_array) d)

Upvotes: 2

Related Questions