How to find documents in DocumentDB where a property (array of objects) contains an object with a given criteria

Question

I want to query a Microsoft Azure DocumentDB collection to get all documents whose "array" property (in JSON speech) contains an object which matches a given filter condition.

This is my document:

public class CouponReadModel
{
    public List<Models.BookEntry> BookEntries { get; set; }
    // some other properties

    public class Models
    {
        public class BookEntry
        {
            public Guid OrderId { get; set; }
            // some other properties
        }        
    }
}

First I tried it using ANY (TReadModel is CouponReadModel in this case)

_documentClient
  .CreateDocumentQuery<TReadModel>(_documentCollectionUri)
  .Where(coupon => coupon.BookEntries.Any(be => be.OrderId == someOrderId));

I just got the following exception:

Exception thrown: 'Microsoft.Azure.Documents.Linq.DocumentQueryException' in mscorlib.dll
Additional information: Method 'Any' is not supported.

Googling for "documentdb linq" leads me to this blog post this. Great! No problem I said to myself! The post tells me that the LINQ operator count is supported for arrays (my BookEntries collection?!). Second try!

_documentClient
  .CreateDocumentQuery<TReadModel>(_documentCollectionUri)
  .Where(coupon => coupon.BookEntries.Count(be => be.OrderId == someOrderId) > 0);

I got another exception:

Exception thrown: 'Microsoft.Azure.Documents.Linq.DocumentQueryException' in mscorlib.dll
Additional information: Method 'Count' is not supported.

Count is not supported? Just another try:

_documentClient
  .CreateDocumentQuery<TReadModel>(_documentCollectionUri)
  .Where(coupon => coupon.BookEntries.Count() > 0);

It works! OK, count is supported but only the raw count without a predicate.

What to do next? Can anybody tell me how to query DocumentDB to get what I want?

I edited out the less-savory use of language. Let's keep things mature here, shall we? — David Makogon

cnaegle cnaegle · Accepted Answer · 2016-04-18T01:21:47

I think what you are looking for is something like this:

_documentClient
 .CreateDocumentQuery<TReadModel>(_documentCollectionUri)
 .Select(coupon => coupon)                           
 .SelectMany(coupon => coupon.BookEntries
 .Where(bookEntries => bookEntries.OrderId == someOrderId)
 .Select(bookEntries => new
  {
    coupon = coupon
  }));

This will return an anonymous type that will basically be the parent document (TReadModel) and will contain the array of BookEntries.

If you are only looking for any BookEntry that matches the search criteria and not the parent document that contains the BookEntry, then change the query to this:

_documentClient
 .CreateDocumentQuery<TReadModel>(_documentCollectionUri)                         
 .SelectMany(coupon => coupon.BookEntries
 .Where(bookEntries => bookEntries.OrderId == someOrderId)
 .Select(bookEntries => bookEntries));

Hope this helps.

How to find documents in DocumentDB where a property (array of objects) contains an object with a given criteria

1 Answers