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, Mar 18 '20 at 16:31
Possible dup - https://stackoverflow.com/questions/29398260/mongodb-projecting-a-field-that-doesnt-always-exist — DaveStSomeWhere, Mar 18 '20 at 16:33

score 1 · Accepted Answer · answered Mar 18 '20 at 16:36

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

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). — JD Gamboa, Mar 22 '20 at 04:11

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

1 Answers1