0

This is my organisation Collection, which has certain opportunitystatus as an array.

{
    "_id" : ObjectId("5c756b4ea5a8cd1b0d3fbfe7"),
    "opportunitystatus" : [ 
        {
            "name" : "Call Scheduled"
        }, 
        {
            "name" : "In-Process"
        }, 
        {
            "name" : "Review Proposal"
        }, 

     ]
    "companyname" : "XYZZZ",
    "creation_dt" : ISODate("2019-02-26T16:37:34.115Z"),
}

I do have another collection of leads which has an array of opportunities,
Lead 1:

{
    "_id" : ObjectId("5cab60e23512066ccac03bdf"),
    "organization_id" : ObjectId("5c756b4ea5a8cd1b0d3fbfe7"),
    "companyname" : "Another Health Plans Inc.",
    "opportunities" : [ 
        {
            "_id" : ObjectId("5cac4efe3f8e9d28221a7d66"),
            "confidence" : 10,
            "status" : "Call Scheduled",
            "valuevalidity" : "One time",
            "estimatedclose" : ISODate("2019-12-31T00:00:00.000Z"),
            "date" : ISODate("2019-04-09T07:51:26.977Z")
        }, 
        {
            "_id" : ObjectId("5cac4f2b3f8e9d28221a7d68"),
            "confidence" : 10,
            "status" : "Reviewing Proposal",
            "estimatedclose" : Date(-23890291200000),
            "date" : ISODate("2019-04-09T07:52:11.149Z")
        }
    ],
    "creation_dt" : ISODate("2019-04-08T14:55:30.921Z"),
    "updated_dt" : ISODate("2019-04-08T14:55:30.921Z"),
}


Lead 2:

{
    "_id" : ObjectId("5cab60e23512066ccac03bdf"),
    "organization_id" : ObjectId("5c756b4ea5a8cd1b0d3fbfe7"),
    "companyname" : "Health Plans Inc.",
    "opportunities" : [ 
        {
            "_id" : ObjectId("5cac4efe3f8e9d28221a7d33"),
            "confidence" : 10,
            "status" : "Reviewing Proposal",
            "valuevalidity" : "One time",
            "estimatedclose" : ISODate("2019-12-31T00:00:00.000Z"),
            "date" : ISODate("2019-04-09T07:51:26.977Z")
        }        ],
    "creation_dt" : ISODate("2019-04-08T14:55:30.921Z"),
    "updated_dt" : ISODate("2019-04-08T14:55:30.921Z"),
}

Now, i want to write a query on given organization so that i can get all the opportunity status with their respective opportunities

    "opportunitystatus" : [ 
        {
            "name" : "Call Scheduled",
            opportunities:[
               {
                "_id" : ObjectId("5cac4efe3f8e9d28221a7d66"),
                "confidence" : 10,
                "status" : "Call Scheduled",
                "valuevalidity" : "One time",
                "estimatedclose" : ISODate("2019-12-31T00:00:00.000Z"),
                "date" : ISODate("2019-04-09T07:51:26.977Z")
             }, 
           ]
        }, 
        {
            "name" : "In-Process"
        }, 
        {
          "name" : "Review Proposal",
          opportunities:[
           {
               "_id" : ObjectId("5cac4f2b3f8e9d28221a7d68"),
                "confidence" : 10,
                "status" : "Reviewing Proposal",
                "estimatedclose" : Date(-23890291200000),
                "date" : ISODate("2019-04-09T07:52:11.149Z")
            },
            {
                "_id" : ObjectId("5cac4efe3f8e9d28221a7d33"),
                "confidence" : 10,
                "status" : "Reviewing Proposal",
                "valuevalidity" : "One time",
                "estimatedclose" : ISODate("2019-12-31T00:00:00.000Z"),
                "date" : ISODate("2019-04-09T07:51:26.977Z")
            } 

         ]
        },
Ranjan Adhikari
  • 251
  • 1
  • 11
  • 1
    What have you tried? You are basically asking for a "join" but all you really have in your main document you want to "join" to is a "string" with a phrase in it. Whilst possible, this is probably not the best thing to be relying on. Note all your documents in the other collection ( in fact ALL collections ) have an `_id` field which is a **unique** identifier. If you want to "relate" to one of those documents in another, then **that field** is what you should be storing within the array in your `organisation` document. Not a "phrase". – Neil Lunn Apr 09 '19 at 10:48
  • so you are saying my opportunitystatus in the organisation document should have an _id and i should do a join? – Ranjan Adhikari Apr 09 '19 at 10:58
  • Point is that if you want an "exact match" for a document, then it's best to go with something **guaranteed** to be "unique". That's exactly what the `_id` field as a "primary key" is. A "join" basically works either with A. An array of references to the "foreign keys" as "primary keys" from other related documents. B. By the "foreign documents" containing a reference to the "primary key" of a parent document. Either way really depends on your own usage needs. For instance you could keep your "description" in the array along with a foreign `_id` reference. – Neil Lunn Apr 09 '19 at 11:03
  • Not to be confused with that it "could possibly work" for you with just the "strings" as they are, as long as those strings are "unique". But you would probably be better off referring to the `_id` values instead within one collection or both. But basically "joins" are essentially covered in many existing answers as well as the [`$lookup`](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) documentation. Which is basically the modern "on the server" way to "join". – Neil Lunn Apr 09 '19 at 11:06

0 Answers0