CODEWITHSUNDEEP
sqlnosqlazure-cosmosdbhierarchical-data
g. arshad
g. arshad

Reputation: 21

Hierarchical Queries in cosmos db(document db) SQL API

I need to query a container in cosmos db which has hierarchical links.

Let' say we have table employees:

EMP_ID MANAGER_ID NAME
120 110 JOHN
110 100 MIKE
100 NULL PAM

If I query for EMP_ID 100, the query should return all the employees which either have id 100 or come under 'PAM'. I did some research and found out that Oracle SQL supports CONNECT BY. In PostgreSQL, this can be done by recursive CTE's. I've tried both of these methods in the cosmos db SQL but none seem to work. Is there some out of the box support in cosmos db which supports these types of queries?

Upvotes: 0

Views: 1551

Answers (1)

AnuragSharma-MSFT
AnuragSharma-MSFT

Reputation: 692

The behavior you would want to see is related to joining multiple documents which is not supported in Cosmos DB. You have correctly mentioned to use CONNECT BY in oracle for the same purpose.

The answer to the query lies in properly designing the model to take care of above scenarios. I would encourage you to please read through below links that have very good description on the same.

  1. self-join on documentdb syntax error
  2. https://learn.microsoft.com/en-us/azure/cosmos-db/modeling-data

However if you are using any SDKs to connect to Cosmos DB, you can bring in entire result set simply by writing 'Select query with conditions' and then use the in-built libraries (LINQ or any other way) to model result as you want through , although it is not recommended.

Upvotes: 1

Related Questions