How to get Filed name (Column Name) in couchbase if no result found using N1QL query?

Question

I'm writing query to find an user information in Couchbase,I'm getting result as empty array but i want fields with empty result, I was tried couple of time but i won't get the fields the result which are having empty value. How to get the header fields?

My Query is

SELECT C.firstName AS `First Name`,
C.surName `Last Name`,
U.auditDetail.createTime AS `Date/Time of the Registration`
C.contactDetails.`email`.`value` AS `Email Address`,
C.contactDetails.`phone`.`value` AS ‘Phone Number`,
C.sex `Gender`,
C.dob AS `Data of Birth`,
ag.id AS `Referral Code`,
C.addressDetails.`office_new`.`zipcode` AS ‘Postal Code`,
C.addressDetails.`office_new.`city` AS `City`
FROM data C
UNNEST C.activeGroups AS ag
JOIN data_PH U ON U.loginld=C.contactDetails.`email`.`value`
WHERE U.type_='user'
AND C.type_= ‘customer`

Result:

{
  "results": []
}

Answer 1

When you execute query the signature has the header of the fields. This is like Describe columns of RDBMS.

SELECT
  C.firstName AS `First Name`,
  C.surName `Last Name`,
  U.auditDetail.createTime AS `Date/Time of the Registration`,
  C.contactDetails.`email`.`value` AS `Email Address`,
  C.contactDetails.`phone`.`value` AS `Phone Number`,
  C.sex `Gender`,
  C.dob AS `Data of Birth`,
  ag.id AS `Referral Code`,
  C.addressDetails.`office_new`.`zipcode` AS `Postal Code`,
  C.addressDetails.`office_new`.`city` AS City
FROM default AS C
UNNEST C.activeGroups AS ag
JOIN default AS U ON U.loginld=C.contactDetails.`email`.`value`
WHERE U.type_="user"
      AND C.type_= "customer";

{
    "requestID": "c4a806b6-b3d7-4172-8b60-ebe195d00cef",
    "signature": {
        "City": "json",
        "Data of Birth": "json",
        "Date/Time of the Registration": "json",
        "Email Address": "json",
        "First Name": "json",
        "Gender": "json",
        "Last Name": "json",
        "Phone Number": "json",
        "Postal Code": "json",
        "Referral Code": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.723082ms",
        "executionTime": "9.616195ms",
        "resultCount": 0,
        "resultSize": 0
    }
}

If field value is MISSING the filed will not present in JSON to save size of Json. If you really want to project as default values("") use IFMISSING() or IFMISSINGORNULL() or IFNULL()

This only works when you have results. zero results still gives empty objects.

 IFMISSING(C.firstName,"") AS `First Name`  --project MISSING field as empty string    
 IFMISSING(C.firstName, NULL) AS `First Name` --project MISSING field as NULL