Problem with array

Working with Data Python Frameworks
1

Good day, I’ve been scraching my head for a while now. I can’t figure out how to get data from docs in 3 diferent collections:

  1. Document from tentants collection:
{
  "_id": {
    "$oid": "671802da14d80e976eb4c1dd"
  },
  "date": "2024-10-22T12:00:00",
  "tenant": "NAME OF TENANT",
  "rooms": [
    [
      "TN",
      "N2",
      "201"
    ],
    [
      "TN",
      "N3",
      "301"
    ]
  ],
  "status": "active"
}
  1. Documents from device_distribution:
{
  "_id": {
    "$oid": "6717f23c14d80e976eb4c112"
  },
  "line": 1,
  "label": "L1",
  "device": "TN-N2-1",
  "room": "201",
  "level": "N2",
  "building": "TN",
  "date": "2024-10-22T12:00:00"
}

{
  "_id": {
    "$oid": "6717f23c14d80e976eb4c113"
  },
  "line": 2,
  "label": "L2",
  "device": "TN-N2-1",
  "room": "201",
  "level": "N2",
  "building": "TN",
  "date": "2024-10-22T12:00:00"
}

{
  "_id": {
    "$oid": "6717f23c14d80e976eb4c114"
  },
  "line": 3,
  "label": "L3",
  "device": "TN-N2-1",
  "room": "201",
  "level": "N2",
  "building": "TN",
  "date": "2024-10-22T12:00:00"
}
  1. Documents from records collection:
{
  "_id": {
    "$oid": "673213c0a9c31e9eb57b53c1"
  },
  "date": "2024-11-11T14:00:00",
  "device": "TN-N2-1",
  "line": 1,
  "building": "TN",
  "reading": 1.5,
  "connections": 1
}

{
  "_id": {
    "$oid": "673213e1a9c31e9eb57b53c3"
  },
  "date": "2024-11-11T23:00:00",
  "device": "TN-N2-1",
  "line": 1,
  "building": "TN",
  "reading": 2.5,
  "connections": 1
}

This is what I am trying to achieve using an agregation pipeline:

  1. Get:
  • _id
  • tenant
  • rooms

From collection:

  • tenants

Filter by:

  • _id: ObjectId(“671802da14d80e976eb4c1dd”)
  • status: “active”
  1. Get:
  • line
  • device
  • room
  • level
  • building

From collection:

  • device_distribution

Filter by:

  • building
  • level
  • room <<< Here is where the first problem occures, I need to filter the documents by the info in the rooms array I get from the tenants collection
  1. Get:
  • device
  • line
  • reading
  • connections

From collection:

  • records

Filter by:

  • date (only latest doc from current month and year)
  • device
  • line

I would greatly appreciate for help

2

What have you tried? Share the aggregation you tried. How did it failed?

Please provide an example of the result that you want.

You could bring more people to work on your issue by creating a playground with your sample documents.

1 Like