How convert object with date fields to date in N1QL

SQL++
1

Hello!
We have objects like this
"date": [
{
“dayOfMonth”: 8,
“hourOfDay”: 10,
“minute”: 13,
“month”: 10,
“second”: 7,
“year”: 2017
}
]
in our bucket. They used to form start and end keys for a View. Atm we moving to N1QL and I wonder how I can query by objects like this? Is it possible to convert it to date with N1QL date functions for compare in WHERE clause?

2

N1QL follows ISO 8601 format https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

SELECT TOSTR(date.year)
       || "-" || CASE WHEN date.month < 10 THEN "0"||TOSTR(date.month) ELSE TOSTR(date.month) END
       || "-" || CASE WHEN date.dayOfMonth < 10 THEN "0"||TOSTR(date.dayOfMonth) ELSE TOSTR(date.dayOfMonth) END
       || "T" || CASE WHEN date.hourOfDay < 10 THEN "0"||TOSTR(date.hourOfDay) ELSE TOSTR(date.hourOfDay) END
       || ":" || CASE WHEN date.minute < 10 THEN "0"||TOSTR(date.minute) ELSE TOSTR(date.minute) END
       || ":" || CASE WHEN date.second < 10 THEN "0"||TOSTR(date.second) ELSE TOSTR(date.second) END
LET date = { "dayOfMonth": 8, "hourOfDay": 10, "minute": 13, "month": 10, "second": 7, "year": 2017 };


 INSERT INTO default VALUES("1",{"date":{ "dayOfMonth": 8, "hourOfDay": 10, "minute": 13, "month": 10, "second": 7, "year": 2017} });
 SELECT  * FROM default
 LET date_str = TOSTR(date.year)
               || "-" || CASE WHEN date.month < 10 THEN "0"||TOSTR(date.month) ELSE TOSTR(date.month) END
               || "-" || CASE WHEN date.dayOfMonth < 10 THEN "0"||TOSTR(date.dayOfMonth) ELSE TOSTR(date.dayOfMonth) END
               || "T" || CASE WHEN date.hourOfDay < 10 THEN "0"||TOSTR(date.hourOfDay) ELSE TOSTR(date.hourOfDay) END
               || ":" || CASE WHEN date.minute < 10 THEN "0"||TOSTR(date.minute) ELSE TOSTR(date.minute) END
           || ":" || CASE WHEN date.second < 10 THEN "0"||TOSTR(date.second) ELSE TOSTR(date.second) END
WHERE date_str > "2017-01-01T01:00:00";
3

Thank you for the answer!
Do I need to create index for each field (year, month, etc…)?