CODEWITHSUNDEEP
javascriptnode.jsparametersgoogle-bigqueryparameter-passing
A-D
A-D

Reputation: 381

Parameterized Bigquery and Array of string values

I am running a nodejs program that runs a parameterized select query. I have the following params that I am passing to the query

  1. Id
  2. Name

In my table , id is a numeric field whereas name is a string. Now, even though the name array in the parameterized query consists of string values, the query that finally gets generated does not have the names as string and thus the query fails. If I remove the name from the query and run it with Id alone, it works. Here is what my query looks like.


ids = [1,2,3,4,5,6]
names = ["john", "rocky", "sam", "alice"]

const sqlQuery = 'SELECT * FROM `table_name_here` WHERE id IN UNNEST ([' + Ids + ']) AND name IN UNNEST ([' + Names + '])';

**final generated query that runs on bigquery**
SELECT * FROM `jenkins_notifications.prod_notifications_build` WHERE id IN UNNEST ([1,2,3,4,5,6]) AND name IN UNNEST ([john, rocky, sam, alice])

The final query should be like 

SELECT * FROM `jenkins_notifications.prod_notifications_build` WHERE id IN UNNEST ([1,2,3,4,5,6]) AND name IN UNNEST (["john", "rocky", "sam", "alice"])

Upvotes: 0

Views: 774

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173076

the simplest fastest way to fix the issue is to use below for names 

names = ["'john'", "'rocky'", "'sam'", "'alice'"]

Or, better option is using map function 

names.map(function(name) {return "'" + name + "'";}).toString()

as shown in below example 

ids = [1,2,3,4,5,6]
names = ["john", "rocky", "sam", "alice"]

const sqlQuery = 'SELECT * FROM `table_name_here` WHERE id IN UNNEST ([' + Ids + ']) AND name IN UNNEST ([' + names.map(function(name) {return "'" + name + "'";}).toString() + '])';

Upvotes: 2

Related Questions