替代CosmosDB中的Have子句 [英] Substitute for having clause in CosmosDB

问题描述

我从查询中得到以下结果，其中count字段是从聚合函数派生的

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

我只想显示那些count> = 2的facilityName.

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

我们如何实现这一目标?

How can we achieve this?

推荐答案

我尝试通过存储过程实现您的要求，请参考我的SP代码:

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

输入参数:

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

获取输出:

更新:

因此，如果您的集合已分区，则存储过程可能不适合您，因为分区键是执行SP所必需的.请参阅此线程中我的详细说明:

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

实际上，我上面的sp代码中没有复杂的逻辑，它只是循环sql的结果并尝试查找与 idArray.length 相等的 object.count >表示object.facilityName存在于所有文档中.

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.

因此，您不必使用SP，您可以使用任何小段代码来处理我上面描述的逻辑.

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

这篇关于替代CosmosDB中的Have子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！