Select matching array with aggregate

Question

hotdeal:

{ "property" : "ATL-D406" }

translation:

{ "property" : "ATL-D406", "language" : "gb", "txt": [{"aaa":"hi", "bbb":"bye"}] }
{ "property" : "ATL-D406", "language" : "ru", "txt": [{"aaa":"priviet", "bbb":"baka"}] } 
{ "property" : "ATL-D406", "language" : "cn", "txt": [{"aaa":"??", "bbb":"??"}] }

Current result:

{ "property":"ATL-D406", "language":[ "gb", "ru", "cn" ] }

What I would like:

{ "property":"ATL-D406", "language":"gb", "aaa":"hi", "bbb":"bye" }

I cannot understand why my $elemMatch is not working, shouldn't it isolate that particular element in the result from "translation"?

Also once I got it to only take the "gb" language, how do I project "aaa" and "bbb" without them being inside an array? I tried $resolve but then I get no data at all. Can you $resolve an array in an array? ($resolve "txt")?

db.hotdeal.aggregate([ 
    { '$match': {} },
    { '$lookup': { 
        from: 'translation', 
        localField: 'property', 
        foreignField: 'property', 
        as: 'translations' 
    } },

    { '$match': 
        { 'translations': 
            { '$elemMatch': { 'language': 'gb' } }
        } 
    }, 

    { '$project': { 
        _id: 0, 
        property: 1, 
        language: '$translations.language'
    }}
])

Neil Lunn · Accepted Answer · 2017-06-04 06:49:46Z

1

You are doing the wrong operation. You want $filter, $map, and also $arrayElemAt by the look of it. The $elemMatch operator is a "query" operation for "selection of data", and is not used to "filter" array content.

db.hotdeal.aggregate([ 
    { '$match': {} },
    { '$lookup': { 
        'from': 'translation', 
        'localField': 'property', 
        'foreignField': 'property', 
        'as': 'translations' 
    } },
    { '$project': { 
        '_id': 0, 
        'property': 1, 
        'translation': {
         '$arrrayElemAt': [
           { '$map': {
             'input': {
               '$filter': {
                 'input': '$translations'
                 'as': 't',
                 'cond': { '$eq': [ '$$t.language', 'gb' ] }
               },
             },
             'as': 't',
             'in': {
               'language': '$$t',
               'aaa': { '$arrayElemAt': [ '$$t.txt.aaa', 0 ] },
               'bbb': { '$arrayElemAt': [ '$$t.txt.bbb', 0 ] }
             }
           }},
           0
         ]
       }
    }},
    { '$project': {
      'property': 1,
      'language': '$translation.language',
      'aaa': '$translation.aaa',
      'bbb': '$translation.bbb'
    }}
])

I generally find that these sort of "trival" structural changes are best handled in "client code" rather than mangling through the aggregation framework anyway.

So simply do the "join" and then let the "client" process the alteration:

db.hotdeal.aggregate([ 
    { '$match': {} },
    { '$lookup': { 
        'from': 'translation', 
        'localField': 'property', 
        'foreignField': 'property', 
        'as': 'translations' 
    } }
]).forEach( doc => {
  let translation = doc.translations.find( t => t.language === 'gb' );
  doc.language = translation.language;
  doc.aaa = translation.txt[0].aaa;
  doc.bbb = translation.txt[0].bbb;

  delete doc.translations;
  delete doc._id;
  printjson(doc);
});

So that is doing the same thing, yet is far less obtuse.

edited Jun 4, 2017 at 6:49

answered Jun 4, 2017 at 6:39

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

9 Comments

torbenrudgaard Over a year ago

Hi Neil. Interesting but a quite complex way to do it. Are you sure that you cannot use $elemMatch inside a $match operation on the data you got from the $lookup? I believe I have done so before.

Neil Lunn Over a year ago

@torbenrudgaard Read it all. $elemMatch would only "confirm" that the document has the matching property in an element of the array which resulted from $lookup. It does not remove it. That is what you are asking. What I also conclude when you read everything is that the "complex" operation is completely unnecessary. Falls under the category of "Just because you can do it, does not mean you should". The swiss army chainsaw and all that. Don't overuse aggregation functions when you are not "aggregating".

torbenrudgaard Over a year ago

Neil, I just managed to do it like this.... argh! I cannot format code here - let me post it as an answer so you can see it.

torbenrudgaard Over a year ago

Neil have a look at my answer - it actually gave the desired result.

Neil Lunn Over a year ago

@torbenrudgaard $unwind is not an efficient way of processing this. It does work, but there is a large cost with large collections and arrays

|

torbenrudgaard · Accepted Answer · 2017-06-04 06:57:51Z

1

db.hotdeal.aggregate([ 
    { '$match': {} },
    { '$lookup': { 
        from: 'translation', 
        localField: 'property', 
        foreignField: 'property', 
        as: 'translations' 
    } },

    { '$unwind': '$translations' },

    { '$match': { 'translations.language': 'gb' } }, 

    { '$project': { 
        _id: 0, 
        property: 1, 
        language: '$translations.language',
        aaa: '$translations.txt.aaa',
        bbb: '$translations.txt.bbb'
    }}
])

answered Jun 4, 2017 at 6:57

torbenrudgaard

2,6219 gold badges36 silver badges60 bronze badges

Collectives™ on Stack Overflow

Select matching array with aggregate

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related