MongoDB : Retrieve Associated Value from Object in an Array of Arrays

Question

In mongo I have a documents that follow the below pattern :

{
 name: "test",
 codes: [
  [
    {
      code: "abc",
      value: 123 
    },
    {
      code: "def",
      value: 456 
    },
  ],
  [
    {
      code: "ghi",
      value: 789 
    },
    {
      code: "jkl",
      value: 012 
    },
  ]
 ]
}

I'm using an aggregate query (because of joins) and in a $project block I need to return the "name" and the value of the object that has a code of "def" if it exists and an empty string if it doesn't.

I can't simply $unwind codes and $match because the "def" code is not guaranteed to be there.

$filter seems like the right approach as $elemMatch doesn't work, but its not obvious to me how to do this on nested array of arrays.

This question is still unclear, So you just need name, value, value can either be ' ' or 456 ?? Can you edit this question with an expected output !! — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Feb 27, 2020 at 1:07
I don't seem to be able to edit the question but the return value would be: { name: "test", value: 456 } — Chris Coddington
– Chris Coddington, Commented Feb 27, 2020 at 15:01

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-02-27 18:01:34Z

1

You can try below query, instead of unwinds & filter this can give you required result with less docs to operate on :

db.collection.aggregate([
    /** merge all arrays inside codes array into code array */
    {
        $addFields: {
            codes: {
                $reduce: {
                    input: '$codes',
                    initialValue: [],
                    in: { $concatArrays: ["$$value", "$$this"] }
                }
            }
        }
    },
    /** project only needed fields & value will be either def value or '', 
     * if 'def' exists in any doc then we're check index of it to get value of that particular object using arrayElemAt */
    {
        $project: {
            _id:0, name: 1, value:
            {
                $cond: [{ $in: ["def", '$codes.code'] }, { $arrayElemAt: ['$codes.value', { $indexOfArray: ["$codes.code", 'def'] }] }, '']
            }
        }
    }])

Test : MongoDB-Playground

answered Feb 27, 2020 at 18:01

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

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

1 Comment

Chris Coddington Over a year ago

You are a freaking genius. Thank you! I couldn't upvote but this is definitely the answer.

Collectives™ on Stack Overflow

MongoDB : Retrieve Associated Value from Object in an Array of Arrays

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