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][1]][1]
Get output:
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/bFlcA.png
[2]: https://i.stack.imgur.com/ZSHpC.png
> It gives me all the facility name but I need only facility which are
> common in the above documents that is "facilityName": "Honda Service
> Center".
I may get your point now.However,i'm afraid that's impossible in cosmos sql. I try to count number of appearance of `facilitiesName` cross the documents and get below solution which is closest with your need.
sql:
SELECT count(c.id) as cnt, f.facilityName from c
join f in c.facilities
where array_contains(['6ECF4568-CB0E-4E11-A5CD-1206638F9C39','2ECF4568-CB0E-4E11-A5CD-1206638F9C39'],c.id,true)
AND c.entityType = 'ServiceInformationFacility'
group by f.facilityName
output:
[![enter image description here][1]][1]
Then i tried to extend it with some [subquery][2] but no luck. So i'd suggest using stored procedure to finish the next job.The main purpose is looping above result and judge if the `cnt` equals the `[ids array].length`.
-----------------------------------------------------------------------------------
Update Answer for Stored procedure code:
input param for @idArray:`["6ECF4568-CB0E-4E11-A5CD-1206638F9C39","2ECF4568-CB0E-4E11-A5CD-1206638F9C39"]`
[![enter image description here][3]][3]
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.');
}
Output:
[![enter image description here][4]][4]
[1]: https://i.stack.imgur.com/fN6BH.png
[2]: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery
[3]: https://i.stack.imgur.com/OOCWc.png
[4]: https://i.stack.imgur.com/PCPUR.png
