Mongodb $project: $filter sub-array

Question

There is an items (mongoose) schema that looks like this (simplified to what it matters to the question):

{
    brand: {
        name: String,
    },
    title: String,
    description: [{ lang: String, text: String }],
    shortDescription: [{ lang: String, text: String }],
    variants: {
        cnt: Number,
        attrs: [
            {
                displayType: String,
                displayContent: String,
                displayName: [{ lang: String, text: String }],
                name: String,
            },
        ],
    }
}

I'm trying to filter the items by language, so I've constructed the following query:

db.items.aggregate([
    { $match: { 'description.lang': 'ca', 'shortDescription.lang': 'ca' } },
    { $project: {
        'brand.name': 1,
        title: 1,
        description: {
            '$filter': {
                input: '$description',
                as: 'description',
                cond: { $eq: ['$$description.lang', 'ca'] }
            }
        },
        shortDescription: {
            '$filter': {
                input: '$shortDescription',
                as: 'shortDescription',
                cond: { $eq: ['$$shortDescription.lang', 'ca'] }
            }
        },
        'variants.cnt': 1,
        'variants.attrs': 1
    } }
])

And it works as expected: it filters description and shortDescription by language. Right now I'm wondering if it could be possible to filter every variants.attrs.$.displayName as well. Is there any way to do it?

I've been trying to $unwind variant.attrs but I get completly lost when trying to $group again and I'm not really sure if this is the best way...

I have a question: description, shortDescription, variants.attrs and variants.attrs.displayName are lists. All these 4 lists can have multiple elements? — sergiuz
– sergiuz, Commented Sep 6, 2016 at 22:06
Yes, they contain texts for every language. description: [{ lang:'en', text: 'This item is awful' }, { lang: 'es', text: 'Este producto es increíble' }, ...] — Miquel
– Miquel, Commented Sep 7, 2016 at 5:37

Nishant Bhardwaj · Accepted Answer · 2016-09-13 04:10:52Z

You are nearly there. Try these steps:

use $unwind stage before $project stage to expand the outer array of documents, i.e. variants.attrs
Add filter for the sub array variants.attrs.displayName in the $project stage.
You will have to project all the sub fields of variants key.
Next add $group stage and group by all the elements except the sub-array. Use $push to rebuild the sub array in group by stage.

Lastly, add $project stage to rebuild the document to its original structure.

db.items.aggregate([
  { $match: { 'description.lang': 'ca', 'shortDescription.lang': 'ca' } },
  { $unwind : "$variants.attrs" },
  { $project: {
     '_id' : 1,
     'brand.name': 1,
     title: 1,
     description: {
        '$filter': {
            input: '$description',
            as: 'description',
            cond: { $eq: ['$$description.lang', 'ca'] }
        }
     },
     shortDescription: {
       '$filter': {
           input: '$shortDescription',
           as: 'shortDescription',
           cond: { $eq: ['$$shortDescription.lang', 'ca'] }
        }
     },
     'variants.attrs.displayName' : {
        '$filter' : {
           input: '$variants.attrs.displayName',
           as: 'variants_attrs_displayName',
           cond: { $eq : ['$$variants_attrs_displayName.lang','ca']}
        }
      },

      'variants.cnt': 1,
      'variants.attrs.displayType': 1,
      'variants.attrs.displayContent' : 1,
      'variants.attrs.name' : 1
    } 
 } , { $group:
          {
            _id : { 
                _id: "$_id",
                title: "$title",
                brand:"$brand",
                description:"$description",
                shortDescription:"$shortDescription", 
                variants_cnt : "$variants.cnt"
                },
            variants_attrs : { $push : 
            { 
              displayType : "$variants.attrs.displayType",
              displayContent : "$variants.attrs.displayContent",
              displayName : "$variants.attrs.displayName",
              name: "$variants.attrs.name" 
            }
          }
        }
    },
{ $project : 
 {
    "_id" : 0,
    brand : "$_id.brand",
    title : "$_id.title",
    description : "$_id.description",
    shortDescription : "$_id.shortDescription",
    variants : {
      cnt : "$_id.variants_cnt" ,
      attrs : "$variants_attrs"
     }
   }  
 }
])

Depending on your use case, you should reconsider your data model design to avoid duplication of filter values. i.e. 'description.lang': 'ca', 'shortDescription.lang': 'ca', 'variants.attrs.displayName.lang': 'ca'

This is one of the solutions. The cons is that you need to know the model before it is localized (so it's not standarized). Finally I adopted a solution with ref fields and populating them with selected language. The con of this other solutin is one extra read to fetch translations, but it is scalable.

Collectives™ on Stack Overflow

Mongodb $project: $filter sub-array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related