I know _CosmosDB SQL API_ includes Subquery support. When will the documentation be updated to include what subquery support is supported?
⚠Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.
Thanks for the question. We are currently investigating and will update you shortly.
@garfbradaz This is being assigned to the content author to evaluate and update as appropriate.
Thanks @CHEEKATLAPRADEEP-MSFT
@garfbradaz Thanks for the feedback, it's in our plan to add instructions for subqueries, distinct and querystats operators, we are working on updating the docs.
@garfbradaz We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.
Hey @CHEEKATLAPRADEEP-MSFT: Shouldn't we keep this open until the documentation is updated?
Hello,
Very curious to see an example here as well!
Hey folks. Sorry it's taken so long to get these documented. I've posted some examples you can try out today. I'm sorry I don't have a schema or sample data for you to try this out on but these should give you some idea of how to write these queries.
We are actively working on this now. I think maybe 3 weeks this will be fully documented with sample data, etc.
Thanks everyone for your patience.
Optimize JOIN Expressions
Consider the following query:
SELECT Count(1) AS Count
FROM c
JOIN t IN c.tags
JOIN n IN c.nutrients
JOIN s IN c.servings
WHERE t.name = 'infant formula' AND (n.nutritionValue > 0
AND n.nutritionValue < 10) AND s.amount > 1
For this query, the index will match any document that has a tag with the name ‘infant formula’, a nutrient item with a value between 0 and 10 and a serving item with an amount greater than 1. However, the JOIN expression here will perform the cross product of all items of tags, nutrients and servings arrays for each matching document. The WHERE clause will then apply the filter predicate on each
This query is equivalent to the one above but utilizes subqueries:
SELECT Count(1) AS Count
FROM c
JOIN (SELECT VALUE t FROM t IN c.tags WHERE t.name = 'infant formula')
JOIN (SELECT VALUE n FROM n IN c.nutrients WHERE n.nutritionValue > 0 AND n.nutritionValue < 10)
JOIN (SELECT VALUE s FROM s IN c.servings WHERE s.amount > 1)
Assuming only 1 item in the tags array matches the filter, and 5 items for both nutrients and servings arrays, the JOIN expressions will expand to 1 x 1 x 5 x 5 = 25 items as opposed to 1,000 items in the first query.
One of the most common subqueries use cases will probably involve EXISTS()
Here’s an excerpt for that:
A very common use case is to filter a document by the existence of an item in
SELECT TOP 5 f.id, f.tags
FROM food f
WHERE ARRAY_CONTAINS(f.tags, {name: 'orange'})
The same query could be rewritten to use EXISTS
SELECT TOP 5 f.id, f.tags
FROM food f
WHERE EXISTS(SELECT VALUE t FROM t IN f.tags WHERE t.name = 'orange')
Hope these are helpful.
Most helpful comment
@garfbradaz This is being assigned to the content author to evaluate and update as appropriate.