N1QL output format Question

SQL++
1

A few questions about output of N1QL.

a) as a doc does not always have to have a field, is there a way for N1QL to return all fields no matter if they are in Doc or not and either return as null or with “” value ?

b) in some cases i check if field exists and is not null, is there a function which will also check if its an empty string in one call ? IFMISSINGORNULL(mailing_address.unit || " ", “”) Whats the best way to check if the key exists , is not null or has no empty string.

2

If you want you can return what ever you want.
Example:

SELECT IFMISSING(a,NULL) AS a , IFMISSING(b, "") AS b 
 FROM ....

 check if its an empty string    a == ""
 if the key exists , is not null or has no empty string (eliminates MISSING, NULL, "")
 IFMISSINGORNULL(a,"") != ""
3

So is there a simple way to check if the value of a key is missing , null or empty string ?

4 
eliminates MISSING, NULL, ""
IFMISSINGORNULL(a,"") != ""
5

if i follow your example it does not work
(mailing_address.address || " " || IFMISSINGORNULL(mailing_address.unit || " ", "") != "" || mailing_address.city ||" " || mailing_address.zip) as mailingAddress
whats missing ?

6

The check returns true or false and you can’t use that in concat which requires string. When unit is MISSING,NULL , “” you will have two spaces. If you don’t want that use CASE expression to control that.

CONCAT(mailing_address.address, " ", 
                      IFMISSINGORNULL(mailing_address.unit,""), " ",
                      mailing_address.city, " ",
                      mailing_address.zip) AS mailingAddress