How to Filter MongoDB Data Based on Array Values?

Question

I have a document in a collection which have values in array.

{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "black", "price": 22000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "XYZ",
  "details": [
     {"color": "yellow", "price": 10000}, 
     {"color": "black", "price": 12000}, 
     {"color": "green", "price": 11000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "pink", "price": 32000}, 
     {"color": "blue", "price": 31000}
  ]
}

I want to filter data for color red and blue and need output like

{"name": "ABC", "color": "red", "price": 20000},
{"name": "ABC", "color": "blue", "price": 21000},
{"name": "CBD", "color": "red", "price": 30000},
{"name": "CBD", "color": "blue", "price": 31000}

what will the query for this...

I am able to get

{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "blue", "price": 31000}
  ]
}

but I want like

{"name": "ABC", "color": "red", "price": 20000},
{"name": "ABC", "color": "blue", "price": 21000},
{"name": "CBD", "color": "red", "price": 30000},
{"name": "CBD", "color": "blue", "price": 31000}

let me know the MongoDB query or JS/TS code to simplify array object...

Answer 1

You can use flatMap to get desired formatted data like below:

const data = [
  {
    "name": "ABC",
    "details": [
       {"color": "red", "price": 20000}, 
       {"color": "blue", "price": 21000}
    ]
  },
  {
    "name": "CBD",
    "details": [
       {"color": "red", "price": 30000}, 
       {"color": "blue", "price": 31000}
    ]
  }
];

const result = data.flatMap(entry => entry.details.map(detail => ({name: entry.name, ...detail})));

console.log(result);

Answer 2

You can run the below js code (if you don't mind the performance):

let initialArr = [{
    "name": "ABC",
    "details": [
       {"color": "red", "price": 20000}, 
       {"color": "blue", "price": 21000}
    ]
  },

  {
    "name": "CBD",
    "details": [
       {"color": "red", "price": 30000}, 
       {"color": "blue", "price": 31000}
    ]
  }
]
let resultArr = []
for (let i = 0; i < initialArr.length; i++) {
    for (let k = 0; k < initialArr[i].details.length; k++) {
        resultArr.push({name: initialArr[i].name, ...initialArr[i].details[k]})
    }
}

Answer 3

Using Array.flatMap() can do it ,below is reference for you,you can check at flattening-an-array-of-complex-objects-with-an-array-attribute

let data = [
{
  "name": "ABC",
  "details": [
     {"color": "red", "price": 20000}, 
     {"color": "black", "price": 22000}, 
     {"color": "blue", "price": 21000}
  ]
},
{
  "name": "XYZ",
  "details": [
     {"color": "yellow", "price": 10000}, 
     {"color": "black", "price": 12000}, 
     {"color": "green", "price": 11000}
  ]
},
{
  "name": "CBD",
  "details": [
     {"color": "red", "price": 30000}, 
     {"color": "pink", "price": 32000}, 
     {"color": "blue", "price": 31000}
  ]
}
]

data = data.filter(d1 => d1.details.some(d2 => d2.color == "red" || d2.color == "blue"))
data = data.flatMap(({ name,details }) => details.map(d => ({ name, ...d })))

console.log(data)

Answer 4

MongoDB's $unwind operator can help -

Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

Here's an aggregate query -

db.getCollection('your_collection').aggregate([
  // create a document for each element in `details` array
  { $unwind : "$details" },

  // filter document with color red or blue
  { $match: { "details.color": { $in: ["red", "blue"] } } },
  
  // output in desired format
  { $project: { name: 1, color: "$details.color", price: "$details.price" } }
])

1. This is how unwounded data would look like -

[
  {"name": "ABC", "details": { "color": "red", "price": 20000} },
  {"name": "ABC", "details": { "color": "blue", "price": 22000} },
  {"name": "ABC", "details": { "color": "blue", "price": 21000} },
  {"name": "XYZ", "details": { "color": "yellow", "price": 10000} },
  {"name": "XYZ", "details": { "color": "black", "price": 12000} },
  ...
]

2. Filtered, unwounded data (only red & blue)-

[
  {"name": "ABC", "color": "red", "price": 20000},
  {"name": "ABC", "color": "blue", "price": 21000},
  {"name": "CBD", "color": "red", "price": 30000},
  {"name": "CBD", "color": "blue", "price": 31000}
]

Answer 5

I think, if you like to get good performance then this would be the best option:

db.collection.aggregate([
   { $match: { "details.color": { $in: ["red", "blue"] } } },
   {
      $set: {
         details: {
            $filter: {
               input: "$details",
               cond: { $in: ["$$this.color", ["red", "blue"]] }
            }
         }
      }
   },
   { $unwind: "$details" },
   { $replaceWith: { $mergeObjects: [{ name: "$name" }, "$details"] } }
])

Mongo Playground

Answer 6

By using aggregate the below query helps you alot.

db.collection.aggregate([
  {
    $unwind: "$details"
  },
  {
    $match: {
      "details.color": { $in: ["red", "blue"] }
    }
  },
  {
    $project: {
      _id: 0,
      name: 1,
      color: "$details.color",
      price: "$details.price"
    }
  }
]);