Mongo group document property's data by 2 fields

Question

I have a document with the following structure:

    {
        _id: "some_id",
        ... ,
        properties: [
            {
                "document": "doc1",
                "sheet": "sheet1",
                "property": "property1"
            },
            {
                "document": "doc1",
                "sheet": "sheet2",
                "property": "property2"
            },
            {
                "document": "doc1",
                "sheet": "sheet2",
                "property": "property3"
            },
            {
                "document": "doc2",
                "sheet": "sheet1",
                "property": "property4"
            },
        ]
    }

I would like to create a query to find all the properties of the document with ID _id, and then group the data by document and sheet property values

Example:

    {
      "document": "File 1",
      "result": [
        {
          "sheet": "sheet1",
          "data": [{...}]
        },
        {
          "sheet": "sheet2",
          "data": [{...}]
        },
        {
          "sheet": "sheet3",
          "data": [{...}]
        }
      ]
    }

How can I perform the grouping/aggregation of the properties of a certain document?

I am not used to MongoDB, and the only thing I managed to do is to obtain the properties projection document with the following query:

    db.getCollection('myCollection')
        .find({}, {_id:UUID("21e1fd87-6e22-4487-85d5-18e639f9b710"), properties: 1})

user_14389830 · Answer

You can do it with Aggregation Framework:

$match - to filter the document with _id field
$unwind - to deconstruct properties array field
$group - to group by document and sheet fields
$project to return only the fields that you want

db.collection.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$unwind": "$properties"
  },
  {
    "$group": {
      "_id": {
        "document": "$properties.document",
        "sheet": "$properties.sheet"
      },
      "data": {
        "$addToSet": "$properties.property"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "document": "$_id.document",
      "result": {
        "sheet": "$_id.sheet",
        "data": "$data"
      }
    }
  }
])

Working example

Mongo group document property's data by 2 fields

1 Answers