cosmosdb where clause in sub lists with linq

Question

My root data has several sub collections, and I need to do a where clause on 2 of them, sample:

   {
        "id": "000092224369_0030",
  ....
        "orderData": {
          ...
          "request_secondary": {
            "_type": "request_secondary",
            "secondary_requests": [{
"secondary_description":"do something" }]
          },
          "partnership": {
            "contacts": [
              {
                "role": "AG",
                "first_name": "LIEBENS Eric",
                "last_name": null,
                "_type": "contact",
                "email": "eric.liebens@gmail.com",
                "tel1": "0495-543905",
                "tel2": null,
                "vat": null
              },
              {
                "role": "ZO",
                "first_name": "Coralie",
                "last_name": "Demeyere",
                "_type": "contact",
                "email": "coralie.demeyere@ores.net",
                "tel1": "069/256533",
                "tel2": null,
                "vat": null
              },
              {
                "role": "ZR",
                "first_name": "GASBARRO Gianni",
                "last_name": null,
                "_type": "contact",
                "email": null,
                "tel1": "0495-385479-0",
                "tel2": null,
                "vat": "BE0474281005"
              }
            ],
    ...

Here I need to do a query that bring back the record where any secondary_description equals a text, or a contact with a name with that text. It should translate to sql for in something this:

SELECT c.id from c
join x  in c.orderData.request_secondary
join y in c.orderData.partnership.contacts
where x.secondary_description ='sometin' or y.first_name= 'sometin'

I have tried this solution: How to query sub document (list type) in Cosmos Db

It works great with one sub collection, but I have no idea how i could get this to work with several selectmany... Is there any way I can do this in linq? Thanks!

Answer 1

Based on your description, i think your SQL needs to be tweaked a little bit.

SELECT c.id from c
join x  in c.orderData.request_secondary.secondary_requests
join y in c.orderData.partnership.contacts
where x.secondary_description ='something' or y.first_name= 'something'

However, there's going to be duplicate data in the results.So , I also suggestion you adopt the stored procedure which I answered in the thread:How to query sub document (list type) in Cosmos Db.

function sample() {
    var collection = getContext().getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
        function (err, feed, options) {
            if (err) throw err;
            if (!feed || !feed.length) getContext().getResponse().setBody('no docs found');
            else {
                var returnResult = [];
                for(var i = 0;i<feed.length;i++){
                    var isContinue = true;
                    var array1 = feed[i].orderData.request_secondary.secondary_requests;
                    var array2 = feed[i].orderData.partnership.contacts;
                    for(var j = 0;i<array1.length;j++){
                        if(array1[j].secondary_description == 'something'){
                            returnResult.push(feed[i]);
                            isContinue=false;
                            break;
                        }
                    }
                    if(isContinue){
                        for(var k = 0;i<array2.length;k++){
                            if(array2[j].first_name == 'something'){
                                returnResult.push(feed[i]);
                                break;
                            }
                        }
                    }    
                }
                getContext().getResponse().setBody(returnResult);
            }
        });

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

---

Update Answer:

You could build LINQ from SQL follow the doc.

client.CreateDocumentQuery().SelectMany((x) => 
x.orderData.requestSecondary.secondaryRequests.Where(
    s=>s.secondaryDescription == "something" 
) ||
x.orderData.partnership.contacts.Where(
    c=>c.firstName == "something" 
)

However , i think you still need to resolve the duplicate data of the result set on your client.

Hope it helps you.