How to get missing value column name from couchbase

I'm running query which is don't have current day value, i have tried ifmissing() function to get the column name but it doesn't work some case i don't know the reason.

My query is:

SELECT SPLIT(DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,‘hour'),‘T') [0],
    ADD (TONUMBER(SPLIT(SPLIT (ev.auditDetail.createTime, 'T')[1], ‘:')[0]),7) AS HOUR,
    COUNT (*) AS COUNT
    FROM data_KH ev
    WHERE type_ = ‘user’
    AND DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,‘.')[0],7,‘hour’) >
    CLOCK_TZ (‘Asia/Bangkok', '1111-11-11‘)
    AND SPLIT (loginId,‘@‘)[1] NOT IN [‘yopmail.com', ‘ymail.com', ‘accoliteindia.com']
    GROUP BY SPLIT (DATE_ADD_STR(SPLIT(ev. auditDetail.createTime,'.')[0],7,'hour'),‘T‘) [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev.auditDetail.createTime, ‘T')[1], ‘:')[0]),7)
    ORDER BY SPLIT (DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,'hour'),‘T') [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev. auditDetail.createTime, 'T')[1], ':')[0]),7)

Solution

Subquery results are ARRAY, If no results it will be 0 length (not MISSING). There is EXIST/NOT EXIST on subquery results. As you need results too and don't want repeat query twice. You can use the following approach too.

CB 6.5 or above

WITH result AS (SELECT DATE_FORMAT_STR(crdate, "2020-01-01") AS date,
                       DATE_PART_STR(crdate, "hour") AS hour,
                       COUNT (1) AS count
                FROM data_KH AS ev
                LET crdate = DATE_ADD_STR(SPLIT(ev.auditDetail.createTime, ".")[0], 7, "hour")
                WHERE type_ = "user"
                    AND crdate > CLOCK_TZ ("Asia/Bangkok", "1111-11-11")
                    AND SPLIT (loginId,"@")[1] NOT IN ["yopmail.com", "ymail.com", "accoliteindia.com"]
                GROUP BY DATE_FORMAT_STR(crdate, "2020-01-01"), DATE_PART_STR(crdate, "hour"))
SELECT d.*
FROM (CASE WHEN ARRAY_LENGTH(result) > 0 THEN result ELSE [{"date": CLOCK_TZ("Asia/Bangkok","2020-01-01"),
                                                      "hour":DATE_PART_STR(CLOCK_TZ("Asia/Bangkok"), "hour"),
                                                      "count":0}]
                                               END) AS d
ORDER BY d.date, d.hour;

Pre CB 6.50

SELECT d.*
FROM ARRAY_FLATTEN ((SELECT RAW CASE WHEN ARRAY_LENGTH(result) > 0
                                    THEN result
                                    ELSE [{"date": CLOCK_TZ("Asia/Bangkok","2020-01-01"),
                                           "hour":DATE_PART_STR(CLOCK_TZ("Asia/Bangkok"), "hour"),
                                           "count":0}]
                                    END
                    LET result = (SELECT DATE_FORMAT_STR(crdate, "2020-01-01") AS date,
                                         DATE_PART_STR(crdate, "hour") AS hour,
                                         COUNT (1) AS count
                                  FROM data_KH AS ev
                                  LET crdate = DATE_ADD_STR(SPLIT(ev.auditDetail.createTime, ".")[0], 7, "hour")
                                  WHERE type_ = "user"
                                      AND crdate > CLOCK_TZ ("Asia/Bangkok", "1111-11-11")
                                      AND SPLIT (loginId,"@")[1] NOT IN ["yopmail.com", "ymail.com", "accoliteindia.com"]
                                  GROUP BY DATE_FORMAT_STR(crdate, "2020-01-01"), DATE_PART_STR(crdate, "hour"))),1) AS d
ORDER BY d.date, d.hour;

You can also use STR_TO_TZ(ev.auditDetail.createTime,"Asia/Bangkok") instead DATE_ADD_STR(SPLIT(ev.auditDetail.createTime, ".")[0], 7, "hour") https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html