DocumentDB multiple filter query on array

Question

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.

majita · Accepted Answer · 2016-10-13 06:16:42Z

3

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

answered Oct 13, 2016 at 6:16

majita

1,34614 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matty Over a year ago

I'm use to extra columns when getting use to NoSQL. =) DateTime fields are so much fun. I like this approach... might have to look at a hybrid to this. The problem would arise when I have Red and Autumn Red as Values. That query could match Red on both. Maybe "filter_color" : "filter_color|red,filter_color|autumn red" with the search CONTAINS(s.filter_color, "filter_color|red") or a more simple qualifier. I'll try this out and get back to you.

Matty Over a year ago

I added a field that just contains all of the filter and values. The field data looks like this: "|filter_color|red|,|filter_color|autumn red|..." I put pipes around the fields which makes it easy to do a contains without having issues with matches. Also, I only have one additional field that is just used for this. Insanely fast.

Collectives™ on Stack Overflow

DocumentDB multiple filter query on array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related