N1QL Conditions support

SQL++
1

Hello,

We are evaluating N1QL. For POC, we used XDCR to ElasticSearch(EL) and querying data using EL. As far as now EL satisfies our need. But we really want to move to N1QL if N1QL can satisfy our needs.

Can N1QL support below type of condition?

(Condition1 OR Condition2) AND (Condition3 OR Condition4) AND (Condition5)

For Example, below is the document structure -
{
UserID: 1
Username: "ABC"
Roles: [
{Role1},
{Role2},
{Role3},
{Role4}
]
}

Our search condition can be - (Role1 OR Role2) AND (Role4)

Also is there any limit on conditions?

Thanks,
Sameer

2

Hi Sameer,

As far as I know there is no limit on the number of conditions in a query. If you do find that there is a such a limit, please let us know.

Cheers,
Manik

3

HI Manik,

Thank you for your reply.

Can you also answer to the main question? We really need to check the AND\OR query condition support using N1QL.

Thanks,
Sameer

4

My apologies. Yes, AND/OR combinations are supported.

Cheers,
Manik

5

Hi Manik,

Thank you very much.

It would be great if you can provide us some example on such type of query.

Thank you again.

Regards,
Sameer

6

Sameer,
Don’t forget to look here: http://docs.couchbase.com/prebuilt/n1ql/n1ql-dp4/N1QLRef-DP4.pdf
There are many examples. I found one on Page 57, 65, 102, and 107. Additionally I think you can find a few in the tutorial as well. http://query.couchbase.com/ and choose the tutorial link. Click the index at the bottom and peruse all the different query examples.

I hope this helps!
Austin

7

Sameer,

To add to what Austin said, for your specific usecase you will need to use the UNNEST. The UNNEST operator performs an in-document join of the array with the parent document. Please refer to our documentation or the tutorial for examples on how to perform UNNEST.

To give you a quick primer on how to use this feature. Consider a document that looks like this :
{
“contacts”: {
“children”: [
{
“age”: 17,
“gender”: “m”,
“name”: “abama”
},
{
“age”: 21,
“gender”: “m”,
“name”: “bebama”
}
],
“hobbies”: [
“golf”,
“surfing”
],
“name”: “ian”,
“type”: “contact”
}
}

The query “select contacts.name, child from contacts unnest contacts.children as child;” Will produce the following results :

{
“child”: {
“age”: 17,
“gender”: “m”,
“name”: “abama”
},
“name”: “ian”
},
{
“child”: {
“age”: 21,
“gender”: “m”,
“name”: “bebama”
},
“name”: “ian”
}

Once you have unnested the array with the parent document then you can apply the conditional filters to output the results that you are looking for.

Hope this helps.

Cheers,
Manik

8

Thank you Austin/Manik.

Thanks,
Sameer

9

Just came across this thread. Sorry for my late question.

Expanding on Manik’s reply, I want to do a query like this: “select contacts.name, child from contacts unnest contacts.children as child where child.age > 20”. Is there anyway to create a secondary index child.age? In more general terms, I am looking for ways to create index on object fields inside an array field, something similar to complex keys in MongoDB.