👀 Views: 39 💬 Answers: 1 📅 Created: 2025-06-16

bigquery gcp sql SQL

I'm building a feature where I need help solving I'm working with an 'Invalid Query behavior' when trying to run a query in Google BigQuery that utilizes a Common Table Expression (CTE) along with the `ARRAY_AGG` function... The query compiles without issues until I introduce the CTE. Here's a simplified version of what I'm attempting: ```sql WITH aggregated_data AS ( SELECT user_id, ARRAY_AGG(order_id) AS order_ids FROM `my_project.my_dataset.orders` GROUP BY user_id ) SELECT * FROM aggregated_data; ``` When I run this query, I get the following behavior message: ``` behavior: Invalid Query behavior: Syntax behavior: Expected "SELECT", "WITH", "EXCEPT", "ORDER", "LIMIT", "OFFSET", "UNION", "JOIN", "GROUP", "HAVING" or "INTERSECT" but got "ARRAY_AGG" at [1:1] ``` I've ensured that the syntax is correct for using CTEs but need to seem to pinpoint what might be causing this behavior. I've tried removing the CTE and running the inner query by itself, which works fine, so the scenario seems to lie with how I'm structuring the CTE. I've also checked the BigQuery documentation for any nuances regarding the use of `ARRAY_AGG` in CTEs but haven't found anything specific that applies to my case. Additionally, I verified that I have adequate permissions on the dataset and that the table exists. Any insights on why this syntax is leading to an invalid query behavior would be greatly appreciated. Is there a better approach? I'm working on a REST API that needs to handle this. Thanks for taking the time to read this!