Queries on Joins in Cosmos DB Sql API

Question

This document has an example query to run against a Cosmos DB SQL API

https://docs.microsoft.com/en-us/azure/cosmos-db/tutorial-query-sql-api#example-query-2

SELECT c.givenName 
FROM Families f 
JOIN c IN f.children 
WHERE f.id = 'WakefieldFamily'
ORDER BY f.children.grade ASC

Using https://www.documentdb.com/sql/demo

I couldn't get a similar example to work until I put a [0] into the order by. I just got {}. NB I know the URL says documentdb but the page header on that page says Cosmos DB

SELECT food.servings[0].amount
FROM ROOT food
WHERE food.id = "19015"
order by food.servings[0].amount

Should it work?

Is there a document somewhere that covers all the queries you can run against this API?

(extra info)

(1) Go to https://www.documentdb.com/sql/demo

(2) Put this query in against it

SELECT food.servings[0].amount
FROM ROOT food
WHERE food.id = "19015"
order by food.servings[0].amount

It works, but this query looks nothing like the example in https://docs.microsoft.com/en-us/azure/cosmos-db/tutorial-query-sql-api#example-query-2, it uses a '[0]' which isn't needed in the original example.

Hi, tony. Not sure what you're looking for. Do you mean the sample order by sql doesn't work for you? What's the meaning of covering all the queries? — Jay Gong
Added some extra info which I hope explains it, why is the [0] needed in my example but not in the original? — tony

Samer Boshra Samer Boshra · Accepted Answer · 2018-02-05T20:41:42

This is actually not supported. You should've gotten an error indicating this but for some reason the query silently returns no results. If you replace 'f.children' by the alias 'c' in the ORDER BY clause, you'd get the expected error:

SELECT c.givenName 
FROM Families f 
JOIN c IN f.children 
WHERE f.id = 'WakefieldFamily'
ORDER BY c.grade ASC

You should get this error:

Order-by over correlated collections is not supported.

Queries on Joins in Cosmos DB Sql API

1 Answers