CODEWITHSUNDEEP
azureazure-cosmosdb
Matty
Matty

Reputation: 883

DocumentDB multiple filter query on array

Using the DocumentDB query playground, I am working on a filter type of query. I have a set of attributes in my data that are set up to allow the user to search by the specific attribute. Each attribute type becomes and OR statement if multiple items are selected from the name in the name/value collection. If attributes are selected that differ (i.e. color and size) this becomes an AND statement.

SELECT food.id,
    food.description,
    food.tags,
    food.foodGroup
FROM food
JOIN tag1 IN food.tags
JOIN tag2 IN food.tags
WHERE (tag1.name = "snacks" OR tag1.name = "granola bars")
AND (tag2.name = "microwave")

This query works beautifully in the playground.

The main issue is that I have up to 12 attributes, and maybe more. Once I hit 5 joins, that is my maximum allowed number of joins, so the query below doesn't work. (note that this isn't playground data, but a sample of my own)

SELECT s.StyleID FROM StyleSearch s
JOIN a0 in s.Attributes
JOIN a1 in s.Attributes
JOIN a2 in s.Attributes
JOIN a3 in s.Attributes
JOIN a4 in s.Attributes
JOIN a5 in s.Attributes
WHERE (a0 = "color-finish|Grey" OR a0 = "color-finish|Brown" OR a0 = "color-finish|Beige")
AND (a1 = "fabric-type|Polyester" OR a1 = "fabric-type|Faux Leather")
AND (a2 = "design-features|Standard" OR a2 = "design-features|Reclining")
AND (a3 = "style_parent|Contemporary" OR a3 = "style_parent|Modern" OR a3 = "style_parent|Transitional")
AND (a4 = "price_buckets|$1500 - $2000" OR a4 = "price_buckets|$2000 and Up")
AND (a5 = "dimension_width|84 in +")

I am not 100% sure I am using the proper query to perform this, but a simple where clause per below which works in SQL brings back anything matching in the or statements so I end up with items from each "AND statement.

SELECT s.StyleID FROM StyleSearch s
JOIN a in s.Attributes
WHERE (a = "color-finish|Grey" OR a = "color-finish|Brown" OR a = "color-finish|Beige")
AND (a = "fabric-type|Polyester" OR a = "fabric-type|Faux Leather")
AND (a = "design-features|Standard" OR a = "design-features|Reclining")
AND (a = "style_parent|Contemporary" OR a = "style_parent|Modern" OR a = "style_parent|Transitional")
AND (a = "price_buckets|$1500 - $2000" OR a = "price_buckets|$2000 and Up")
AND (a = "dimension_width|84 in +")

Here is an example of the data:

{
    "StyleID": "chf_12345-bmc",
    "Attributes": [
      "brand|chf",
      "color|red",
      "color|yellow",
      "dimension_depth|30 in +",
      "dimension_height|counter height",
      "materials_parent|wood",
      "price_buckets|$500 - $1000",
      "style_parent|rustic",
      "dimension_width|55 in +"
    ]
  }

I am looking for the proper way to handle this. Thanks in advance.

Upvotes: 0

Views: 1245

Answers (1)

majita
majita

Reputation: 1306

Is it possible for you to change the structure of your document to add filter attributes specifically for your query on e.g. 

     {
        "StyleID": "chf_12345-bmc",
        "Attributes": [
          "brand|chf",
          "color|red",
          "color|yellow",
          "dimension_depth|30 in +",
          "dimension_height|counter height",
          "materials_parent|wood",
          "price_buckets|$500 - $1000",
          "style_parent|rustic",
          "dimension_width|55 in +"
        ],
        "filter_color": "red,yellow",
        "filter_fabric_type":"Polyester,leather"
      }

This would eliminate the join restriction because now your query looks something like this: 

SELECT s.StyleID FROM StyleSearch s
WHERE (CONTAINS(s.filter_color, "Grey") OR CONTAINS(s.filter_color, "Red"))
AND (CONTAINS(s.filter_fabric_type, "Polyester") OR CONTAINS(s.filter_fabric_type, "Leather"))

Of course this does mean that you have additional fields to maintain.

You might also consider writing a stored proc for this and using javascript to loop through your collection and filtering that way: DocumentDB stored procedure tutorial

Upvotes: 3

Related Questions