Reputation: 984
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": "[email protected]",
"tel1": "0495-543905",
"tel2": null,
"vat": null
},
{
"role": "ZO",
"first_name": "Coralie",
"last_name": "Demeyere",
"_type": "contact",
"email": "[email protected]",
"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!
Upvotes: 0
Views: 982
Reputation: 23767
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.
Upvotes: 1