Azure CosmosDB not returning results with UDF in Where clause

Question

I made a UDF in my Azure CosmosDB so that I can query records where the Timestamp field equals a certain year.

For example, my query looks like this:

SELECT * FROM c WHERE c.StaticKey = 'd6c5a92203d84ce28b94cfc64a6ad4ce' 
AND udf.YEAR(c.Timestamp) = '2020' OFFSET 0 LIMIT 1

But for some reason my query always returns 0 results. I have a lot of records in my table that have a datetime from this year.

This record for exmaple DOES return 1 row and prints 2020:

SELECT udf.YEAR(c.Timestamp) FROM c WHERE c.StaticKey = 'd6c5a92203d84ce28b94cfc64a6ad4ce' OFFSET 0 LIMIT 1

My UDF in Azure looks like this:

function YEAR(datetimeString){
    var datetime = new Date(Date.parse(datetimeString));
    return datetime.getFullYear();
}

The column Timestamp contains datetime values looking like this: 2020-10-20T07:13:22.802346.

As you can see in this fiddle the javascript function works good: https://jsfiddle.net/0eozyhnv/

Anyone any idea why my UDF isn't working in my WHERE clause?

Mo B. Mo B. · Accepted Answer · 2020-11-18T20:44:59

Your UDF returns an integer. So you should write the query like this:

SELECT * FROM c WHERE c.StaticKey = 'd6c5a92203d84ce28b94cfc64a6ad4ce' 
AND udf.YEAR(c.Timestamp) = 2020 OFFSET 0 LIMIT 1

But note that this is relatively slow since UDFs can't make use of indexes. If you can format your dates in the format accepted by Cosmos DB (YYYY-MM-DDThh:mm:ss.fffffffZ) you could also use the built-in function DATETIMEPART('yyyy', c.Timestamp)=2020.

However, I suspect the fastest solution would be STARTSWITH(c.Timestamp, '2020') since this makes use of a range index on Timestamp.

Azure CosmosDB not returning results with UDF in Where clause

1 Answers