Using list in WHERE condition of n1ql query

SQL++
1

cluster.n1ql_query(SELECT * FROM bucket WHERE column1 = “1” )

this retrieves the data from couchbase bucket

but instead of giving the value manually, I’ll store the value “1” in the list named “A”, now how can I use this list “A” in n1ql query to retrieve the data from couchbase bucket

2

cluster.n1ql_query(SELECT * FROM bucket WHERE column1 IN [“1”, “2”] );

1 Like
3

Hi @vsr1 ,
I don’t want to give values manually, instead saved that value “1” in a list named “A” and I want to use that list “A” to retrieve data, hope I’m clear.

I want the query in below format

cluster.n1ql_query(‘SELECT * FROM bucket WHERE column1 IN [“A”]’ )

here A = [1]

4

U mentioned A is list, where is is it field of the each document or separate document?

SELECT * FROM bucket WHERE column1 IN A;
{"column1": 1, "A":[1,2,3,4]}

If part of each document , This required full index scan because right side of the IN i.e A is not constant

Separate document retrieve the document and use it in right side of IN. Look above link and subquery handling

1 Like
5

Hi @vsr1 sir, I think I was not clear with what I wanted.

In an excel sheet there is column with name “column1” having the column values “1”, so in-order to check if records are matched with the bucket I’m retrieving the data from a couchbase bucket with name “bucket” by using the query

cluster.n1ql_query(SELECT * FROM bucket WHERE column1 = “1” )

now I don’t want to give manual column value of “column1” so I stored the column values of “column1” of excel sheet in a list using

A = df[“column1”].unique().tolist()
(here “A” will have only one distinct value that is “1”)

now what query should I use in order to get all documents from couchbase bucket with name “bucket” having the value of “column1” = “1” using the variable instead of manually giving the value as “1”.

6

You should use named parameters

SELECT * FROM bucket WHERE column1 = $A

And Set $A as your list

1 Like
7

Hi @vsr1 sir, query you mentioned is working but while converting the retrieved json data to DataFrame using :

result = cluster.n1ql_query(SELECT * FROM bucket WHERE column1 = $A )

result_df = pd.DataFrame(result)

I’m getting the below mentioned error:

N1QLError: <N1QL Execution failed, OBJ={‘code’:5010,‘msg’:‘Error evaluating filter. cause:No value for named parameter’}>

Unable to find what to do, please help

8

instead I tried the syntax in the below mentioned way:

result = N1QLQuery('SELECT * FROM bucket WHERE column1 = $A ')

result_df = pd.DataFrame(result)

now I’m getting the error as mentioned below :

ValueError: DataFrame constructor not properly called.

I feel what’s written is correct but not sure why error is showing up.

9

Check examples of named/positional parameters https://couchbase.live/

1 Like
10

Query mentioned below worked for me

result = N1QLQuery('SELECT * FROM bucket WHERE column1 IN $1 ', A)

and @vsr1 thanks alot for your responses.