Efficient queries when unrelated types in the collection happen to share the same indexed path

[Timovzl]

When storing multiple types in one collection, we can differentiate them with a type field, for example. But what if we want to efficiently query objects of one particular type that have a particular field set to a given value (or range)? "Give me all Orders with creationDateTime >= yesterday."

The naive approach would be to let Cosmos work the problem. I deduce that it would either query two indexes (type and creationDateTime) and find the intersecting documents, or inspect each document matching the most selective field (most likely the creationDateTime) and discarding the ones mismatching the other field. Either approach inspects way more data than desirable.

Going in the direction of a collection per type would remove the ability of cross-document transactions, which is very impactful.

Perhaps the least invasive way to get the ability to query a specific type based on a single key path would be to use custom JSON field names for each document's top-level members. An Order's creationDateTime JSON field could be renamed to order_creationDateTime. With all top-level members separated, each possible path (including for nested members) becomes unique to the document's type.

{
  "id": "1",
  "order_creationDateTime": 0,
  "order_color": {
    "r": 255,
    "g": 255,
    "b": 255
  }
}

Question 1: Do you see any downsides to this solution?

There might be a prettier solution, but I'm unsure how to achieve it without reducing the usability of the code. The mangled top-level fields are ever so slightly ugly to me. I would much rather see order_creationDateTime represented as the equivalent of order.creationDateTime, i.e. an object with just an order field, which in turn holds all the originally-top-level fields like creationDateTime.

{
  "id": "1",
  "order": {
    "creationDateTime": 0,
    "color": {
      "r": 255,
      "g": 255,
      "b": 255
    }
  }
}

This would solve the problem in a more natural-looking way, in my opinion.

Question 2: How could such a result be achieved?

In theory, we could wrap each order in a wrapper with only an order member, and later query it inside such a wrapper too, but that seems highly disruptive to the workflow.