Mongo db query | Join | Create query on two collection

Question

Select leadId count on two collection in Mongo DB

Collection 1 : leads

{
 leadId:"abc123",
 status:"OPENED",
 stage:"start",
 crossSell:
    {
      cc:
         {
            consent:true,
            shown:[{first:true}]
         }
    }
 }

Collection 2 : pdata

{
 activeLeadId:"abc123",
 status:"OPENED",
 details:
    [
        {
            rating:10
        },
        {
            rating:9
        }
    ]
}

Question : Find leadId count from leads collection join with pdata collection based on below conditions

leads.leadId = pdata.activeleadId and
leads.status = "OPENED" and
leads.crossSell.cc.consent = true and
leads.crossSell.cc.shown[0].first = true and
pdata.details.rating >= 5

what is the expected output in json? you want to unwind the arrays first and then do the lookup? or you want lookup with the arrays as they are? for example rating>=5 what it means? array to have at least one >=5, all >=5 or unwind and keep those that >=5? — Takis
– Takis, Commented Dec 10, 2022 at 13:11
Which status should be "OPENED"? Which or how many ratings in the details array need to have a value of 5 or higher? — user20042973
– user20042973, Commented Dec 10, 2022 at 13:13
@Takis expected output is count, I mean howmany records are available with matchin conditions. the array to include in condition which have at least one rating >=5 — surendrapanday
– surendrapanday, Commented Dec 11, 2022 at 7:08
@user20042973 I have updated question, at-least one rating in details should have value >= 5 — surendrapanday
– surendrapanday, Commented Dec 11, 2022 at 7:09

turivishal · Accepted Answer · 2022-12-16 05:55:23Z

1

+50

You can try a aggregation query,

$match your conditions for leads collection
$lookup with pdata collection, pass leadId to match with pdata
match required conditions for pdata
$limit to return single document, because we don't need that data in response
$match condition to check is pdata is not empty
$count to get total number of records

db.leads.aggregate([
  {
    $match: {
      status: "OPENED",
      "crossSell.cc.consent": true,
      "crossSell.cc.shown.first": true
    }
  },
  {
    "$lookup": {
      "from": "pdata",
      "let": { "leadId": "$leadId" },
      "pipeline": [
        {
          $match: {
            $expr: { $eq: ["$$leadId", "$activeLeadId"] },
            "details.rating": { $gte: 5 }
          }
        },
        { $limit: 1 }
      ],
      "as": "pdata"
    }
  },
  { $match: { pdata: { $ne: [] } } },
  { $count: "count" }
])

Playground

edited Dec 16, 2022 at 5:55

answered Dec 15, 2022 at 7:11

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

surendrapanday Over a year ago

'shown' in leads collection and 'details' field in pdata are array. can we use 'details.rating' on array.

surendrapanday Over a year ago

This is not giving output. Query is always giving output 1 even for "details.rating": { $gte: 20}

turivishal Over a year ago

can you provide example documents, that give an output of more than 1 count? you can add documents in the above playground and share the link.

surendrapanday Over a year ago

I have added more documents. mongoplayground.net/p/GD6jXrV1MVK

turivishal Over a year ago

What is the expected count for these documents?

|

Collectives™ on Stack Overflow

Mongo db query | Join | Create query on two collection

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related