N1QL output format Question

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.

If you want you can return what ever you want.

 FROM ....

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

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

eliminates MISSING, NULL, ""

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 ?

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