Problem with array

Vitaly_Abilevich · 2024-11-28T23:16:53.431Z

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:

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"
}

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"
}

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:

Get:

_id
tenant
rooms

From collection:

tenants

Filter by:

_id: ObjectId(“671802da14d80e976eb4c1dd”)
status: “active”

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

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

steevej · 2024-12-02T02:43:34.414Z

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.

steevej · 2024-12-09T15:39:35.010Z

@Vitaly_Abilevich, 8 days ago I took the time to reply your post and asked for more information.

I would appreciate some followup. If you found the solution to your issue please share it in a new post and mark it as the solution.