BigQuery Ressource in Workflows

Queries and Resources
, ,
1

I'm trying to consume a BigQuery ressource on a workflow, and this is the query : SELECT * FROM campus_staging.sessions_users WHERE session_id IN UNNEST ({{sessionsStartsToday.data}})

sessionsStartsToday.data it's an array that contains some ids.
When running this query i got this error :

{"data":null,"error":{"error":true,"message":"Syntax error: Missing whitespace between literal and alias at [6:27]","lineNumber":6,"lineCol":27,"isRetoolSystemError":false,"queryExecutionMetadata":{"estimatedResponseSizeBytes":151,"resourceTimeTakenMs":324,"isPreview":false,"resourceType":"bigquery","lastReceivedFromResourceAt":1714646883564}}}

But when i hard code the array like this for an example :

SELECT
  *
FROM
  campus_staging.sessions_users
WHERE
  session_id
IN UNNEST (["53db2dbd-084d-4389-b489"])

it works !

2

idk if this is the problem, but when you use {{ and }} its a good idea to place a space between them. I think this is in the docs somewhere also, but try this:
SELECT * FROM campus_staging.sessions_users WHERE session_id IN UNNEST ( {{ sessionsStartsToday.data }} )
sometimes the linter or compiler gets confused and reads only 1 curly bracket resulting in it thinking there's a malformed json object

3

image
image488×624 42.3 KB

Still the same problem!

4 
SELECT
  *
FROM
  campus_staging.sessions_users
WHERE
  session_id
IN UNNEST({{ JSON.stringify(sessionsEndToday.data) }})

it works like this when i convert it to JSON!

@retool_team

1 Like