Convert array-of-strings to array-of-type

Question

I have an array of strings that are valid dates and I want to convert them into an array of dates.

Is there a more straightforward way to do the following in BigQuery?

SELECT CAST(['2014-01-01', '2015-01-01'] AS ARRAY<date>)

Currently, I'm having to do the following workaround:

SELECT ARRAY(select cast(_ as date) FROM UNNEST((select ["2014-01-01", "2015-01-01"])) _)

The input/output should be:

IN:  ["2014-01-01", "2015-01-01"] 
--> 
OUT: [DATE "2014-01-01", DATE "2015-01-01"]

please clarify! so, let's say you have initial array of string and now you want to transform it into array of dates? is this the case? — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 11, 2022 at 1:05
@MikhailBerlyant yes, exactly. How to get: ["2014-01-01"] into [DATE "2014-01-01"] — David542
– David542, Commented Oct 11, 2022 at 1:31
I think the way you do is the only way - select array_strings, array(select date(_) from t.array_strings _) array_dates from your_table as t obviously assuming those strings are dates, otherwise you do that extra safe_cast(_ as date) . Note safe_cast not just cast — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 11, 2022 at 2:24
I don't feel my above comments really qualified as an answer :o( they are just comments — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 12, 2022 at 3:40

Scott B · Accepted Answer · 2022-10-13 05:44:21Z

2

There is already a filed feature request for this function. You may click on +1 to bring more attention to the feature request and also STAR it for you to be notified for the updates.

Currently, there is no other workaround to achieve this other than what you mentioned.

SELECT ARRAY(select cast(_ as date) FROM UNNEST((select ["2014-01-01", "2015-01-01"])) _)

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.

answered Oct 13, 2022 at 5:44

community wiki

Scott B

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert array-of-strings to array-of-type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related