How do I access a field inside array inside object inside array in the aggregation framework?

Question

Suppose I had this data structure as my document:

{
  id: 1,
  customers: [
    {
      id: 2,
      addresses: [
        {
          phone: "555-342",
          zipCode: "1946873193",
          addressLine: "whatever"
        }
      ]
    }
  ]
}

How do I access the phone property in the aggregation framework? $ROOT.customers.0.addresses.0.phone doesn't work as a field path expression.

@AlexBlex yes I saw that but can't understand how to use chained "arrayElemAt"s for the cause — shayan
– shayan, Commented Jan 16, 2018 at 17:34
The syntax is { $arrayElemAt: [ "$customers", 0 ] }, not customers.0. Applies to all arrays. — Alex Blex
– Alex Blex, Commented Jan 16, 2018 at 17:38

mickl · Accepted Answer · 2018-01-16 17:39:42Z

3

You can use $let with $arrayElemAt to handle two nesting levels but then you can add $project to get phone property.

db.collection.aggregate([
{
  $addFields: {
    selectedElement: {
        $let: {
           vars: {
              fstCustomer: { $arrayElemAt: [ '$customers', 0 ] }                
           },
           in: { $arrayElemAt: [ '$$fstCustomer.addresses', 0 ] }
        }
     }
  } 
},
{
  $project: {
    phone: "$selectedElement.phone"
  }
}
])

answered Jan 16, 2018 at 17:39

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

Comments

s7vr · Accepted Answer · 2018-01-16 17:40:25Z

2

You can chain the $arrayElemAt in following way.

Outer $let expression to get the first customer and inner $let expression to get the first address element of first customer followed by projection of phone property.

db.col.aggregate(
{"$project":{
  "phone":{
    "$let":{
      "vars":{
        "customer":{"$arrayElemAt":["$customers",0]}
      },
      "in":{
        "$let":{
          "vars":{
            "address":{"$arrayElemAt":["$$customer.addresses",0]}
          },
          "in":"$$address.phone"
        }
      }
    }
  }
}})

answered Jan 16, 2018 at 17:40

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

1 Comment

shayan Over a year ago

I see $let was the missing key. @mickl user answered a min sooner so I should accept his despite the similarity in answer

Collectives™ on Stack Overflow

How do I access a field inside array inside object inside array in the aggregation framework?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related