Mongodb-Query db using result from another query

Question

I have multiples queries on the same collection:

1st Query:

db.getCollection('messagelogs').find({'intents.intent':'General_Positive_Feedback'},{'currentStep':1,'_id':0})

1st Query result

{
"currentStep" : [ 
    "flowkuec8ta1o"
]
}

I'm using the first query result 'flowkuec8ta1o' for 2nd query('currentStep' in query 1 result is used as the value of key 'previousStep' in the below 2nd query) :

2nd Query:

db.getCollection('messagelogs').find({'previousStep':'flow1pemwl7ws'},{'userMessage':1})

2nd Query Result:

{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"userMessage" : "ILikedIt#1"
 }

How can I combine both the queries into one?
Please dont mark this question of this question. I tried that questions solution, which was not giving the desired result for me. Can anyone please help me with this?

Attaching sample document 1(Where 'currentStep' is fetched from):

{
"_id" : ObjectId("5cec2dc69b806c4a00f91f16"),
"currentStep" : [ 
    "flowkuec8ta1o"
],
"previousStep" : [ 
    "conditioncplwf7pw1", 
    "condition00yokr6jv", 
    "conditionzq9koi6i3"
],
"userMessage" : "Liked It :)",
"intents" : [ 
    {
        "_id" : ObjectId("5cec2dc69b806c4a00f91f17"),
        "intent" : "General_Positive_Feedback",
        "score" : "0.9774518966674806"
    }
]
}

Attaching sample document 2 (Where 'currentStep' value fetched from query 1 is used as 'previousStep' in the second query)

{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"currentStep" : [],
"previousStep" : [ 
    "flowkuec8ta1o"
],
"userMessage" : "ILikedIt#1",
    "intents" : [ 
    {
        "_id" : ObjectId("5cec2dd29b806c4a00f91f19"),
        "intent" : "Feedback",
        "score" : "1"
    }
]
}

Sample output:

{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"userMessage" : "ILikedIt#1"
}

Adding sample documents as per @Fanparks request:
The below two documents matches the first query(to get the 'currentStep')
Document 1:

{
"currentStep": [
    "flowkuec8ta1o"
],
"previousStep": [
    "conditioncplwf7pw1",
    "condition00yokr6jv",
    "conditionzq9koi6i3"
],
"userMessage": "Liked It :)",
"intents": [{
    "_id": ObjectId("5cec2dc69b806c4a00f91f17"),
    "intent": "General_Positive_Feedback",
    "score": "0.9774518966674806"
}]
}

Document 2:

{
"currentStep": [
    "flowkuec8ta1o"
],
"previousStep": [
    "conditioncplwf7pw1",
    "condition00yokr6jv",
    "conditionzq9koi6i3"
],
"userMessage": "Just Okay, Could Be better",
"intents": [{
    "_id": ObjectId("5cec2f1a9b806c4a00f91f41"),
    "intent": "General_Positive_Feedback",
    "score": "1"
}]
}

The below documents matches the second query(Where the 'previousStep' matches the 'currentStep' fetched from the first query)
Document 1:

{
"currentStep": [],
"previousStep": [
    "flowkuec8ta1o"
],
"userMessage": "ILikedIt#1",
"intents": [{
    "_id": ObjectId("5cec2dd29b806c4a00f91f19"),
    "intent": "Feedback",
    "score": "1"
}]
}

Document 2:

{
"currentStep": [],
"previousStep": [
    "flowkuec8ta1o"
],
"userMessage": "JustOkayCouldBeBetter#1",
"intents": [{
    "_id": ObjectId("5cec2f2b9b806c4a00f91f43"),
    "intent": "Feedback",
    "score": "1"
}]
}

Document 3:

{
"currentStep": [],
"previousStep": [
    "flowkuec8ta1o"
],
"userMessage": "I'mGivingAPositiveFeedback",
"intents": [{
    "_id": ObjectId("5ced84e2fdf046078c85d9cb"),
    "intent": "Feedback",
    "score": "1"
}]
}

Document 4:

{
"currentStep": [],
"previousStep": [
    "flowkuec8ta1o"
],
"userMessage": "I'm giving a positive feedback for the second time!",
"intents": [{
    "_id": ObjectId("5cede37dfdf046078c85d9e0"),
    "intent": "Feedback",
    "score": "1"
}]
}

Desired Output:

/* 1 */
{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"userMessage" : "ILikedIt#1"
}

/* 2 */
{
"_id" : ObjectId("5cec2f2b9b806c4a00f91f42"),
"userMessage" : "JustOkayCouldBeBetter#1"
}

/* 3 */
{
"_id" : ObjectId("5ced84e2fdf046078c85d9ca"),
"userMessage" : "I'mGivingAPositiveFeedback"
}

/* 4 */
{
"_id" : ObjectId("5cede37dfdf046078c85d9df"),
"userMessage" : "I'm giving a positive feedback for the second time!"
}

**Actual Output: **

/* 1 */
{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"userMessage" : "ILikedIt#1"
}

/* 2 */
{
"_id" : ObjectId("5cec2f2b9b806c4a00f91f42"),
"userMessage" : "JustOkayCouldBeBetter#1"
}

/* 3 */
{
"_id" : ObjectId("5ced84e2fdf046078c85d9ca"),
"userMessage" : "I'mGivingAPositiveFeedback"
}

/* 4 */
{
"_id" : ObjectId("5cede37dfdf046078c85d9df"),
"userMessage" : "I'm giving a positive feedback for the second time!"
}

/* 5 */
{
"_id" : ObjectId("5cec2dd29b806c4a00f91f18"),
"userMessage" : "ILikedIt#1"
}

/* 6 */
{
"_id" : ObjectId("5cec2f2b9b806c4a00f91f42"),
"userMessage" : "JustOkayCouldBeBetter#1"
}

/* 7 */
{
"_id" : ObjectId("5ced84e2fdf046078c85d9ca"),
"userMessage" : "I'mGivingAPositiveFeedback"
}

/* 8 */
{
"_id" : ObjectId("5cede37dfdf046078c85d9df"),
"userMessage" : "I'm giving a positive feedback for the second time!"
}

Please explain why do you want to combine both the queries? What is relation between your above two queries? I cannot see I'm using the first query result 'flowkuec8ta1o' for 2nd query. Where did you use it in second one? Post some sample collections and the ouput you want. — Ashh
@Fanpark I have updated the question as per your request. Kindly have a look — Tony Mathew

Ashh Ashh · Accepted Answer · 2019-05-28T07:57:40

You can use below aggregation

db.getCollection('messagelogs').aggregate([
  { "$match": { "intents.intent": "General_Positive_Feedback" }},
  { "$lookup": {
      "from": "messagelogs",
      "let": { "currentStep": "$currentStep" },
      "pipeline": [
        { "$match": { "$expr": { "$eq": ["$previousStep", "$$currentStep"] }}},
        { "$project": { "userMessage": 1 }}
      ],
      "as": "previousStep"
    }},
  { "$unwind": "$previousStep" },
  { "$replaceRoot": { "newRoot": "$previousStep" }},
  { "$group": {
    "_id": "$userMessage",
    "id": { "$first": "$_id" }
  }},
  { "$project": {
    "_id": "$id",
    "userMessage": "$_id"
  }}
])

MongoPlayground

Mongodb-Query db using result from another query

1 Answers