[*] and IN in the WHERE clause

SQL++
1

Two things that are either missing, or I don’t know what I’m doing :slight_smile: I can’t find anything in the documentation to cover the following cases in the WHERE clause.

Case #1 - IN Clause

SELECT propertyA, propertyB FROM bucket WHERE propertyB IN ("A","B","C")

This is a staple in data engineering, am I missing something? Is there an alternate way to do this in N1QL?

Case #2 - Arrays

Say you have the following array inside a document:

"orderDetails": [
    {   "orderDetailId": 1,
        "productCode": 123},
    {   "orderDetailId": 2,
        "productCode": 456},
    {   "orderDetailId": 3,
        "productCode": 123}
]

The following syntax seems “chubby”:

WHERE ANY child IN orderDetails SATISFIES
 child.productCode = 123
END

Curious why the following isn’t supported:

WHERE orderDetails[*].productCode = 123

I’ve seen the [*] used in some other syntax, but it appears to not work as I was expecting when used in the WHERE clause.

Thanks,

1 Like
2

The IN operator evaluates to TRUE if the right-side value is an array and directly contains the left-side value.
FYI

you should change ("A","B","C") to ["A","B","C"]

in your case, this will be

[123,456,123]

so orderDetails[*].productCode = 123 is not equivalent to ANY child IN orderDetails SATISFIES child.productCode = 123 END

3 Likes
3

So from @atom_yang’s answer, you can do:

WHERE 123 IN orderDetails[*].productCode

1 Like
4

You guys are awesome as always, thanks again for the super quick responses. :slight_smile: Do you have a link to the documentation for the “IN” statement?

5

I have posted here, FYI

1 Like
6

BTW, you can quickly find the answer by google with N1QL IN keywords.

7

ANY syntax allows to use ARRAY indexing feature and get the data faster. The following query uses ix1 index.

CREATE INDEX ix1 ON default (DISTINCT ARRAY child.productCode FOR child IN orderDetails END);
SELECT propertyA, propertyB FROM default WHERE ANY child IN orderDetails SATISFIES child.productCode = 123 END;

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
https://blog.couchbase.com/1-making-most-of-your-arrays-with-covering-array-indexes-and-more/

8

I see it on Google now that I’ve made the mental shift to “Collection Operators”. As general feedback coming from a SQL Server world, a large portion of the N1QL documentation is pretty light on examples. While now it makes sense after seeing your example, the documentation on the collections operators page doesn’t have any examples of manually constructing an array on the RHS.

Not the end of the world, just general feedback that more examples are helpful for people coming from other query languages.

Thanks,

9

Thank you for the feedback on documentation. Copying @prasad and @keshav_m

10

added https://issues.couchbase.com/browse/DOC-2229
Btw, constructor operators are explained at https://developer.couchbase.com/documentation/server/4.6/n1ql/n1ql-language-reference/constructionops.html

11

I have a problem with some data that i have to skip in N1QL paths. For example

“paths”: {
“/emv3dsecure/v1/devicedatacollection”: {
“post”: {
“summary”: “EMV 3D SEcure Device Data Collection API”,
“parameters”: [
{
“name”: “ProfileId”,
“in”: “query”,
“description”: “ProfileId for Cors Authentication”,
“required”: true,
“style”: “form”,
“explode”: true,
“schema”: {
“type”: “string”
}
}
]
},
“post”: {
“summary”: “EMV 3D Secure StepUp API”,
“parameters”: [
{
“name”: “ProfileId”,
“in”: “query”,
“description”: “ProfileId for Cors Authentication”,
“required”: true,
“style”: “form”,
“explode”: true,
“schema”: {
“type”: “string”
}
}
]

I have to skip “/emv3dsecure/v1/devicedatacollection” and search for nested fields in parameters array. The level “/emv3dsecure/v1/devicedatacollection” contains information that i am not able to know.
I tried
OBJECT_VALUES(paths)[0].post path in WHERE Clause but it returns only the data below the first post field. Why OBJECT_VALUES(paths)[*].post is not working? Is there any other way to solve that problem?

12

It will work . post becomes ARRAY. I would recommend first do in projection and see how the structure then try use in WHERE clause.

SELECT OBJECT_VALUES(paths)[*].post    
 FROM  mybucket ;