CODEWITHSUNDEEP
c#azure-cosmosdb
Deb
Deb

Reputation: 753

Is "Where In" querying like in Sql Server possible in DocumentDB?

I have this following simple Sql Where In that I am trying in DocumentDB but am not able to make it work on the Query Explorer:

SELECT * FROM root.DocumentDbTest_AllFieldTypes f
    WHERE f.field1 NOT IN (
                            SELECT g.field1 FROM root.DocumentDbTest_AllFieldTypes g 
                            WHERE g.time = "09:12:34"
                          );

I am getting this following error: Syntax error, incorrect syntax near 'SELECT'.

Can someone please tell me the correct syntax perhaps for doing this IN query?

Upvotes: 5

Views: 2106

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

Document supports the IN operator in WHERE clauses; but it does not support subselects.

In other words... this is supported:

SELECT food.id,
       food.description,
       food.tags,
       food.foodGroup,
       food.version
FROM food
WHERE food.foodGroup IN ("Poultry Products",
                         "Sausages and Luncheon Meats")

This is not supported:

SELECT *
FROM root.DocumentDbTest_AllFieldTypes f
WHERE f.field1 NOT IN
    ( SELECT g.field1
     FROM root.DocumentDbTest_AllFieldTypes g
     WHERE g.time = "09:12:34" );

If you'd like to see subselects in DocumentDB, please voice your opinion by voting for this feature on DocumentDB's feedback page.

Upvotes: 1

Related Questions