N1QL Conditions support

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

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

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

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

Cheers,
Manik

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

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

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

Thank you Austin/Manik.

Thanks,
Sameer

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.