I have the follwoing 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