is there a query in cosmos documentDB with a search until rule

Question

so i am trying to query some data from a cosmos documentdb database as follows

here is my data :

{

"id": "**********",

"triggers": [
    {
        "type": "enter_an_area",
        "trigger_identity": "***********",

        },

    },
    {
        "type": "enter_an_area",
        "trigger_identity": "********",

        },

    },
    {
        "type": "exit_an_area",
        "trigger_identity": "*******",


    },

this is one document of my collection, where i have a document for every user with a unique ID, now what i want to do is count the number of users that use a specific trigger, a user may have the same trigger multiple times, as you can see in the example "enter_an_area" has more than one entery, but i would still want to count it as one entery.

i use this query to get the count for a specific trigger :

SELECT VALUE COUNT(1) FROM u JOIN t in u.triggers WHERE CONTAINS(t.type, "enter_an_area")

but for the example above this would return: 2 where i want it to return 1

is there a query to do this in documentdb? if there is no such a query, is there a way to return results without duplicates? because as a solution i thought i can return the IDs that use this specific trigger, but then i would get duplicate IDs when a user have more than one entery for a trigger.

Jay Gong · Accepted Answer · 2018-04-11T01:14:21.243

It seems that your issue is about function like distinct the results of the query joins an array. I suggest you using stored procedure to implement your solution as a workaround.

Please refer to my sample stored procedure:

function sample() {
    var collection = getContext().getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT u.id FROM u JOIN t in u.triggers WHERE CONTAINS(t.type, "enter_an_area")',
        function (err, feed, options) {
            if (err) throw err;
            if (!feed || !feed.length) getContext().getResponse().setBody('no docs found');
            else {
                var returnResult = [];
                var temp =''
                for(var i = 0;i<feed.length;i++){
                    var valueStr = feed[i].id;
                    if(valueStr != temp){
                        temp = valueStr;
                        returnResult.push(feed[i]) 
                    } 
                }
                getContext().getResponse().setBody(returnResult.length);
            }
        });

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

Hope it helps you.

@ibrahim.bond Hi,please edit your comment in your question. thx — Jay Gong, Apr 10 '18 at 14:22
@ibrahim.bond I tried to figure out your further solution. I think your way in your edit will be ok if you only have 7 types fixed. — Jay Gong, Apr 11 '18 at 07:18
@ibrahim.bond If you think the workaround is helpful ,please mark it for others' reference.Thx a lot. — Jay Gong, Apr 11 '18 at 07:19

is there a query in cosmos documentDB with a search until rule

1 Answers1