CosmosDb Sql query that matches common values in array between documents

Question

I am working with Cosmos DB and I want to write a SQL query that will match common value in array of documents based on id.

To elaborate, imagine you have the following three documents:

{
        "id": "2ECF4568-CB0E-4E11-A5CD-1206638F9C39",
        "entityType": "ServiceInformationFacility",
        "facilities": [
            {
                "id": "6F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Kat Service Center",
                "phoneNumber": "9879561234"

            },
            {
                "id": "7F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Honda Service Center",
                "phoneNumber": "9879561234"

            }]

    },
    {
        "id": "3ECF4568-CB0E-4E11-A5CD-1206638F9C39",
        "entityType": "ServiceInformationFacility",
        "facilities": [
            {
                "id": "8F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Hyundai Service Center",
                "phoneNumber": "9879561234"

            },
            {
                "id": "7F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Honda Service Center",
                "phoneNumber": "9879561234"

            }]

    },
    {
        "id": "6ECF4568-CB0E-4E11-A5CD-1206638F9C39",
        "entityType": "ServiceInformationFacility",
        "facilities": [
            {
                "id": "8F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Hyundai Service Center",
                "phoneNumber": "9879561234"

            },
            {
                "id": "7F706BA3-27AD-45B8-9831-A531E37C4C17",
                "facilityName": "Honda Service Center",
                "phoneNumber": "9879561234"
            } ]
    }

I want to write a query that return all the common facility based on id.That means when passing the list of Ids, the facility exists in the given Ids should be display(not either or). so in the above collection it should only return "facilityName": "Honda Service Center" by passing parameter id("2ECF4568-CB0E-4E11-A5CD-1206638F9C39","3ECF4568-CB0E-4E11-A5CD-1206638F9C39","6ECF4568-CB0E-4E11-A5CD-1206638F9C39").

So far I have tried:

SELECT q.facilityName  FROM c
join q in c.facilities
where c.id in('6ECF4568-CB0E-4E11-A5CD-1206638F9C39','2ECF4568-CB0E-4E11-A5CD-1206638F9C39')AND c.entityType = 'ServiceInformationFacility'

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".

Thanks in advance

Jay Gong Jay Gong · Accepted Answer · 2020-01-14T01:42:20

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:

Then i tried to extend it with some subquery 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"]

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:

CosmosDb Sql query that matches common values in array between documents

1 Answers