how to query cosmos db array to find exact match for all the items-CodePudding

I have below data in cosmos db

[
{
    "id": "123",
    "threadId": "1234",
    "participantId": [
        "0000000e-1a91-7ff4-28f4-343a0d006def",
        "0000000e-1a9a-a08e-6a0b-343a0d006671"
    ]
},
{
    "id": "1234",
    "threadId": "1234",
    "participantId": [
        "0000000e-1a91-7ff4-28f4-343a0d006def",
        "0000000e-1a98-1dd4-85f4-343a0d009ba3"
    ]
}

]

So i am looking for a query to find exact match of Array Items for e.g in c# I will pass list of Participant i.e. 0000000e-1a91-7ff4-28f4-343a0d006def, 0000000e-1a9a-a08e-6a0b-343a0d006671 and it should look for exact match and it should not bring data from other participant array. As here it has one match

CodePudding user response：

You can make a query like this:

SELECT * 
FROM c
WHERE 
    ARRAY_CONTAINS(c.participantId, '0000000e-1a91-7ff4-28f4-343a0d006def') AND
    ARRAY_CONTAINS(c.participantId, '0000000e-1a98-1dd4-85f4-343a0d009ba3') AND
    ARRAY_LENGTH(c.participantId) = 2 //in case you want an exact match

If you prefer to use a linq statement in C# you could do the same:

var participants = new List<string>()
{
    "0000000e-1a91-7ff4-28f4-343a0d006def",
    "0000000e-1a98-1dd4-85f4-343a0d009ba3"
};

IQueryable<MyItem> qry = container
    .GetItemLinqQueryable<MyItem>(requestOptions: new() { MaxItemCount = -1 });
foreach (var participant in participants)
{
    qry = qry.Where(x => x.participantId.Contains(participant));
}
qry = qry.Where(x => x.participantId.Count() == participants.Count);

var iterator = qry.ToFeedIterator();

var results = new List<MyItem>();
while (iterator.HasMoreResults)
{
    var response = await iterator.ReadNextAsync();
    foreach (var item in response)
    {
        results.Add(item);
    }
}