CODEWITHSUNDEEP
sqlazureazure-cosmosdbazure-cosmosdb-sqlapi
John Dou
John Dou

Reputation: 23

How to implement complex SQL statements in cosmos DB, such as JOIN IN and GROUP BY

I have some items in Cosmos DB.

In Master collection:

{ "id": "12a8af32-c8df-41f8-a253-37c900199113", "BuildingAddr": "123", "province": "jiangsu", "city": "shanghai", "UnitNumber": "F8N7S777" }

In Callback collection:

{ "id": "818e6f3f-60fb-4c38-926f-296e410dfe19", "UnitNumber": "R2NT3670", "RequestTime": 1578542878, "status": 1, "StorageTime": 15785428456, }

I had tried like this:

SELECT Master.city, COUNT(Callback.id) AS errorNumber FROM Callback JOIN Master IN Callback.UnitNumber WHERE Callback.UnitNumber IN ("F8NKH287", "F8NKH288") AND Callback.status IN (1, 4, 5) AND Master.province = "jiangsu" GROUP BY call.city

I want to select items in Callback which Callback.UnitNumber = Master.UnitNumber and Callback.UnitNumber in given UnitNumber, and Callback.status in given status, and Master.province equals given province, then group by Master.city. Too much normal SQL statements doesn't work in cosmos db. That makes me having a headache, and now I feel so desperate! Please help me, thanks!

Upvotes: 1

Views: 194

Answers (1)

Mark Brown
Mark Brown

Reputation: 8763

Cosmos DB is not a relational database and does not support ANSI SQL constructs like cross collection joins. If you are new to this type of database I recommend starting with this set of docs to understand key concepts for designing for a NoSQL datastore.

NoSQL vs Relational

Modeling data

How to model and partition data - real world example

Thanks.

Upvotes: 1

Related Questions