How to deal with array value in query?

array in sql query
sql query with array values
sql array functions
bigquery array
bigquery unnest multiple arrays
bigquery struct
sql array length
array_agg bigquery

I have a url which takes an array of topicName query like this

localhost:3000/api/?topicName=mobile&topicName=website

and sometimes topicName only has one value like this

localhost:3000/api/?topicName=mobile

and below is how I use that query values in node JS

let topicNameArray = [];
topicNameArray = req.query.topicName
let value = topicNameArray.map(function(e) {
return new RegExp(e, "i");
})

The problem I am facing is when the array query only has one value, my app will receive this message

"message": "topicNameArray.map is not a function

Can anyone suggest me a solution to deal with this problem? Thank you so much!

I believe you need to reform your querystring to mitigate the error you are experiencing.

Currently I think you have a query string like such: localhost:3000/api/?topicName=mobile&topicName=website Here you have one query argument topicName and this is essentially a string. So when you have multiple instance of topicName key in your querystring you should have been replacing the the value at this key with the last instance of it in the querystring. So to send an array of values use the querystring structure as following: localhost:3000/api/?topicName[]=mobile&topicName[]=website (notice the square brace.)

Later you can check if the query argument is an array or not. You can use Array.isArray function for that. Then you can perform your exception handling on later parts.

How to pass an array within a query string?, it in a representational format of your choice: p1=value1&pN=valueN and then decode it in your server side code. We’ve added a json_extract_array_element_text call on top of the previous query to get the first element of the array (which is zero-indexed). This returns the following table: This time we only get back the first element of the order_id = 1 record. Finally, we’re able to get the total number of array items in the items array:

you could check if topicName is an array using one of the following and convert it to an array like this

topicNameArray = req.query.topicName
if(req.query.topicName.constructor !== Array) {
   topicNameArray = [req.query.topicName];
}

Or like this,

topicNameArray = req.query.topicName
if(!topicNameArray.length) { // only arrays have length property
   topicNameArray = [req.query.topicName];
}

Array and simple queries, by removing elements from to and adding them to the back. 7.8. WITH Queries (Common Table Expressions). WITH provides a way to write subqueries for use in a larger SELECT query. The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for this query.

Currently, topicNameArray is being reassigned to req.query.topicName which is actually a string when only one topicName is given. You can check the type of req.query.topicName and if its a string turn it into an array like so:

let topicNameArray = req.query.topicName;   // assume its an array
if (typeof topicNameArray === "string")  { // check if it's a string
  topicNameArray = [req.query.topicName];   // convert to array
}

or if you prefer the look of the ternary operator:

let topicName = req.query.topicName;
let topicNameArray = (typeof topicName === "string") ? [topicName] : topicName;

Query an Array, field contains at least one element with the specified value, use the filter [ <field> => <value> ] where <value> is the element value. The name of the property in the input object (or the index of the element in the input array). The value of the property or the array element. The type (for example, string, number, boolean, array, or object). OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

Working with Arrays in Standard SQL | BigQuery, elements, you may wish to restore order to the table. An array formula references a range that doesn’t have the same number of rows or columns as the range that contains the array formula To fix this, make sure that the range referenced by the array formula has the same number of rows and columns as the range of cells in which the array formula was entered, or enter the array formula into fewer or more cells to match the range reference in the formula.

passing array of values in query (for IN condition), GENERATE_ARRAY generates an array of values from a starting and ending value and a step value. For example, the following query generates an array that​  First do a quick test. In a new workbook, type a 2 in cell A1. Type a 4 in cell B1. Then in C1 type this formula =B1-A1. If you get the #VALUE! error, go to the next step. If you don't get the error, try other solutions on this page. In Windows, open your Region control panel. Windows 10: Click Start, type Region, and then click the Region

How to Query and Filter Elements from MongoDB Arrays, To query if the array field contains at least one element with the specified value, use the filter { <field>: <value> } where <value> is the element value. To query if the array field contains at least one element with the specified value, use the filter { <field>: <value> } where <value> is the element value. If you need an array representation of the Unix time, use the getdate() function. It takes an optional Unix timestamp argument, but defaults to the value of time() if one isn’t provided.

Comments
  • You can use console.log(topicNameArray) to check topicNameArray is array or string.
  • The Array.isArray is what I need. Thanks!