Queries on Joins in Cosmos DB Sql API

Question

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

https://learn.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://learn.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
– Jay Gong, Commented Feb 5, 2018 at 9:07
Added some extra info which I hope explains it, why is the [0] needed in my example but not in the original? — tony
– tony, Commented Feb 5, 2018 at 14:05

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

1

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.

answered Feb 5, 2018 at 20:41

Samer Boshra

9396 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tony Over a year ago

Thanks. That was the example from the documentation. Is the documentation wrong or am I trying to use the wrong API against documentdb.com/sql/demo? Where does the [0] syntax fit into this?

Collectives™ on Stack Overflow

Queries on Joins in Cosmos DB Sql API

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related