Using $elemMatch and $or to implement a fallback logic (in projection)Updating a Nested Array with MongoDBFind in Double Nested Array MongoDBDoes $elemMatch not work with a new mongodb 2.2?Querying a MongoDB based on Mongo ID in a node.js appMongoDB find subdocument and sort the resultsMongoDB C# Driver - $match with the same field twiceCombining $elemMatch and $ in mongodb projectionMongodb Update Query returning ambiguous countmongoDB search records by multidimensional arrayMongodb comparing object arrays keysMongoDB: return a document if another does not existAggregation lookup not working in spring
Forgetting the musical notes while performing in concert
iPad being using in wall mount battery swollen
How could indestructible materials be used in power generation?
A category-like structure without composition?
Why is this clock signal connected to a capacitor to gnd?
Why do I get two different answers for this counting problem?
Saudi Arabia Transit Visa
How would I stat a creature to be immune to everything but the Magic Missile spell? (just for fun)
Could the museum Saturn V's be refitted for one more flight?
Is it possible to create a QR code using text?
What mechanic is there to disable a threat instead of killing it?
How to remove strange space symbols in Word
What method can I use to design a dungeon difficult enough that the PCs can't make it through without killing them?
Can we compute the area of a quadrilateral with one right angle when we only know the lengths of any three sides?
Why didn't Boeing produce its own regional jet?
Can a virus destroy the BIOS of a modern computer?
Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?
Why can't we play rap on piano?
Why doesn't using multiple commands with a || or && conditional work?
How dangerous is XSS?
How can saying a song's name be a copyright violation?
Why would the Red Woman birth a shadow if she worshipped the Lord of the Light?
I would say: "You are another teacher", but she is a woman and I am a man
How can I determine if the org that I'm currently connected to is a scratch org?
Using $elemMatch and $or to implement a fallback logic (in projection)
Updating a Nested Array with MongoDBFind in Double Nested Array MongoDBDoes $elemMatch not work with a new mongodb 2.2?Querying a MongoDB based on Mongo ID in a node.js appMongoDB find subdocument and sort the resultsMongoDB C# Driver - $match with the same field twiceCombining $elemMatch and $ in mongodb projectionMongodb Update Query returning ambiguous countmongoDB search records by multidimensional arrayMongodb comparing object arrays keysMongoDB: return a document if another does not existAggregation lookup not working in spring
db.projects.findOne("_id": "5CmYdmu2Aanva3ZAy",
"responses":
"$elemMatch":
"match.nlu":
"$elemMatch":
"intent": "intent1",
"$and": [
"$or": [
"entities.entity": "entity1",
"entities.value": "value1"
,
"entities.entity": "entity1",
"entities.value":
"$exists": false
]
],
"entities.1":
"$exists": false
)
In a given project I need a projection containing only one response, hence $elemMatch. Ideally, look for an exact match:
"entities.entity": "entity1",
"entities.value": "value1"
But if such a match doesn't exist, look for a record where entities.value does not exist
The query above doesn't work because if it finds an item with entities.value not set it will return it. How can I get this fallback logic in a Mongo query
Here is an example of document
"_id": "5CmYdmu2Aanva3ZAy",
"responses": [
"match":
"nlu": [
"entities": [],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"entities": [
"entity": "entity1",
"value": "value1"
],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"intent": "intent2",
"entities": []
,
"intent": "intent1",
"entities": [
"entity": "entity1"
]
]
,
"key": "utter_intent2_Laag5aDZv2"
]
mongodb mongodb-query aggregation-framework
edited Mar 8 at 2:35
Neil Lunn
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
add a comment |
db.projects.findOne("_id": "5CmYdmu2Aanva3ZAy",
"responses":
"$elemMatch":
"match.nlu":
"$elemMatch":
"intent": "intent1",
"$and": [
"$or": [
"entities.entity": "entity1",
"entities.value": "value1"
,
"entities.entity": "entity1",
"entities.value":
"$exists": false
]
],
"entities.1":
"$exists": false
)
In a given project I need a projection containing only one response, hence $elemMatch. Ideally, look for an exact match:
"entities.entity": "entity1",
"entities.value": "value1"
But if such a match doesn't exist, look for a record where entities.value does not exist
The query above doesn't work because if it finds an item with entities.value not set it will return it. How can I get this fallback logic in a Mongo query
Here is an example of document
"_id": "5CmYdmu2Aanva3ZAy",
"responses": [
"match":
"nlu": [
"entities": [],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"entities": [
"entity": "entity1",
"value": "value1"
],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"intent": "intent2",
"entities": []
,
"intent": "intent1",
"entities": [
"entity": "entity1"
]
]
,
"key": "utter_intent2_Laag5aDZv2"
]
mongodb mongodb-query aggregation-framework
edited Mar 8 at 2:35
Neil Lunn
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09
add a comment |
db.projects.findOne("_id": "5CmYdmu2Aanva3ZAy",
"responses":
"$elemMatch":
"match.nlu":
"$elemMatch":
"intent": "intent1",
"$and": [
"$or": [
"entities.entity": "entity1",
"entities.value": "value1"
,
"entities.entity": "entity1",
"entities.value":
"$exists": false
]
],
"entities.1":
"$exists": false
)
In a given project I need a projection containing only one response, hence $elemMatch. Ideally, look for an exact match:
"entities.entity": "entity1",
"entities.value": "value1"
But if such a match doesn't exist, look for a record where entities.value does not exist
The query above doesn't work because if it finds an item with entities.value not set it will return it. How can I get this fallback logic in a Mongo query
Here is an example of document
"_id": "5CmYdmu2Aanva3ZAy",
"responses": [
"match":
"nlu": [
"entities": [],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"entities": [
"entity": "entity1",
"value": "value1"
],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"intent": "intent2",
"entities": []
,
"intent": "intent1",
"entities": [
"entity": "entity1"
]
]
,
"key": "utter_intent2_Laag5aDZv2"
]
mongodb mongodb-query aggregation-framework
edited Mar 8 at 2:35
Neil Lunn
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
db.projects.findOne("_id": "5CmYdmu2Aanva3ZAy",
"responses":
"$elemMatch":
"match.nlu":
"$elemMatch":
"intent": "intent1",
"$and": [
"$or": [
"entities.entity": "entity1",
"entities.value": "value1"
,
"entities.entity": "entity1",
"entities.value":
"$exists": false
]
],
"entities.1":
"$exists": false
)
In a given project I need a projection containing only one response, hence $elemMatch. Ideally, look for an exact match:
"entities.entity": "entity1",
"entities.value": "value1"
But if such a match doesn't exist, look for a record where entities.value does not exist
The query above doesn't work because if it finds an item with entities.value not set it will return it. How can I get this fallback logic in a Mongo query
Here is an example of document
"_id": "5CmYdmu2Aanva3ZAy",
"responses": [
"match":
"nlu": [
"entities": [],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"entities": [
"entity": "entity1",
"value": "value1"
],
"intent": "intent1"
]
,
"key": "utter_intent1_p3vE6O_XsT"
,
"match":
"nlu": [
"intent": "intent2",
"entities": []
,
"intent": "intent1",
"entities": [
"entity": "entity1"
]
]
,
"key": "utter_intent2_Laag5aDZv2"
]
mongodb mongodb-query aggregation-framework
mongodb mongodb-query aggregation-framework
edited Mar 8 at 2:35
Neil Lunn
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
edited Mar 8 at 2:35
Neil Lunn
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
edited Mar 8 at 2:35
Neil Lunn
101k23179187
edited Mar 8 at 2:35
Neil Lunn
101k23179187
edited Mar 8 at 2:35
Neil Lunn
101k23179187
101k23179187
asked Mar 7 at 22:38
znatznat
7,223115080
asked Mar 7 at 22:38
znatznat
7,223115080
asked Mar 7 at 22:38
znatznat
7,223115080
7,223115080
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09
add a comment |
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09
add a comment |
1 Answer
1
active
oldest
votes
To answer the question, the first thing to start with is that doing what you want is not as simple as an $elemMatch projection and requires special projection logic of the aggregation framework. The second main principle here is "nesting arrays is a really bad idea", and this is exactly why:
db.collection.aggregate([
"$match": "_id": "5CmYdmu2Aanva3ZAy" ,
"$addFields":
"responses":
"$filter":
"input":
"$map":
"input": "$responses",
"in":
"match":
"nlu":
"$filter":
"input":
"$map":
"input": "$$this.match.nlu",
"in":
"entities":
"$let":
"vars":
"entities":
"$filter":
"input": "$$this.entities",
"cond":
"$and": [
"$eq": [ "$$this.entity", "entity1" ] ,
"$or": [
"$eq": [ "$$this.value", "value1" ] ,
"$ifNull": [ "$$this.value", false ]
]
]
,
"in":
"$cond":
"if": "$gt": [ "$size": "$$entities" , 1] ,
"then":
"$slice": [
"$filter":
"input": "$$entities",
"cond": "$eq": [ "$$this.value", "value1" ]
,
0
]
,
"else": "$$entities"
,
"intent": "$$this.intent"
,
"cond": "$ne": [ "$$this.entities", [] ]
,
"key": "$$this.key"
,
"cond": "$ne": [ "$$this.match.nlu", [] ]
])
Will return:
"_id" : "5CmYdmu2Aanva3ZAy",
"responses" : [
"match" :
"nlu" : [
"entities" : [
"entity" : "entity1",
"value" : "value1"
],
"intent" : "intent1"
]
,
"key" : "utter_intent1_p3vE6O_XsT"
]
That is extracting ( as best I can determine your specification ), the first matching element from the nested inner array of entities where the conditions for both entity and value are met OR where the value property does not exist.
Note the additional fallback in that if both conditions meant returning multiple array elements, then only the first match where the value was present and matching would be the result returned.
Querying deeply nested arrays requires chained usage of $map and $filter in order to traverse those array contents and return only items which match the conditions. You cannot specify these conditions in an $elemMatch projection, nor has it even been possible until recent releases of MongoDB to even atomically update such structures without overwriting significant parts of the document or introducing problems with update concurrency.
More detailed explanation of this is on my existing answer to Updating a Nested Array with MongoDB and from the query side on Find in Double Nested Array MongoDB.
Note that both responses there show usage of $elemMatch as a "query" operator, which is really only about "document selection" ( therefore does not apply to an _id match condition ) and cannot be used in concert with the former "projection" variant nor the positional $ projection operator.
You would be advised then to "not nest arrays" and instead take the option of "flatter" data structures as those answers already discuss at length.
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with$unwindand that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.
– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55053942%2fusing-elemmatch-and-or-to-implement-a-fallback-logic-in-projection%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To answer the question, the first thing to start with is that doing what you want is not as simple as an $elemMatch projection and requires special projection logic of the aggregation framework. The second main principle here is "nesting arrays is a really bad idea", and this is exactly why:
db.collection.aggregate([
"$match": "_id": "5CmYdmu2Aanva3ZAy" ,
"$addFields":
"responses":
"$filter":
"input":
"$map":
"input": "$responses",
"in":
"match":
"nlu":
"$filter":
"input":
"$map":
"input": "$$this.match.nlu",
"in":
"entities":
"$let":
"vars":
"entities":
"$filter":
"input": "$$this.entities",
"cond":
"$and": [
"$eq": [ "$$this.entity", "entity1" ] ,
"$or": [
"$eq": [ "$$this.value", "value1" ] ,
"$ifNull": [ "$$this.value", false ]
]
]
,
"in":
"$cond":
"if": "$gt": [ "$size": "$$entities" , 1] ,
"then":
"$slice": [
"$filter":
"input": "$$entities",
"cond": "$eq": [ "$$this.value", "value1" ]
,
0
]
,
"else": "$$entities"
,
"intent": "$$this.intent"
,
"cond": "$ne": [ "$$this.entities", [] ]
,
"key": "$$this.key"
,
"cond": "$ne": [ "$$this.match.nlu", [] ]
])
Will return:
"_id" : "5CmYdmu2Aanva3ZAy",
"responses" : [
"match" :
"nlu" : [
"entities" : [
"entity" : "entity1",
"value" : "value1"
],
"intent" : "intent1"
]
,
"key" : "utter_intent1_p3vE6O_XsT"
]
That is extracting ( as best I can determine your specification ), the first matching element from the nested inner array of entities where the conditions for both entity and value are met OR where the value property does not exist.
Note the additional fallback in that if both conditions meant returning multiple array elements, then only the first match where the value was present and matching would be the result returned.
Querying deeply nested arrays requires chained usage of $map and $filter in order to traverse those array contents and return only items which match the conditions. You cannot specify these conditions in an $elemMatch projection, nor has it even been possible until recent releases of MongoDB to even atomically update such structures without overwriting significant parts of the document or introducing problems with update concurrency.
More detailed explanation of this is on my existing answer to Updating a Nested Array with MongoDB and from the query side on Find in Double Nested Array MongoDB.
Note that both responses there show usage of $elemMatch as a "query" operator, which is really only about "document selection" ( therefore does not apply to an _id match condition ) and cannot be used in concert with the former "projection" variant nor the positional $ projection operator.
You would be advised then to "not nest arrays" and instead take the option of "flatter" data structures as those answers already discuss at length.
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with$unwindand that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.
– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
add a comment |
To answer the question, the first thing to start with is that doing what you want is not as simple as an $elemMatch projection and requires special projection logic of the aggregation framework. The second main principle here is "nesting arrays is a really bad idea", and this is exactly why:
db.collection.aggregate([
"$match": "_id": "5CmYdmu2Aanva3ZAy" ,
"$addFields":
"responses":
"$filter":
"input":
"$map":
"input": "$responses",
"in":
"match":
"nlu":
"$filter":
"input":
"$map":
"input": "$$this.match.nlu",
"in":
"entities":
"$let":
"vars":
"entities":
"$filter":
"input": "$$this.entities",
"cond":
"$and": [
"$eq": [ "$$this.entity", "entity1" ] ,
"$or": [
"$eq": [ "$$this.value", "value1" ] ,
"$ifNull": [ "$$this.value", false ]
]
]
,
"in":
"$cond":
"if": "$gt": [ "$size": "$$entities" , 1] ,
"then":
"$slice": [
"$filter":
"input": "$$entities",
"cond": "$eq": [ "$$this.value", "value1" ]
,
0
]
,
"else": "$$entities"
,
"intent": "$$this.intent"
,
"cond": "$ne": [ "$$this.entities", [] ]
,
"key": "$$this.key"
,
"cond": "$ne": [ "$$this.match.nlu", [] ]
])
Will return:
"_id" : "5CmYdmu2Aanva3ZAy",
"responses" : [
"match" :
"nlu" : [
"entities" : [
"entity" : "entity1",
"value" : "value1"
],
"intent" : "intent1"
]
,
"key" : "utter_intent1_p3vE6O_XsT"
]
That is extracting ( as best I can determine your specification ), the first matching element from the nested inner array of entities where the conditions for both entity and value are met OR where the value property does not exist.
Note the additional fallback in that if both conditions meant returning multiple array elements, then only the first match where the value was present and matching would be the result returned.
Querying deeply nested arrays requires chained usage of $map and $filter in order to traverse those array contents and return only items which match the conditions. You cannot specify these conditions in an $elemMatch projection, nor has it even been possible until recent releases of MongoDB to even atomically update such structures without overwriting significant parts of the document or introducing problems with update concurrency.
More detailed explanation of this is on my existing answer to Updating a Nested Array with MongoDB and from the query side on Find in Double Nested Array MongoDB.
Note that both responses there show usage of $elemMatch as a "query" operator, which is really only about "document selection" ( therefore does not apply to an _id match condition ) and cannot be used in concert with the former "projection" variant nor the positional $ projection operator.
You would be advised then to "not nest arrays" and instead take the option of "flatter" data structures as those answers already discuss at length.
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with$unwindand that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.
– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
add a comment |
To answer the question, the first thing to start with is that doing what you want is not as simple as an $elemMatch projection and requires special projection logic of the aggregation framework. The second main principle here is "nesting arrays is a really bad idea", and this is exactly why:
db.collection.aggregate([
"$match": "_id": "5CmYdmu2Aanva3ZAy" ,
"$addFields":
"responses":
"$filter":
"input":
"$map":
"input": "$responses",
"in":
"match":
"nlu":
"$filter":
"input":
"$map":
"input": "$$this.match.nlu",
"in":
"entities":
"$let":
"vars":
"entities":
"$filter":
"input": "$$this.entities",
"cond":
"$and": [
"$eq": [ "$$this.entity", "entity1" ] ,
"$or": [
"$eq": [ "$$this.value", "value1" ] ,
"$ifNull": [ "$$this.value", false ]
]
]
,
"in":
"$cond":
"if": "$gt": [ "$size": "$$entities" , 1] ,
"then":
"$slice": [
"$filter":
"input": "$$entities",
"cond": "$eq": [ "$$this.value", "value1" ]
,
0
]
,
"else": "$$entities"
,
"intent": "$$this.intent"
,
"cond": "$ne": [ "$$this.entities", [] ]
,
"key": "$$this.key"
,
"cond": "$ne": [ "$$this.match.nlu", [] ]
])
Will return:
"_id" : "5CmYdmu2Aanva3ZAy",
"responses" : [
"match" :
"nlu" : [
"entities" : [
"entity" : "entity1",
"value" : "value1"
],
"intent" : "intent1"
]
,
"key" : "utter_intent1_p3vE6O_XsT"
]
That is extracting ( as best I can determine your specification ), the first matching element from the nested inner array of entities where the conditions for both entity and value are met OR where the value property does not exist.
Note the additional fallback in that if both conditions meant returning multiple array elements, then only the first match where the value was present and matching would be the result returned.
Querying deeply nested arrays requires chained usage of $map and $filter in order to traverse those array contents and return only items which match the conditions. You cannot specify these conditions in an $elemMatch projection, nor has it even been possible until recent releases of MongoDB to even atomically update such structures without overwriting significant parts of the document or introducing problems with update concurrency.
More detailed explanation of this is on my existing answer to Updating a Nested Array with MongoDB and from the query side on Find in Double Nested Array MongoDB.
Note that both responses there show usage of $elemMatch as a "query" operator, which is really only about "document selection" ( therefore does not apply to an _id match condition ) and cannot be used in concert with the former "projection" variant nor the positional $ projection operator.
You would be advised then to "not nest arrays" and instead take the option of "flatter" data structures as those answers already discuss at length.
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
To answer the question, the first thing to start with is that doing what you want is not as simple as an $elemMatch projection and requires special projection logic of the aggregation framework. The second main principle here is "nesting arrays is a really bad idea", and this is exactly why:
db.collection.aggregate([
"$match": "_id": "5CmYdmu2Aanva3ZAy" ,
"$addFields":
"responses":
"$filter":
"input":
"$map":
"input": "$responses",
"in":
"match":
"nlu":
"$filter":
"input":
"$map":
"input": "$$this.match.nlu",
"in":
"entities":
"$let":
"vars":
"entities":
"$filter":
"input": "$$this.entities",
"cond":
"$and": [
"$eq": [ "$$this.entity", "entity1" ] ,
"$or": [
"$eq": [ "$$this.value", "value1" ] ,
"$ifNull": [ "$$this.value", false ]
]
]
,
"in":
"$cond":
"if": "$gt": [ "$size": "$$entities" , 1] ,
"then":
"$slice": [
"$filter":
"input": "$$entities",
"cond": "$eq": [ "$$this.value", "value1" ]
,
0
]
,
"else": "$$entities"
,
"intent": "$$this.intent"
,
"cond": "$ne": [ "$$this.entities", [] ]
,
"key": "$$this.key"
,
"cond": "$ne": [ "$$this.match.nlu", [] ]
])
Will return:
"_id" : "5CmYdmu2Aanva3ZAy",
"responses" : [
"match" :
"nlu" : [
"entities" : [
"entity" : "entity1",
"value" : "value1"
],
"intent" : "intent1"
]
,
"key" : "utter_intent1_p3vE6O_XsT"
]
That is extracting ( as best I can determine your specification ), the first matching element from the nested inner array of entities where the conditions for both entity and value are met OR where the value property does not exist.
Note the additional fallback in that if both conditions meant returning multiple array elements, then only the first match where the value was present and matching would be the result returned.
Querying deeply nested arrays requires chained usage of $map and $filter in order to traverse those array contents and return only items which match the conditions. You cannot specify these conditions in an $elemMatch projection, nor has it even been possible until recent releases of MongoDB to even atomically update such structures without overwriting significant parts of the document or introducing problems with update concurrency.
More detailed explanation of this is on my existing answer to Updating a Nested Array with MongoDB and from the query side on Find in Double Nested Array MongoDB.
Note that both responses there show usage of $elemMatch as a "query" operator, which is really only about "document selection" ( therefore does not apply to an _id match condition ) and cannot be used in concert with the former "projection" variant nor the positional $ projection operator.
You would be advised then to "not nest arrays" and instead take the option of "flatter" data structures as those answers already discuss at length.
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
edited Mar 8 at 2:37
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
answered Mar 8 at 2:24
Neil LunnNeil Lunn
101k23179187
101k23179187
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with$unwindand that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.
– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
add a comment |
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with$unwindand that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.
– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
I figured that I would have to use the aggregation framework. I realized that I could simplify it a bit by assuming that responses with more entity values sets are preferred. Using a reduce and a group steps, sorting by count and taking the first value works withou - unwind responses and responses.match.nlu - match entities and entities values (this returns all matching entities, with or without value) - project->reduce: a new field containing an array with all entities values - sort and group (with first) to select the best response. It ends up being readable .
– znat
Mar 13 at 0:23
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
However I am wondering about the performance impact of unwinding at the beginning. Thank you so much for your detailed answer and recommendations
– znat
Mar 13 at 0:24
@znat The performance is generally terrible with
$unwind and that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.– Neil Lunn
Mar 13 at 6:02
@znat The performance is generally terrible with
$unwind and that basically scales to get worse over sharded clusters. The basic concept here is nothing that you actually asked for needs to "group" on something "inside" the array content, nor across "multiple documents" and is indeed a "per document" operation. In that case, even though this "looks" more complicated than you think it need be, it really is not. Anyhow the real lesson here "should" be do not nest arrays. I know you "think" this is how you do relational structure, but it really isn't. I made the same mistakes.– Neil Lunn
Mar 13 at 6:02
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
I realize the limitations of nested arrays. But I have to live with them for a while until we refactor our data model
– znat
Mar 13 at 12:35
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55053942%2fusing-elemmatch-and-or-to-implement-a-fallback-logic-in-projection%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Show what an actual document looks like and what you expect to receive in response. If you have different cases, then show different documents and the different expected responses.
– Neil Lunn
Mar 7 at 23:34
@NeilLunn absolutely. Question updated
– znat
Mar 8 at 0:39
Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to Accept your Answers to the questions you ask
– Neil Lunn
Mar 11 at 22:10
Hi @NeilLunn, thank you so much for your answer. I took a couple of days offs, hence the delay
– znat
Mar 13 at 0:09