The value has to be of type 'string | number', you provided an array

Queries and Resources
,
1

I'm trying to update an array of records within BigQuery. We're currently using the GUI mode, but get the following error:

Value of type ARRAY<STRUCT<video_id INT64, date_added STRUCT<value STRING>>> cannot be assigned to videos, which has type ARRAY<STRUCT<video_id INT64, date_added DATETIME>> at [1:48]

It seems that no matter how I try to format the datetime stamp using new Date() or moment, it's always being sent as a String. How can I make sure that it's always datetime?

The only workaround that I've seen are to use manual SQL but due to the nature of our query that would also require us to disable prepared statements, which we want to avoid.
Screenshot 2024-10-30 at 08.44.52

I've had a search on the forum but struggled to find something applicable

2

Hi @sk-benbateman I'm looking into this bug :thinking: any chance you could share the version that works with prepared statements disabled?

3

Hey (sorry for the slow reply)

So basically I didn't try it through the GUI with prepared statements disabled, I just built the SQL with prepared statements disabled.

So instead I ran this query:

UPDATE [TABLE_NAME]
SET videos = [(905551, DATETIME("2024-10-17T14:46:57.873834")), (906722, DATETIME("2024-10-17T14:46:57.873834")), (906716, CURRENT_DATE()), (906346, CURRENT_DATE())]
WHERE playlist_id = "c407bb03-0fda-4f19-801f-a90302ceb9f4"
1 Like