Simulate SQL Coalesce and ISNULL using N1ql

SQL++
1

Hello, I am tasked with writting a query that has to run with values comming from a UI request, hence some of the values may no be present. Normally in SQL databases this can be achieved using COALESCE and ISNULL etc, how can I achieve the same using N1QL?

For example:
SELECT *
FROM bucket1 g LEFT JOIN bucket1 tick ON KEYS tick.tickDocId
WHERE g.metaData.refType = “c”
AND g.metaData.tombstone=false
AND tick.metaData.tombstone=false
AND UPPER(g.g.firstName)= “JOHN”
AND UPPER(g.g.lastName)=“DOE”
AND g.sCode=“GR”
AND g.sDate=“2017-08-24”
AND UPPER(tick.branch)=“MIA”
ORDER BY UPPER(g.lastName) ASC
LIMIT 10 OFFSET 0

I would like to pass any of the values on the where clause as values that could be missing and hence ommited from the final execution of the query, similar to SQL COALESCE.

I have come across the functions covered here: https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/condfununknown.html

However there wasn’t a single example there illustrating the usage of these functions.

2

If you don’t want project the row if the field is
c1 is not present in the document add predicate c1 IS MISSING
c1 is present in the document add predicate c1 IS NOT MISSING
c1 value is null in the document add predicate c1 IS NULL
c1 is not null in the document add predicate c1 IS NOT NULL

IFMISSINGORNULL(startdate,defaultdate)
returns first non-missing,non-null value
You can use like any predicate. WHERE IFMISSINGORNULL(startdate,defaultdate) > “2018-05-01”
These functions works similar to SQL COALESEC and equivalent is IFNULL(), if you want consider MISSING as NULL equivalent is IFMISSINGORNULL()

3

In my case, for example, the values “JOHN”, "DOE, “GR”, “MIA” could be not passed, I need my query to ignore those and continue, can you show with my query how would I achieve this?

4

IFMISSINGORNULL(UPPER(g.g.firstName), "") NOT IN ["JOHN", "DOE"]

OR

g.g.firstName IS NOT VALUED OR UPPER(g.g.firstName) NOT IN ["JOHN", "DOE"]

5

That worked like a charm! Thank you!

6

What does the NOT IN do in this instance?

7 
x NOT IN [“JOHN”, “DOE”] ===> NOT (x IN  [“JOHN”, “DOE”] ) ==>  NOT (x = "JOHN" OR x = "DOE")
===> (x != "JOHN" AND x != "DOE")
1 Like