Implement Paging on Azure Cosmos Db Data coming from two seperate documents

0
votes

We have two separate set of documents in Cosmos Db, one storing User and it's various roles and second set of documents storing the permission to a particular job.

Now, the job list is unbounded and can grow substantially over a period of time. As group by is not allowed on multiple documents, we are trying to figure out the best strategy to implement a way on retrieving all users either based on role or particular job.

1) Solution 1 - Keep User data and job data as sub documents in a big long document and helps with querying and even continuation tokens.

2) Solution 2 - Keep user and role data in 1 documents and multiple job documents and query on the client side separately and perform query there. In this case the continuation token support is lost, as you have to query the complete data first to provide any meaningful results.

3) Solution 3 - Keep the role data with each job document and directly query on it. In this case, we will get number of users based on job and then make single query/user to get their information.

Can anyone recommend a better solution or pick from above 3 and suggest a path forward?

1
azureazure-cosmosdb
I'd recommend that you investigate the new graph capabilities inside of CosmosDb as it makes these kinds of queries much easier to work with – Jesse Carter
Thanks Jesse for the suggestion. I have been looking at Graph Db, but the problem is our data is in SQL (Document Db) and you need to create a separate account to have graph. – user1932923
Can anyone recommend a better solution or pick from above 3 and suggest a path forward? Do you have any detail problem when implement these solutions? – Tom Sun - MSFT
The problem is using Document Db, there is no good way to implement a solution. If you go graph, you have to spin a separate account and incur more costs, its frustrating to see that Document Db has failed to implement required scenarios. Every blog says split your documents, now once you split; you walk urself into a hole where you cannot do Joins on Document Db site and have to do it on client and then we loose inbuilt paging. – user1932923
It seems that you need extra storage to store the relationship. How about using Azure sql to store the relationship of user (documentId, userid, role id), role,job. Then store the incertain property info such as userInfo into documentdb. – Tom Sun - MSFT

1 Answers

0
votes

It seems that you need extra storage to store the relationship. We could use Azure SQL to store the relationship of user(documentId, userid, role id), role,job. Then store the incertain property info such as useinfo into Documentdb.