Return first element if no match found in array

Question

I have the following document:

{
    _id: 123,
    state: "AZ",
    products: [
        {
            product_id: 1,
            desc: "P1"
        },
        {
            product_id: 2,
            desc: "P2"
        }   
    ]
}

I need to write a query to return a single element from the products array where state is "AZ" and product_id is 2. If the matching product_id is not found, then return the first (or any) element from the products array.

For example: If product_id is 2 (match found), then the result should be:

products: [
    {
        product_id: 2,
        desc: "P2"
    }   
]

If the product_id is 3 (not found), then the result should be:

products: [
    {
        product_id: 1,
        desc: "P1"
    }   
]

I was able to meet one condition when the match is found but not sure how to satisfy the second condition in the same query:

db.getCollection('test').find({"state": "AZ"}, {_id: 0, state: 0, products: { "$elemMatch": {"product_id": "2"}}})

I tried using the aggregation pipeline as well but could not find a working solution.

Note: This is different from the following question as I need to return a default element if the match is not found: Retrieve only the queried element in an object array in MongoDB collection

@AnthonyWinzlet, I need to return a default element in case the match is not found. This was not asked in the question you mentioned as duplicate. — ASK, Oct 02 '18 at 18:06

Ashh · Answer 1 · 2018-10-02T20:03:28.940

You can try below aggregation

Basically you need to $filter the products array and check for the $condition if it doesn't contain any element or equal to [] then you have to $slice with the first element of the products array.

db.collection.aggregate([
  { "$addFields": {
    "products": {
      "$cond": [
        {
          "$eq": [
            { "$filter": {
              "input": "$products",
              "cond": { "$eq": ["$$this.product_id", 2] }
            }},
            []
          ]
        },
        { "$slice": ["$products", 1] },
        { "$filter": {
          "input": "$products",
          "cond": { "$eq": ["$$this.product_id", 2] }
        }}
      ]
    }
  }}
])

or even using $let aggregation

db.collection.aggregate([
  { "$addFields": {
    "products": {
      "$let": {
        "vars": {
          "filt": {
            "$filter": {
              "input": "$products",
              "cond": { "$eq": ["$$this.product_id", 2] }
            }
          }
        },
        "in": {
          "$cond": [
            { "$eq": ["$$filt", []] },
            { "$slice": ["$products", 1] },
            "$$filt"
          ]
        }
      }
    }
  }}
])

Thanks Anthony. As this solution would only work on MongoDB 3.4 onwards, i was wondering if I simply change the $addFields to $project to make it work on v3.2 as I have access to 3.2 only. It didn't work using $project. I will test this on 3.4 and newer as well but wanted to see how this query can be written for older versions? — ASK, Oct 03 '18 at 04:35
It will work if you use `$project` instead of `$addFields` because all the operators had been introduced in version 3.2. Here is the working example https://mongoplayground.net/p/BFD4CucD86t — Ashh, Oct 03 '18 at 06:10
Great, this works! Thanks for providing the working example. — ASK, Oct 03 '18 at 16:09

dnickless · Accepted Answer · 2018-10-03T07:11:30.317

If you don't care which element you get back then this is the way to go (you'll get the last element in the array in case of no match since $indexOfArray will return -1):

db.getCollection('test').aggregate([{
    $addFields: {
        "products": {
            $arrayElemAt: [ "$products", { $indexOfArray: [ "$products.product_id", 2 ] } ]
        },
    }
}])

If you want the first then do this instead ($max will take care of transforming -1 into index 0 which is the first element):

db.getCollection('test').aggregate([{
    $addFields: {
        "products": {
            $arrayElemAt: [ "$products", { $max: [ 0, { $indexOfArray: [ "$products.product_id", 2 ] } ] } ]
        },
    }
}])

Here is a version that should work on v3.2 as well:

db.getCollection('test').aggregate([{
    "$project": {
        "products": {
            $slice: [{
                $concatArrays: [{
                    $filter: {
                        "input": "$products",
                        "cond": { "$eq": ["$$this.product_id", 2] }
                    }},
                    "$products" // simply append the "products" array
                   // alternatively, you could append only the first or a specific item like this [ { $arrayElemAt: [ "$products", 0 ] } ]
                ]
            },
            1 ] // take first element only
        }
    }
}])

Thanks @dnickless, this would work on MongoDB 3.4 onwards only. Can we have a solution which would work on 3.2 as well? I would test it on 3.4+ as well but I need to solve this on 3.2 at this moment. — ASK, Oct 03 '18 at 04:39

Return first element if no match found in array

2 Answers2

Linked