Find common attributes from nested array in MongoDB

user_11072775 0
Viewed 156

Below is my schema:

  [{
    "attributes":[
        {
            "attrId":"123",
            "values":[
                    {"name": "asd"},
                    {"name": "bcc"}
                ]
        },
        {
            "attrId":"456",
            "values":[
                    {"name": "zzz"},
                    {"name": "bcc"}
                ]
        }
        ]
},

{
    "attributes":[
        {
            "attrId":"123",
            "values":[
                    {"name": "asd"},
                    {"name": "bcc"}
                ]
        },
        {
            "attrId":"456",
            "values":[
                    {"name": "kkk"},
                    {"name": "bcc"}
                ]
        }
        ]
}]

I want output as:

[{123:["asd","ccc"]}]

i.e to return attrId and values if all values match with all other documents, I am thinking it to convert values into string like "asdbcc" so that it will be easy to compare.

node.js mongodb javascript
1 Answers

You can try,

  • $group by null and make array of root objects and get count of total objects in root
  • $unwind deconstruct root array that we have created in above stage
  • $unwind deconstruct root.attributes array
  • $group by root.attributes.attrId and root.attributes.values and get total count of grouped documents and values to get k(key) as attrId and v(value) as array of values from values object using $map and get first totalCount field
  • $match expression using $expr check common document count and total root object document both are equal then return matching documents
  • $replaceRoot to replace and object after converting from $arraToObject of values array
db.collection.aggregate([
  {
    $group: {
      _id: null,
      root: { $push: "$$ROOT" },
      totalCount: { $sum: 1 }
    }
  },
  { $unwind: "$root" },
  { $unwind: "$root.attributes" },
  {
    $group: {
      _id: {
        attrId: "$root.attributes.attrId",
        values: "$root.attributes.values"
      },
      values: {
        $addToSet: {
          k: "$root.attributes.attrId",
          v: {
            $map: {
              input: "$root.attributes.values",
              in: "$$this.name"
            }
          }
        }
      },
      count: { $sum: 1 },
      totalCount: { $first: "$totalCount" }
    }
  },
  { $match: { $expr: { $eq: ["$count", "$totalCount"] } } },
  { $replaceRoot: { newRoot: { $arrayToObject: "$values" } } }
])

Playground

user_89871280
Related