1

I have the following result from a query, where count field is derived from an aggregate function

[
    {
        "count": 1,
        "facilityName": "Hyundai Service Center"
    },
    {
        "count": 2,
        "facilityName": "Honda Service Center"
    },
    {
        "count": 1,
        "facilityName": "Kat Service Center"
    }
]

I want to display only those facilityName where count >= 2.

How can we achieve this?

Kajal Patel
  • 99
  • 6

1 Answers1

2

I tried to implement your requirement with Stored procedure,please refer to my SP code:

function sample(idArray) {
    var collection = getContext().getCollection();

    var length = idArray.length;

    var sqlQuery = {
        "query":  'SELECT count(c.id) as cnt, f.facilityName from c join f in c.facilities '+
        'where array_contains( @idArray,c.id,true) ' +
        'AND c.entityType = "ServiceInformationFacility" group by f.facilityName',
        "parameters": [
            {"name": "@idArray", "value": idArray}
        ]
    }

    // Query documents and take 1st item.
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        sqlQuery,
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var returenArray = [];
            for(var i=0;i<feed.length;i++){                
                if(feed[i].cnt==length)
                    returenArray.push(feed[i])
            }

            response.setBody(returenArray);
        }
    });

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

Input param:

["6ECF4568-CB0E-4E11-A5CD-1206638F9C39","2ECF4568-CB0E-4E11-A5CD-1206638F9C39"]

enter image description here

Get output:

enter image description here

UPDATES:

So,if your collection is partitioned,maybe stored procedure is not suitable for you because partition key is necessary for execution of SP.Please refer to my detailed explanations in this thread:Delete Documents from Cosmos using Query without Partition Key Specification

Actually, there is no complex logic in my above sp code.It just loop the result of the sql and try to find which object.count equals the idArray.length which means the object.facilityName exists cross all the documents.

So,you don't have to use SP, you can use any tiny piece of code to handle the logic I describe above.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • I'm getting this error, "Partition key provided either doesn't correspond to definition in the collection or doesn't match partition key field values specified in the document" when trying to execute the stored procedure. – Kajal Patel Jan 20 '20 at 09:29
  • 1
    @KajalPatel It seems that SP is not suitable any more because your collection is partitioned.Please refer to my updates and use any language you are good at to implement the simple loop logic. – Jay Gong Jan 20 '20 at 09:40