Reputation: 753
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:
Can someone please tell me the correct syntax perhaps for doing this IN query?
Upvotes: 5
Views: 2106
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