linq - Querying complex nested object in cosmosdb using sql Api

Question

How query only those users whose Itemcount > 10 from the complex nested object(with dynamic key) from comosdb using sql api? UDF not preferred.

Something like,

Select c.username from c where c.Data[*].Order.ItemCount > 10;

 {
  {
    "Username": "User1",
    "Data": {
        "RandomGUID123": {
                "Order": {               
                    "Item": "ItemName123",
                    "ItemCount" : "40"
                },
                "ShipmentNumber": "7657575"
            },
        "RandomGUID976": {
                "Order": {               
                    "Item": "ItemName7686"
                    "ItemCount" : "7"
                },
                "ShipmentNumber": "876876"
             }
          }
  },     
  {
    "Username": "User2",
    "Data": {
        "RandomGUID654": {
            "Order": {               
                "Item": "ItemName654",
                "ItemCount" : "9"
            },
            "ShipmentNumber": "7612575"
        },
        "RandomGUID908": {
            "Order": {               
                "Item": "ItemName545"
                "ItemCount" : "6"
            },
            "ShipmentNumber": "6454"
        }
      }
  }
}

Answer 1

I'm not sure about how to handle unknown keys, but if you're willing to model the key as a value instead (simpler and cleaner I'd argue), you could have:

{
    "Username": "User1",
    "Data": [
        {
            "Id": "RandomGUID123",
            "Order": {
                "Item": "ItemName123",
                "ItemCount": 40
            },
            "ShipmentNumber": "7657575"
        },
        {
            "Id": "RandomGUID976",
            "Order": {
                "Item": "ItemName7686",
                "ItemCount": 7
            },
            "ShipmentNumber": "876876"
        }
    ]
}

With a query like:

SELECT DISTINCT VALUE(c.Username) 
FROM c
JOIN (SELECT VALUE d from d IN c.Data where d["Order"].ItemCount > 10)

Result:

[
    "User1"
]

"Order" is a reserved keyword and requires the bracket syntax to reference.