0

I have two collections

  1. Reports

    {"brand": "Nike", "created": "2021-05-03T20:12:32.911000", "id": ObjectId("60929848088f18212809221c")}

  2. Status

    {"id": "60929848088f18212809221c", "report": ObjectId("60929848088f18212809221c"), "created": "2021-05-05T20:12:32.911000"}

I want to join these collections based on the report(in Status collection) - _id ( in Reports collection).

I tried the below query(The code here is complex because it does a few more things that are necessary to me):

query = [
        {'$group': {'_id': '$_id', 'data': {'$last': '$$ROOT'}}},
        {'$match': match},
        {'$sort': {'data.created': pymongo.DESCENDING}},
        {'$facet': {
            'stage1': [{'$group': {'_id': None, 'count': {'$sum': 1}}}],
            'stage2': [{'$skip': offset}, {'$limit': limit}]
        }},
        {'$unwind': '$stage1'},
        {'$project': {
            'count': '$stage1.count',
            'data': '$stage2'
        }},
        {
            "$lookup": {
                "from": "status",
                "localField": "data._id",
                "foreignField": "report",
                "as": "report"
            }
        }
    ]

I expect such a result:

[
  {
    "count": 3,
    "data": [
      {
        "_id": ObjectId("5a934e000102030405000001"),
        "data": {
          "_id": ObjectId("5a934e000102030405000001"),
          "brand": "Nike",
          "created": "2021-05-03T20:12:32.911000",
        },
        "report": {
            "_id": ObjectId("5a934e000102030405000003"),
            "created": "2021-05-05T20:12:32.911000",
            "id": "60929848088f18212809221c",
            "report": ObjectId("5a934e000102030405000001")
        },
      },
      }
    ]
  }
]

I mean I mean it will be in every result

I tried to get help with that too but it did not work Here. I'm using Mongo DB 4+

Emma92
  • 55
  • 1
  • 5
  • just try passing `"localField": "data.data.id",` in localField. – turivishal May 09 '21 at 16:50
  • Not working, Maybe because I'm using "self.reports.aggregate(query)"? – Emma92 May 09 '21 at 18:31
  • see working [playground](https://mongoplayground.net/p/tpTeZXYN5_o) – turivishal May 09 '21 at 18:33
  • It still does not work, but I meant the result to be for each retrieval, for example: [{"count": 1,"data": [{"_id": ObjectId("5a934e000102030405000000"),"data": {"_id": ObjectId("5a934e000102030405000000"),"brand": "Nike","created": "2021-05-03T20:12:32.911000","id": ObjectId("60929848088f18212809221c")},"report": [{"_id": ObjectId("5a934e000102030405000001"),"created": "2021-05-05T20:12:32.911000","id": "60929848088f18212809221c","report": ObjectId("60929848088f18212809221c")}]}],}] – Emma92 May 10 '21 at 07:53
  • your question is not clear, you can edit your question and please elaborate with details. post some example documents and expected result. – turivishal May 10 '21 at 08:01

0 Answers0