MongoDB query - unwind and match preserving null OR different value/ add a new field based on a condition

Question

If a have a following structure :

{
    _id: 1,
    name: 'a',
    info: []
},
{
    _id: 2,
    name: 'b',
    info: [
        {
            infoID: 100,
            infoData: 'my info'
        }
    ]
},
{
    _id: 3,
    name: 'c',
    info: [
        {
            infoID: 200,
            infoData: 'some info 200'
        },
        {
            infoID: 300,
            infoData: 'some info 300'
        }
    ]
}

I need to query in such a way to obtain the documents where infoID is 100 showing the infoData, or nothing if info is empty, or contains subdocuments with infoID different from 100.

That is, I would want the following output:

{
    _id: 1,
    name: 'a',
    infoData100: null
},
{
    _id: 2,
    name: 'b',
    infoData100: 'my info'
},
{
    _id: 3,
    name: 'c',
    infoData100: null
}

If I $unwind by info and $match by infoID: 100, I lose records 1 and 3.

Thanks for your responses.

I think you can do this with aggregation using $filter to select only the array elements you want, and then $cond inside of $project to set the final value — Joe
– Joe, Commented Mar 18, 2020 at 16:31

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-18 16:36:35Z

1

Try below query :

Query :

db.collection.aggregate([
  /** Adding a new field or you can use $project instead of addFields */
  {
    $addFields: {
      infoData100: {
        $cond: [
          {
            $in: [100, "$info.infoID"] // Check if any of objects 'info.infoID' has value 100
          },
          {
            // If any of those has get that object & get infoData & assign it to 'infoData100' field
            $let: {
              vars: {
                data: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: "$info",
                        cond: { $eq: ["$$this.infoID", 100] }
                      }
                    },
                    0
                  ]
                }
              },
              in: "$$data.infoData"
            }
          },
          null // If none has return NULL
        ]
      }
    }
  }
]);

Test : MongoDB-Playground

answered Mar 18, 2020 at 16:36

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

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

1 Comment

JD Gamboa Over a year ago

I am sorry but our requirements changed and I no longer needed the above query. Thank you so much for your response and indeed it seems to work, so I'll mark it a valid (even though I truly have no idea how it is working haha).

Collectives™ on Stack Overflow

MongoDB query - unwind and match preserving null OR different value/ add a new field based on a condition

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related