MongoDB: Get Last Element of Array if Property Exists

Question

I would like to aggregate the last setting with an existing value.

My data structure:

{
  _id: 1,
  ...(many other fields)...
  settings: [
    {
      _id: 11,
      values: [
        {
          likesFruit: true,
          likesMeat: true,
        }
      ] 
   },
   {
      _id: 12,
      values: [
        {
          likesFruit: false
        }
      ] 
    }
  ]
}, 
{
  _id: 2,
  ...(many other fields)...
  settings: [] // empty
}

Question: How to get this result

{
  _id: 1,
  ...(many other fields)...
  likesFruit: false,
  likesMeat: true
},
{
 _id: 2,
 ...(many other fields)...
}

I need a solution which doesn't remove the results where settings are e.g. empty/ not contain all props. E.g._id: 2 does not contain any settings. So I don't want to unwind it and filter it then for a specific setting because I still need the root object even if no setting exists.

I tried it with addFields:

$addFields: {
  likesFruit: "$settings.value.likesFruit",
  likesMeat: "$settings.value.likesMeat"
}

Which leaves me with this:

_id: 1,
...(many other fields)...
settings: [...],
likesFruit: [[true], [false]]
likesMeat: [[], [true]]

Which I tried to filter with:

$addFields: {
  likesFruit: {
    $filter: {
      input: "$likesMeat",
      as: "items",
      cond: { $ne : ["$$items", null ] }
    }
  }
}

My main problem is the double nesting. The "settings.value" I'm interested in has only one object inside. So I guess going from settings.values: [{}] to settings.values: {} would be a key to the solution. Then I could project the single values, use $reverseArray and $arrayElemAt: [$array, 0] to get the last element.

mickl · Accepted Answer · 2019-07-10 18:16:15Z

You can take advantage of $mergeObjects operator since as the documentation states:

mergeObjects overwrites the field values as it merges the documents. If documents to merge include the same field name, the field, in the resulting document, has the value from the last document merged for the field.

As you have nested arrays you need double $reduce to aggregate your data. You can also use $replaceRoot to promote your aggregated values to the top level

db.col.aggregate([
    {
        $addFields: {
            settingsAggregated: {
                $reduce: {
                    input: "$settings",
                    initialValue: {},
                    in: {
                        $mergeObjects: [ "$$value", 
                            { $reduce: { input: "$$this.values", initialValue: {}, in: { $mergeObjects: [ "$$value", "$$this" ] } } }
                        ]
                    }
                }
            }
        }
    },
    {
        $replaceRoot: {
            newRoot: {
                $mergeObjects: [ "$$ROOT", "$settingsAggregated" ]
            }
        }
    },
    {
        $project: {
            settings: 0,
            settingsAggregated: 0
        }
    }
])

Mongo Playground

Collectives™ on Stack Overflow

MongoDB: Get Last Element of Array if Property Exists

Question: How to get this result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Question: How to get this result

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related