Hi, I am playing around with couchbase analytics for past few days and faced following problems related to queries.
Problem 1: I read somewhere that N1QL is proper subset of SQL++ Specification. But, I am not able to find any functions related to datetime manipulation like N1QL has (like Date_format_str, Date_diff_str) etc.
Problem 2: Also, I am trying to create an index on a datetime field (startDate)
with query:
CREATE INDEX IX1 ON movements(startDate:DATETIME,firstName:STRING,lastName:STRING)
;
when I specifIed the field’s datatype as datetime, the query threw an error saying :
Cannot index field [startDate] on type datetime. Supported types: bigint, double, string
So I tried
CREATE INDEX IX1 ON movements(startDate:STRING,firstName:STRING,lastName:STRING)
;
And it executed successfully but when I queried firstName and lastName within a particular date range, no result is coming even when data is there for that condition. This obviously has to do with the STRING type that I specified in the Create Index statement which makes it unusable for this use case.
So, my question is, why only these three datatypes are supported in create index statement and not datetime?
As we know that a huge number of queries filter data on date range filters.
Problem 3: Where clause in CREATE INDEX statement is not working.
Query:
CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');
Response:
{
"code": 1,
"msg": "Syntax error: In line 1 >>CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');;<< Encountered \"Where\" at column 81. ",
"query_from_user": "CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');"
}
Please let me know if I’m doing something wrong here.