0

we have a nested json as follows:

{
"_id" : ObjectId("5a074fe0b9d31f246c102078"),
"projects" : [ 
    {
        "workstreams" : [ 
            {
                "cvids" : [ 
                    {
                        "opportunity" : {
                            "resources" : [ 
                                {
                                    "utilisation" : 100,
                                    "weekly_details" : [],
                                    "fte_start_date" : "2017-11-13T00:00:00.000Z",
                                    "fte_end_date" : "2017-11-27T00:00:00.000Z",
                                    "fte_value" : 1
                                }
                            ],
                            "utilisation" : 100,
                            "_id" : ObjectId("5a098fe0b9d31f246c102077")
                        },
                        "opportunity_cvid" : "33333",
                        "rpl_cvid" : "33333"
                    }
                ],
                "workstream_name" : "Proj 1 WS1"
            }
        ],
        "project_name" : "Proj 1"
    },
    {
        "workstreams" : [ 
            {
                "cvids" : [ 
                    {
                        "opportunity" : {
                            "resources" : [ 
                                {
                                    "utilisation" : 100,
                                    "weekly_details" : [],
                                    "fte_start_date" : "2017-11-13T00:00:00.000Z",
                                    "fte_end_date" : "2017-11-27T00:00:00.000Z",
                                    "fte_value" : 1
                                }
                            ],
                            "utilisation" : 100,
                            "_id" : ObjectId("5a098fe0b9d31f246c642077")
                        },
                        "opportunity_cvid" : "121212",
                        "rpl_cvid" : "121212"
                    }
                ],
                "workstream_name" : "Proj2 WS2"
            }
        ],
        "project_name" : "Test Proj2"
    },
    {
        "workstreams" : [ 
            {
                "cvids" : [ 
                    {
                        "opportunity" : {
                            "resources" : [ 
                                {
                                    "utilisation" : 100,
                                    "weekly_details" : [],
                                    "fte_start_date" : "2017-11-13T00:00:00.000Z",
                                    "fte_end_date" : "2017-11-27T00:00:00.000Z",
                                    "fte_value" : 1
                                }
                            ],
                            "utilisation" : 100,
                            "_id" : ObjectId("5a098fe0b9d31f306c102077")
                        },
                        "opportunity_cvid" : "33333",
                        "rpl_cvid" : "33333"
                    },
                    {
                        "opportunity" : {
                            "resources" : [ 
                                {
                                    "utilisation" : 100,
                                    "weekly_details" : [],
                                    "fte_start_date" : "2017-11-13T00:00:00.000Z",
                                    "fte_end_date" : "2017-11-27T00:00:00.000Z",
                                    "fte_value" : 1
                                }
                            ],
                            "utilisation" : 100,
                            "_id" : ObjectId("5a741fe0b9d31f306c102077")
                        },
                        "opportunity_cvid" : "656565",
                        "rpl_cvid" : "656565"
                    }
                ],
                "workstream_name" : "Proj3 WS 3"
            }
        ],
        "project_name" : "Test Proj 3"
    }
],
"contract_name" : "Contract 1"}

I am fairly new to MongoDB. I want to make a generic query to find a list of all 'workstream_name' for the given project_name and contract_name.

For e.g for contract_name='Contract 1'(Top level array) and project_name='Test Proj2'(level 2 array) the output should be:

["Proj2 WS2"]

I have tried many queries. The closest I could get is:

db.getCollection('opportunity').distinct("projects.workstreams.workstream_name", {'contract_name': 'ADRO', 'projects.project_name': 'Proj 1'})

Output:

[
"Proj2 WS2",
"Proj3 WS 3",
"Proj 1 WS1"
]

In this output, the last two values do not match the criteria. Kindly suggest how the query can be improved to get only the workstream_names that match the criteria.

pdiwakar0709
  • 23
  • 6
  • https://docs.mongodb.com/manual/core/aggregation-pipeline/index.html – Alex Blex Nov 14 '17 at 16:24
  • This question is different in terms of the expected output. With the solution in the mentioned question and my mentioned queries, i get a json as the output. What i want is an array of strings matching the condition. – pdiwakar0709 Nov 15 '17 at 12:43
  • @Neil : Kindly reopen the post. – pdiwakar0709 Nov 15 '17 at 13:49
  • You were pointed to the correct process because I am well aware that "distinct" cannot filter conditions within an array. What you have been shown is how you do. But aggregate does not return an "array of strings". It returns "documents". If you want to extract just the values from the documents returned, then that is a separate process done "after" returning the aggregation result. It's very simple to do, as the other linked duplicate demonstrates. You cannot use "distinct" for this, is the overall point, as well as the basic process is already answered. – Neil Lunn Nov 16 '17 at 06:26

0 Answers0