CODEWITHSUNDEEP
azureazure-cosmosdbdocument-databasenosql
Sam
Sam

Reputation: 30388

SELECT with multiple values in DocumentDB

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.

Upvotes: 3

Views: 6560

Answers (5)

dinith jayabodhi
dinith jayabodhi

Reputation: 591

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.

Upvotes: 1

Andrew Liu
Andrew Liu

Reputation: 8119

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.

Upvotes: 9

Shireesh Thota
Shireesh Thota

Reputation: 31

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

Upvotes: 3

MarkKGreenway
MarkKGreenway

Reputation: 8764

Does 

 Select * from Employees where EmployeeId in (1,3,5,6,...)

Not work ?

thanks to ryancrawcour we know it doesn't

Upvotes: 0

Ryan CrawCour
Ryan CrawCour

Reputation: 2728

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.

Upvotes: 1

Related Questions