Cosmos DB Udf not working with Where condition

Question

By referring this i have created a udf to apply date filter ,

Convert TimeScript to Date in Azure CosmosDB SQL Query.

SELECT udf.convertTime(c.DatetimeEpoch) as CommandDate FROM c where c.columnH='acb' and c.CommandDate = '09/21/2019',

But this query doesnot work when i gave the udf out put in where clause.

It wont, because your "where" clause is based on data in the table, not the subset of data selected by your query. You would need to use something like a subquery, or a cte, or a temp table or a table variable to hold the results, and then query those separately. — Unicorno Marley
– Unicorno Marley, Commented Sep 22, 2019 at 3:28
After adding UDF to where clause it worked fine. As i am new to cosmos db not sure about the performnace. Which is the best practice out of 1) Use C# logic for filtering 2) UDF as i have written alreasy — jubi
– jubi, Commented Sep 23, 2019 at 4:27

subseven · Accepted Answer · 2019-09-23 02:50:04Z

0

In CosmosDb, you can't use the alias CommandDate in the where clause, unlike in MSSQL. You have to repeat the udf again in the where clause as @Tony Ju answered.

answered Sep 23, 2019 at 2:50

subseven

891 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aravind Krishna R. · Accepted Answer · 2019-09-23 21:44:52Z

0

I think @Unicorno Marley has explained clearly why your sql didn't work.

You can modify your sql as below:

SELECT udf.convertTime(c.DatetimeEpoch) as CommandDate FROM c where c.columnH='acb' and udf.convertTime(c.DatetimeEpoch) = '09/21/2019'

EDIT: You can now use sub-query to avoid repeat execution of UDFs

SELECT s.CommandDate 
FROM (
    SELECT udf.convertTime(c.DatetimeEpoch) AS CommandDate 
    FROM c 
    where c.columnH = 'acb'
) s
WHERE s.CommandDate = '09/21/2019'

Ideally though you should perform this mapping client side instead of server-side UDFs, because UDF filters in the WHERE clause would be scans.

edited Sep 23, 2019 at 21:44

Aravind Krishna R.

8,00332 silver badges39 bronze badges

answered Sep 23, 2019 at 2:44

caiyi Ju

15.8k3 gold badges24 silver badges34 bronze badges

2 Comments

jubi Over a year ago

@Unicorno Marley Which is the best option UDF or filtering through C# code?

caiyi Ju Over a year ago

@jubi No documentation indicates that this will affect performance. Here is an document about performance tips for .net sdk. Hope it helps.

Collectives™ on Stack Overflow

Cosmos DB Udf not working with Where condition

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related