Querying sub-collection of a sub-collection in Azure DocumentDB

Question

I have an Azure DocumentDB json store within Cosmos and I am attempting to apply a mutually-exclusive conditional where clause over a collection within a collection. How I expect the query to work is not returning the expected result!

Lets say I have a Product class:

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    public IEnumerable<ProductAttribute> ProductAttributes { get; set; }
}

which has a collection of ProductAttributes which, in turn, holds a collection of strings:

  public class ProductAttribute
{
    public string Name { get; set; }

    public IEnumerable<string> AttributeValues { get; set; }
}

If I create 2 example Products and add them to the DocumentDB collection:

    var product = new Product
                {
                    Id = 1,
                    Name = "Banana",
                    ProductAttributes = new List<ProductAttribute>
                    {
                        new ProductAttribute{Name = "SourceCountry",AttributeValues = new List<string>{ "COL", "HON", "SA" }},
                        new ProductAttribute{Name = "Colours",AttributeValues = new List<string>{ "Yellow" }},
                    }
                };

                var product1 = new Product
                {
                    Id = 2,
                    Name = "Grape",
                    ProductAttributes = new List<ProductAttribute>
                    {
                        new ProductAttribute{Name = "SourceCountry",AttributeValues = new List<string>{ "FRA", "ITA", "SA" }},
                        new ProductAttribute{Name = "Colours",AttributeValues = new List<string>{ "Red", "Green" }},
                    }
                };

The documents are stored as the following JSON:

{
"id": "1",
"name": "Banana",
"productAttributes": [
  {
    "name": "SourceCountry",
    "attributeValues": [
      "COL",
      "HON",
      "SA"
    ]
  },
  {
    "name": "Colours",
    "attributeValues": [
      "Yellow"
    ]
  }
]}

and

{
"id": "2",
"name": "Grape",
"productAttributes": [
  {
    "name": "SourceCountry",
    "attributeValues": [
      "FRA",
      "ITA",
      "SA"
    ]
  },
  {
    "name": "Colours",
    "attributeValues": [
      "Red",
      "Green"
    ]
  }
]}

I would like to query Products to only return those that have attributes which match the criteria across both types of ProductAttribute.

The following query on a single attribute does return both Products as expected (both Grape and Banana include a SourceCountry of 'SA':

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')

However, I need to be able to apply criteria across both types of attribute i.e 'SourceCountry' AND 'Colours' - so I tried the below query:

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')
and (pa.name='Colours' and pav = 'Red')

I am expecting 'Grape' to be returning from the above query as this Product is the only one to have both a 'SourceCountry' of 'SA' AND a 'Colour' attribute of 'Red'.

However, no products are returned and I would very much appreciate it of someone could explain why that is the case!

Cloud SME Cloud SME · Accepted Answer · 2017-07-18T17:36:44

Total shot in the dark, but any chance that this might work:

select p.name, p.productAttributes from c as p
join pa in p.productAttributes
join pav in pa.attributeValues    
join pa2 in p.productAttributes
join pav2 in pa2.attributeValues
where (pa.name='SourceCountry' and pav = 'SA')
and (pa2.name='Colours' and pav2 = 'Red')

Querying sub-collection of a sub-collection in Azure DocumentDB

1 Answers