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?
FROM bucket1 g LEFT JOIN bucket1 tick ON KEYS tick.tickDocId
WHERE g.metaData.refType = “c”
AND UPPER(g.g.firstName)= “JOHN”
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.
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
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()