I have an Employees collection and I want to retrieve full documents of 10 employees whose ID's I'd like to send to my SQL SELECT. How do I do that?
To further clarify, I have 10 EmployeeId's and I want pull these employees' information from my Employees collection. I'd appreciate your help with this.
Update:
As of 5/6/2015, DocumentDB supports the IN keyword; which supports up to 100 parameters.
Example:
SELECT *
FROM Employees
WHERE Employees.id IN (
"01236", "01237", "01263", "06152", "21224",
"21225", "21226", "21227", "21505", "22903",
"14003", "14004", "14005", "14006", "14007"
)
Original Answer:
Adding on to Ryan's answer... Here's an example:
Create the following UDF:
var containsUdf = {
id: "contains",
body: function(arr, obj) {
if (arr.indexOf(obj) > -1) {
return true;
}
return false;
}
};
Use your contains UDF is a SQL query:
SELECT * FROM Employees e WHERE contains(["1","2","3","4","5"], e.id)
For documentation on creating UDFs, check out the DocumentDB SQL reference
You can also vote for implementing the "IN" keyword for "WHERE" clauses at the DocumentDB Feedback Forums.
You could also achieve this by using OR support. Below is a sample –
SELECT *
FROM Employees e
WHERE e.EmployeeId = 1 OR e.EmployeeId = 2 OR e.EmployeeId = 3
If you need more number of ORs than what DocumentDB caps, you would have to break up your queries into multiple smaller queries by employeeId values. You can also issue the queries in parallel from the client and gather all the results
The best way to do this, today would be to create a Contains() UDF that took in the array of ids to search on and use that in the WHERE clause.
Another method is to use the ARRAY_CONTAINS method in the SQL API.
Here is the sample code :
SELECT *
FROM Employees
WHERE ARRAY_CONTAINS(["01236", "01237", "01263", "06152", "21224"],Employees.id).
I ran both queries ( using the IN method ) with a sample set of datasets, both are consuming the same amount of RUs.
Does
Select * from Employees where EmployeeId in (1,3,5,6,...)
Not work ?
thanks to ryancrawcour we know it doesn't
