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": []
}

vsr · Accepted Answer · 2020-05-09T14:27:57.687

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

```SELECT IFMISSING(SPLIT(DATE_ADD_STR(SPLIT(auditDetail.createTime,'.')[0],7,hour'),'T')[0],NULL) AS DATE, IFMISSING(COUNT(DISTINCT owner),NULL) AS COUNT FROM data WHERE type_ = ‘Event’ AND DATE_ADD_STR(SPLIT(auditDetail.createTime,*.')[0],7,‘hour') > '2020-05-10' GROUP BY SPLIT(DATE_ADD_STR(SPLIT(auditDetail.createTime,'.')[0],7, hour’), 'T')[0]``` — KARTHIKEYAN.A, May 09 '20 at 16:15
IFMISSING i applied but it not working the able code pls check @vsr — KARTHIKEYAN.A, May 09 '20 at 16:16
not sure what you trying. it is group query it will not be missing. — vsr, May 09 '20 at 21:23
Can you please show me one group by example because if i use ifmissing() function not working to me it always return empty array @vsr — KARTHIKEYAN.A, May 13 '20 at 07:29
INSERT INTO default VALUES("k01", {"a":10}), VALUES ("k02",{"a":10, "b":"xyz"}); SELECT IFMISSING(b,"notpresent") AS b, COUNT(1) AS cnt FROM default WHERE a = 10 GROUP BY b; — vsr, May 13 '20 at 23:58

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

1 Answers1