I have the following type of documents in my MongoDB database:

[
  {
    "_id": {
      "$oid": "63267309592e38db589c576d"
    },
    "name": "ASOS DESIGN tapered smart shorts in white and navy stripe",
    "color predictions": [
      {
        "Beige": {
          "$numberInt": "1"
        }
      },
      {
        "Black": {
          "$numberInt": "2"
        }
      },
      {
        "White": "1"
      }
    ],
    "color": "Black"
  },
  {
    "_id": {
      "$oid": "84253452492e38db589c576d"
    },
    "name": "ASOS DESIGN tapered smart shorts in white and navy stripe",
    "color predictions": [
      {
        "Brown": {
          "$numberInt": "3"
        }
      },
      {
        "Green": {
          "$numberInt": "1"
        }
      },
      {
        "Navy Blue": "1"
      }
    ],
    "color": "Brown"
  }
]

I would like to pull documents based on a list of colors by matching them to values found in the color predictions column: for example I would like to query ['Brown', 'Navy Blue'] and get the second document and any other documents that have those colors in the color predictions column.

How can I craft a query to get those documents?

Thank you, any help would be great.

CodePudding user response：

1. $set - Set colorPredictions field.

   1.1. $reduce - Iterate the document in color predictions array and return a new array.

   1.1.1. input - Array from color predictions field.

   1.1.2. initialValue - Initialize the output to be returned from $reduce operator as an array (empty array).

   1.1.3. in - Iterate each document by converting it from key-value pair into an array with the document containing k and v fields. And adding it into the accumulator ($$value) via $concatArray.

2. $match - Filter document with colorPredictions.k is within the search filter array.

3. $unset - Remove colorPredictions field from the document.

db.collection.aggregate([
  {
    $set: {
      colorPredictions: {
        $reduce: {
          input: {
            "$getField": "color predictions"
          },
          initialValue: [],
          in: {
            "$concatArrays": [
              "$$value",
              {
                $objectToArray: "$$this"
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      "colorPredictions.k": {
        $in: [
          "Brown",
          "Navy Blue"
        ]// Search filter
        
      }
    }
  },
  {
    $unset: "colorPredictions"
  }
])

Demo @ Mongo Playground