使用SQL API在cosmosdb中查询复杂的嵌套对象 [英] Querying complex nested object in cosmosdb using sql Api

问题描述

如何仅查询那些Itemcount> 10从复杂的嵌套对象(带有动态键)从COMOSDB使用SQL API? UDF不是首选.

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

类似

从c中选择c.username，其中c.Data [*].Order.ItemCount> 10;

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"
        }
      }
  }
}

推荐答案

正如诺亚所说，将密钥建模为值是一种实现方法.

As Noah answers,model the key as a value is a way to achieve.

此外，还有另一种无需更改文档架构即可实现的方法.创建UDF的方法如下:

Additionally,there is another way to achieve without changing your schema of your document .Create UDF like this:

function getResult(data){
  for(var key in data){
    const itemCount = data[key].Order.ItemCount;
    if (parseFloat(itemCount).toString() != "NaN" && parseFloat(itemCount) > 10 ) {
　　　　return true;
　　}
  }
  return false;
}

然后运行此sql:

SELECT c.Username FROM c where udf.getResult(c.Data)

结果:

[
    {
        "Username": "User1"
    }
]

这篇关于使用SQL API在cosmosdb中查询复杂的嵌套对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！