Attribute Pattern - How to sort by some attribute value

Working with Data
#1

I am trying to find a good way to be able to sort on a collection that uses the Attribute Pattern. I have a collection of “products” where each product can have up to 500 attributes. Each customer can define custom fields they want to store for all of their products

I want to be able to write a query which returns all products for a single customer having price >= 100.

I am planning on having an indexes on:

 { "customerId": 1, "attr.k": 1, "attr.v": 1 }
 { "customerId": 1, "attr.v": 1, "attr.k": 1 }

My query to filter the records would be the following I think: { "cutomerId": 1, "attr": { $elemMatch: { "attr.k": "price", "attr.v": { $gte: 100 } } } }

My question is, how do I sort by the “price” using this pattern?
Example structure of “products” collection so far:

{
    "productId": 1,
    "customerId": 1,
    "attr": [
        { "k": "price", "v": 42 },
        { "k": "color", "v": "red" }
    ]
}

Original post: How to sort custom fields using the Attribute Pattern

Attribute Pattern - sorting by attribute value
How to sort custom fields using the Attribute Pattern
#2

Hi @Pedro_Lievana ,

To be honest I investigated this issue over and over and did not find a good solution other than having the values duplicated into an additional field duplicating the name for sorting.

For example:

{
    "productId": 1,
    "customerId": 1,
    "attr": [
        { "k": "price", "v": 42 },
        { "k": "color", "v": "red" }
    ],
  sort : {
        price: 42,
       color : "red",
        ...
   }
}

Now you need to create an index on each combination as follows for equality searches :slight_smile:

{
    customer_id: "1",
    attr: {
        $elemMatch: {
            "k": "price",
            "v":  42
        }
    }
}

Indexes:

{ customerId: 1, 
  "attr.k" : 1,
  "attr.v" : 1, 
  "attr.price" : 1
}

As well as :

{ customerId: 1, 
  "attr.k" : 1,
  "attr.v" : 1, 
  "attr.color" : 1
}

However, where it becomes really complex is that when you do a range query like you showed than the “v” field in the index must come after the sort to support Equality Sort Range order rule to support index sorting:

{ customerId: 1, 
  "attr.k" : 1,
   "attr.color" : 1,
  "attr.v" : 1

}

All of this needs to be compared to what performance cost to you get when sorting in memory? Will all of those index have a bad impact on writing rather than accepting the memory read overhead…

Thanks,
Pavel

#3

Thank you Pavel for your response.

This solution is not doable for us, because the customer can filter and sort by any field. The in-memory sorting solution does not scale. If a customer has 2million products and filter returns 1 million, I cannot sort 1 million documents in memory. We are in a point where we need to scale and this is sort is stopping us.
Maybe we can do what elastic does: Does not allow to filter if result set is greater than X documents.

#4

Hi @Pedro_Lievana ,

Thats an interesting thought. Maybe using Atlas Search capabilites with boosting the score for a specific path (filed path) can help here:

I will try to test something.

Thanks
Pavel

#5