CODEWITHSUNDEEP
azureazure-cosmosdb
akshay pai
akshay pai

Reputation: 122

how to provide result of one query to another in azure document DB

if i have to pass a list of numbers got from one select statement to another select statement both working on the same collection, how will I be able to perform that ?

Query: 

SELECT VALUE student.studentId 
FROM student 
where student.acctType="student"

This gives me a result:

["1","2","3","4"]

The query, if I had those values 

SELECT student.firstName,
       student.lastName
from student 
where student.acctType="student" AND 
      student.studentId IN ("1","2","3","4")

This is what I tried but did not work : 

SELECT student.firstName,
       student.lastName
from student 
where student.acctType="student" AND 
      student.studentId IN ( SELECT VALUE student1.studentId 
FROM student1 
where student1.acctType="student")

Upvotes: 1

Views: 775

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

DocumentDB has an SQL-familiar query syntax but it's still a NoSQL database. As such, it has no support for inter-document joins and no support for sub-queries. That's the bad news.

The good news is that DocumentDB allows you to write stored procedures (sprocs) to accomplish everything you could with full SQL and much much more. You could write a sproc that did the query that returned the list of numbers, then use that list of numbers to compose another query to get your the data you need.

That said, for the simple use case you describe, you could accomplish the same thing client-side with two round trips.

Upvotes: 3

Related Questions