Filtering one collection using DBRef from a second one that refers to another third

I know using DBRefs is controversial. Regretfully, I’m not the DBA, so I can’t change its structure.

I have one collection called sensorsData that stores documents generated by some devices, identified by their id:

idDevice: 2542

The value might change, there are tons of devices.

Regarding these documents, I’m only interested in those about broadcastings, identified by a concrete id, #3:

idSensor: 3

Other sensors do other stuff, one device might have different sensors.

Both fields are at level 0, after the id of the document itself:

{
	"_id": {"$oid":"5f1957c7cdf25116937ed3ef"},
	"idSensor":{"$numberLong":"3"},
	"idDevice":{"$numberLong":"48"},
	...
}

I need to filter all devices related to a concrete customer, identified by its specific id, #11. This id is referenced using a DBRef within a collection called devices:

{
	"_id": {"$numberLong":"2542"},
	"group": {"$id":{"$numberLong":"11"}},
	...
}

Notice this _id is the idDevice from the 1st collection.

groups is the third collection, where the id of every customer originally lies:

{
	"_id":{"$numberLong":"11"},
	"name":"MyCustomerOfInterest",
	...
}

I guess I don’t need to use this 3rd collection, the key information is stored within the 2nd one, that relates the customer to its devices.

So, the question:

How can I use devices to filter the devices -or the documents with idSensor #3- from sensorsData that are related to this customer #11?

Thanks in advance.

Hello @Javier_Blanco,

You can do an aggregation $lookup on the sensorsData and devices collections. The query can look like this (a lookup of Join Conditions and Uncorrelated Sub-queries):

db.sensorsData.aggregate([
  { 
    $match: { idSensor : NumberLong("3") } 
  },
  { 
    $lookup: {
        from: "devices",
        let: { sensorsDeviceId: "$idDevice" },
        pipeline: [ 
            { 
                $match: { 
                    $expr: {
                        $and: [
                            { $eq: [ "$$sensorsDeviceId", "$_id" },
                            { $eq: [ "$group", NumberLong("11") }
                        ]
                    }
                }
            }
       ],
       as: "group_sensors_data"
    }
  },
]) 

Let me know how this works out.

1 Like

Hi, @Prasad_Saya, thanks a lot for your answer.

I came out with something more obvious:

var pipeline = 
[
	{
		"$match": {"group.$id": 11}
	},
	{
        "$lookup":
        {
            "from": "sensorsData",
            "localField": "_id",
            "foreignField": "idDevice",
            "as": "array"
        }
    },
    {
        "$unwind": "$array"
    },
    {
		"$match": {"array.idSensor": 3}
	}, 
...
]

db.devices.aggregate(pipeline)

After the last $match there are a couple of $addFields and a $project.

Your solution looks far better; mine doesn’t filter by idSensor until doing the $unwind, so I’m carrying tons of files I don’t need during first stages. Im trying to implement the query in a BI application and the server is unable to load the data, I always end up getting a timeout error.

I’ll try to fix mine using yours, thanks again!

Sure. The query can benefit from indexes, an obvious one is on the idSensor field used in the following match stage: $match: { idSensor : NumberLong("3") }

Also refer: Aggregation Pipeline Optimization

1 Like

This is my new try:

var pipeline = 
[
  	{ 
    	"$match": {"idSensor" : 3} 
  	},
	{ 
        "$lookup": 
        {
            "from": "devices",
            "let": {"sensorsDeviceId": "$idDevice"},
            "pipeline": [{"$match": {"$expr": {"$and": [{"$eq": ["$$sensorsDeviceId", "$_id"]}, {"$eq": ["group.$id", 11]}]}}}],
            "as": "array"
        }
	},
    {
        "$unwind": "$array"
    },
...
]

db.sensorsData.aggregate(pipeline)

As in the previous query, after the $unwind there are a couple of $addFields and a $project .

Regretfully, this new query doesn’t work; I mean, I don’t get any error message, it just keeps on going on forever, while the first one just needs about 100 s to give back an answer… any hint?

Thanks in advance!

Hello @Javier_Blanco,

I don’t know what is the reason for the slow query. Are there any indexes on these collections? Did you follow the rules of the aggregation optimization (the link I had included earlier). Also, the query behavior is depending upon factors like Query Selectivity.

May be, you have to try the queries (both of them) on a smaller sample set of documents from the two collections, and also Analyze Query Performance.

1 Like

I guess there are much more documents with idSensor #3 than #11 customer documents with any kind of idSensor. Even if I just try only the first $match: {"idSensor": 3}, the query goes on forever. That might be the problem.

Given that every sensorsData document includes dates:

{
	"_id": {"$oid":"5f1957c7cdf25116937ed3ef"},
	"idSensor": {"$numberLong":"3"},
	"idDevice":{"$numberLong":"48"},
	...
	"data":
	{
		"inicio":"2019-11-28T16:09:08+01:00",
		"fin":"2019-11-28T16:09:18+01:00",
		...
	},
	...
}

I’m trying to add them as filters to my original query, within the second $match:

[
...
	{
        "$match":
        {
            "$expr":
            {
                "$and": 
                [
                    {"array.idSensor": 3},
                    {"$gt": ["array.data.inicio", "ISODate('2021-02-28T23:59:59+01:00')"]},
                    {"$lt": ["array.data.fin", "ISODate('2021-03-15T00:00:01+01:00')"]}
                ]
            }
        }
    },
...
]

But I’m getting this error message:

“FieldPath field names may not contain ‘.’.”

What is it about?

Hello @Javier_Blanco,

Please post couple sample documents with relevant fields after the { "$unwind": "$array" }, stage - I believe the $match stage you had posted is following it - so that I can see what the data looks like and what is wrong with your query.

I am not too sure but I would try to use $ for the field names as it is usually required withing a $expr.

See https://docs.mongodb.com/manual/reference/operator/query/expr

1 Like