How to construct a mongo query that aggregates on two fields?

Question

TL;DR; How do I query mongo in a way that aggregates a collection two different ways?

I'm learning to query MongoDB. Suppose that I have a collection that contains the results of users submitting a questionnaire and then reviewers signing off that they have reviewed it.

A record in my collection looks like this:

{
    _id: 0,
    Questions:
    [
        {
            label: "fname",
            response: "Sir"
        },  
        {
            label: "lname",
            response: "Robin"
        },
        {
            label: "What is your name?",
            response: "Sir Robin of Camelot"
        },
        {
            label: "What is your quest?",
            response: "To seek the holy grail"
        },
        {
            label: "What is the capital of Asyria?",
            response: "I don't know that."
        }
    ],
    Signatures:
    [
        "Lancelot",
        "Arthur"
    ]
}

I am creating a report that will display a summary of each record. For each record, I need to display the following:

The first name
The last name
The number of signatures.

I am able to write an aggregate query that gets the first and last name. I am also able to write an aggregate query that gets the number of signatures.

However, I am stuck when I try to write an aggregate query that gets both.

// In order to query the first and last name, I use this query:
[
    { $unwind: "$Questions" },
    { $match: { "Questions.Label": { $in: ["fname", "lname"] } } },
    { $project: { "Questions": 1 } },
    { $group: { _id: "$_id", Questions: { $push: "$Questions" } } }
]

// In order to query the number of signatures, I use this query:
[
    { $project: { "SignatureCount": { $size: "$Signatures" } } }
]

Everything works with these two queries, but I want to write a single query that returns the data all together.

So, for example, if the example record above were the only record in my collection, I would want the query to return this:

{
    _id: 0,
    Questions:
    [
        {
            label: "fname",
            response: "Sir"
        },  
        {
            label: "lname",
            response: "Robin"
        }
    ],
    SignatureCount: 2
}

Might require an edit: the input document uses label fields and the aggregation query uses Label. — Xavier Guihot
– Xavier Guihot, Commented Mar 15, 2018 at 19:42

s7vr · Accepted Answer · 2018-03-15 19:37:18Z

1

You can rewrite the query to get both in single project stage in 3.4 version.

Use $filter to filter Questions.

 [
  {"$match":{"Questions.Label":{"$in":["fname", "lname"]}},
  {"$project":{
    "Questions":{
      "$filter":{
        "input":"$Questions",
        "as":"q",
        "cond":{"$in":["$$q.Label",["fname","lname"]]}
      }
    },
    "SignatureCount":{"$size":"$Signatures"}
  }}
]

answered Mar 15, 2018 at 19:37

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

7 Comments

Vivian River Over a year ago

I'm seeing an error that says invalid operator '$in' code: 15999.

s7vr Over a year ago

You will need 3.4 version. db.version() on shell to check your version. I can add the workaround if you need for lower versions.

s7vr Over a year ago

You can use workaround mentioned here

Vivian River Over a year ago

It looks like both shell and DB are version 3.2.3. But I just downloaded and installed it a few days ago. Why should I have gotten an old version?

s7vr Over a year ago

Did you try "cond":{$setIsSubset: [["$$q.Label"],["fname","lname"]]} ?

|

Collectives™ on Stack Overflow

How to construct a mongo query that aggregates on two fields?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related