CODEWITHSUNDEEP
azure-cosmosdb
Bob Tabor
Bob Tabor

Reputation: 987

DocumentDb Repository to Query Child Documents Generically

In DocumentDb, is it possible to search for child documents that meet a certain criteria without having to involve the parent class in the query?

BACKGROUND

I'm (trying to start by) using the DocumentDbRepository.cs that is generated for you automatically in the Azure Portal when you create a new Azure Cosmos DB account. However, it's obvious that this was meant merely as a starting point and will require some additional work for individual scenarios.

In a C# Console app (.NET Core) I have a simple parent-child relationship between Company and Employees:

public class Customer
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "name")]
    public string Name { get; set; }

    [JsonProperty(PropertyName = "location")]
    public string Location { get; set; }

    [JsonProperty(PropertyName = "employees")]
    public List<Employee> Employees { get; set; }

    public Customer()
    {
        Employees = new List<Employee>();
    }
}

public class Employee
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "firstName")]
    public string FirstName { get; set; }

    [JsonProperty(PropertyName = "lastName")]
    public string LastName { get; set; }

    [JsonProperty(PropertyName = "sales")]
    public double Sales { get; set; }

}

In the Document Explorer, I can see that I have one instance of this class structure like so:

{
  "id": "7",
  "name": "ACME Corp",
  "location": "New York",
  "employees": [
    {
      "id": "c4202793-da55-4324-88c9-b9c9fe8f4b6c",
      "firstName": "John",
      "lastName": "Smith",
      "sales": 123
    }
  ]
}

If I wanted to get all Companies that meet a certain criteria, it would be a fairly easy operation using the generated DocumentDbRepository.cs methods:

DocumentDBRepository<Customer>.Initialize();
var customers = DocumentDBRepository<Customer>.GetItemsAsync(p => p.Location.Equals("New York")).Result;

... for reference, the generated GetItemsAsync() from Microsoft method looks like this:

    public static async Task<IEnumerable<T>> GetItemsAsync(Expression<Func<T, bool>> predicate)
    {
        IDocumentQuery<T> query = client.CreateDocumentQuery<T>(
                UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
                new FeedOptions { MaxItemCount = -1 })
            .Where(predicate)
            .AsDocumentQuery();

        List<T> results = new List<T>();
        while (query.HasMoreResults)
        {
            results.AddRange(await query.ExecuteNextAsync<T>());
        }

        return results;
    }

PROBLEM

HOWEVER, if I want to retrieve ONLY EMPLOYEES regardless of the Company they belong to, I'm not sure how to write a method in the repository class that will accomplish this.

First, I think I'll need some sort of type property so I can differentiate what a Customer is versus an Employee (versus other domain class types I may want to also add in the same collection).

Second, I would probably query that using that type property for all queries and not use the DocumentDbRepository.cs methods which seem to only work with root data. In other words, the DocumentDbRepository.cs methods seem to only be concerned with non-hierarchical entities.

But this is where things break down ... given the generic nature of this sample repository class, I can't quite connect the dots in my mind required to query sub-documents / children.

I am merely asking for a nudge in the right direction here. Thank you.

Upvotes: 2

Views: 1674

Answers (1)

Tom Sun
Tom Sun

Reputation: 24549

I want to retrieve ONLY EMPLOYEES regardless of the Company

If I understanding correctly, you want to query employees according employees' property from Customer. If it is that case, we could do that with SQL as following, and we just need to change the where <filter_condition> as we want.

SELECT c as employees
FROM c IN Customer.employees
WHERE c.firstName = 'John'

I test with your mentioned document from Azure portal, it works correctly on my side.

enter image description here

The following is the c# demo code:

var endpointUrl = "https://yourdocumentdbname.documents.azure.com:443/";
var authorizationKey = "xxxxx";
var databaseId = "database name";
var collectionId = "collecton name";
var client =  new DocumentClient(new Uri(endpointUrl), authorizationKey);
var sql = "SELECT c as employee FROM c IN Customer.employees WHERE c.firstName = 'xx'";
var collection = client.CreateDocumentCollectionIfNotExistsAsync(
            UriFactory.CreateDatabaseUri(databaseId), new DocumentCollection
            {
                Id = collectionId

            }).Result.Resource;
var query = client.CreateDocumentQuery(collection.SelfLink, sql).AsDocumentQuery();

while (query.HasMoreResults)
{
  var documents =  query.ExecuteNextAsync().Result;
  //do something
}

Upvotes: 3

Related Questions