azure-cosmosdb - 从条件中仅返回子集的子集

Question

是否可以对孩子进行条件查询？我正在尝试返回所有组，以及所有计数大于 0 的子组（以及子组）。请注意，如果计数大于 0，子组也需要返回子组。

文档：

{
  "id": 1,
  "name": "name1",
  "groups": [
    {
      "id": 1,
      "name": "name1",
      "subGroups": [
        {
          "id": 1,
          "name": "name1",
          "count": 4,
          "assests": [ "asset1", "asset2" ]
        },
        {
          "id": 2,
          "name": "name2",
          "count": 0,
          "assests": [ "asset1", "asset2" ]
        }
      ]
    },
    {
      "id": 2,
      "name": "name2",
      "subGroups": [
        {
          "id": 1,
          "name": "name1",
          "count": 4,
          "assests": [ "asset1", "asset2" ]
        },
        {
          "id": 2,
          "name": "name2",
          "count": 0,
          "assests": [ "asset1", "asset2" ]
        }
      ]
    }
  ]
}

想要的结果：

{
  "id": 1,
  "name": "name1",
  "groups": [
    {
      "id": 1,
      "name": "name1",
      "subGroups": [
        {
          "id": 1,
          "name": "name1",
          "count": 4,
          "assests": [ "asset1", "asset2" ]
        }
      ]
    },
    {
      "id": 2,
      "name": "name2",
      "subGroups": [
        {
          "id": 1,
          "name": "name1",
          "count": 2,
          "assests": [ "asset1", "asset2" ]
        }
      ]
    }
  ]
}

我尝试通过加入来做，但还没有找到一种可以跳过一些子组而不是其他子组的方法。欢迎所有建议。

标签： azure-cosmosdbazure-cosmosdb-sqlapi

Answer 1

我建议你使用存储过程来实现你想要的结果。请参考我的示例代码：

function sample() {
    var collection = getContext().getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var returnArray = [];                      
            for(var i=0 ;i<feed.length;i++){
                var groupsArray = feed[i].groups;
                var map ={};    
                var groups = [];
                for(var j=0 ;j<groupsArray.length; j++){
                    var map1 = {};
                    map1["id"] = groupsArray[j].id;
                    map1["name"] = groupsArray[j].name;

                    var subGroupsArray = groupsArray[j].subGroups;
                    var sub = [];
                    for(var k=0 ;k<subGroupsArray.length;k++){
                        if(subGroupsArray[k].count > 0)
                            sub.push(subGroupsArray[k]);
                    }                    
                    map1["subGroups"] = sub;
                    groups.push(map1);
                }
                map["id"] = feed[i].id;
                map["name"] = feed[i].name;
                map["groups"] = groups;
                returnArray.push(map);
            }
            var response = getContext().getResponse();

            response.setBody(returnArray);

        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

---

好吧，也许您可​​以在 SQL 查询中使用 UDF。但是，您仍然需要在 udf 函数中循环数组。

function test(groupsArray){                      
    var map ={};    
    var groups = [];
    for(var j=0 ;j<groupsArray.length; j++){
        var map1 = {};
        map1["id"] = groupsArray[j].id;
        map1["name"] = groupsArray[j].name;

        var subGroupsArray = groupsArray[j].subGroups;
        var sub = [];
        for(var k=0 ;k<subGroupsArray.length;k++){
            if(subGroupsArray[k].count > 0)
                sub.push(subGroupsArray[k]);
        }                    
        map1["subGroups"] = sub;
        groups.push(map1);
    }
    return groups;
}

sql：

SELECT c.id,c.name,udf.test(c.groups) as groups FROM c