Access a variable field of an object

[Charles_Haines]

I am trying to query a collection that has a difficult structure to work with.
Here is a sample document with the relevant fields.

{ _id: ObjectId('00093eae20bde40e986354ad'), link: 'aaaaaaaa', dataRequests: { id: [ 'a', 'b'], docs: { a: { id: 'a', sampleValue: 'one' }, b: { id: 'b', sampleValue: 'two' }, c: { id: 'c', sampleValue: 'three' } } } }

I want to add a field in an aggregate that grabs the object in dataRequests.docs where the field is the last value in dataRequests.ids.

My aggregate pipeline looks like this.

[ { $addFields: { lastDrIndex: { $arrayElemAt:["$dataRequests.ids", -1] } } }, { $addFields: { lastDr: "$dataRequests.docs[$lastDrIndex]" } } ]

The first part works as expected and adds a field “lastDrIndex”: “b”.
The second part must not be the correct syntax, because it does not add anything to the results.
I would like the second part to take the value of lastDrIndex and use it to access the dataRequests.docs object at that position.

So in this example it would add a field
“lastDr”: { id: ‘b’, sampleValue: ‘two’ }

How do I fix the second part of the aggregate pipeline to use one field as an accessor to another object ?
Or can I somehow combine the two, since I don’t actually need the lastDrIndex and only need lastDr ?

[Charles_Haines]

Sorry for the formatting, I hope this will fix it.

Sample Document

{
  _id: ObjectId('00093eae20bde40e986354ad'),
  link: 'aaaaaaaa',
  dataRequests: {
    id: [ 'a', 'b'],
    docs: {
      a: { id: 'a', sampleValue: 'one'   },
      b: { id: 'b', sampleValue: 'two'   },
      c: { id: 'c', sampleValue: 'three'   }
    }
  }
}

Aggregate Pipeline

[
  {
    $addFields: {  lastDrIndex: { $arrayElemAt:["$dataRequests.ids", -1] }  }
  },
  {
    $addFields: {  lastDr: "$dataRequests.docs[$lastDrIndex]" }
  }
]

[steevej (Steeve Juneau)]

First, you a typo in your first $addFields. Your field is dataRequests.id but you use dataRequests.ids.

Since docs is not an array, I am not too sure you could use the [ ] syntax. Even when I hard code the value b inside the 2nd $addFields it does not work.

I think you would need to use $objectToArray of dataRequests.docs with a $reduce to find the appropriate sampleValue. If this is a frequent use-case you might want to look at the attribute pattern. This would eliminate the need to use $objectToArray since your data will already be an array.

[Charles_Haines]

I did notice my typo, but I cannot edit the post.
Unfortunately, I cannot feasibly change the way the data is stored.
I would normally choose to use a much more standard practice like you suggested, or normalize the data into other collections.

I can look at the ObjectToArray and reduce functions, thank you.

[Tarun_Gaur]

Hello @Charles_Haines ,

Welcome to The MongoDB Community Forums!

In addition to @steevej’s reply, below is an example of how you can use $objectToArray and $arrayToObject to achieve your desired output.

db.test.aggregate([
    {$addFields: {
        lastDr: {
            $arrayToObject: {
                $filter: {
                    input: {$objectToArray: "$dataRequests.docs"},
                    cond: {$eq: ["$$this.k", {$arrayElemAt:["$dataRequests.id", -1]}]}
                }
            }
        }
    }}
])

Output is:

[
  {
    _id: ObjectId("00093eae20bde40e986354ad"),
    link: 'aaaaaaaa',
    dataRequests: {
      id: [ 'a', 'b' ],
      docs: {
        a: { id: 'a', sampleValue: 'one' },
        b: { id: 'b', sampleValue: 'two' },
        c: { id: 'c', sampleValue: 'three' }
      }
    },
    lastDr: { b: { id: 'b', sampleValue: 'two' } }
  }
]

Note that this is an untested query so you might want to do your own testing to ensure it works with your data.

Regards,
Tarun