Return only a subset of child from condition

Question

Is it possible to do a conditional query on children? I am trying to return all groups, with all all sub groups (and there children) where count larger than 0. Notice that subgroups have children that also need to be returned if count larger than 0.

Document:

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

Wanted result:

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

I tried doing via join, but haven't found an approach where you can skip some sub groups and not others. All suggestions are welcome.

Answer 1

I suggest you using stored procedure to implement your desire results. Please refer to my sample code:

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.');
}

---

Well, maybe you could use UDF in SQL query. However, you still need to loop the array in udf function.

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