Mongoose query for docs matching array, no matter its elements order

Question

Using MongoDB/Mongoose, consider my test collection having the following 2 documents:

"_id" : ObjectId("5c4e3cef9cd12904cfc7ecc0"),
"name" : "REGISTER1",
"properties" : [
        {
            "property” : "PROP1",
            "value" : "GREEN"
        },
        {
            "property” : "PROP2",
            "value" : "RED"
        }
    ]

and

"_id" : ObjectId("5c4e3cef9cd12904cfc7ecc0"),
"name" : "REGISTER2",
"properties" : [
        {
            "property" : "PROP2",
            "value" : "RED"
        },
        {
            "property" : "PROP1",
            "value" : "GREEN"
        }
    ]

I'm using the folowing commands to find the registers:

db.tests.find(
   { properties: [
            {
                "property" : "PROP2",
                "value" : "RED"
            },
            {
                "property" : "PROP1",
                "value" : "GREEN"
            }
        ]
   }).pretty();

That returns REGISTER2, not REGISTER1.

In the same way:

db.tests.find(
   { properties: [
            {
                "property" : "PROP1",
                "value" : "GREEN"
            },
            {
                "property" : "PROP2",
                "value" : "RED"
            }
        ]
   }).pretty();

That returns REGISTER1, not REGISTER2.

I need both queries to return both elements 'REGISTER1' and 'REGISTER2', as the only change between then is the properties order (the array order).

How can I query to return both REGISTER1 and REGISTER2, no matter the array order of the query elements ?

Answer 1

You can use an $all query for this:

db.tests.find({
  properties: {$all: [
   {property: 'PROP1', value: 'GREEN'},
   {property: 'PROP2', value: 'RED'} 
]}})

Both elements of the $all have to exist for the document to match, but the order is irrelevant.

For the case where properties elements contain more keys besides what you're matching on, you can use $elemMatch:

db.tests.find({
  properties: {$all: [
   {$elemMatch: {property: 'PROP1', value: 'GREEN'}},
   {$elemMatch: {property: 'PROP2', value: 'RED'}} 
]}})

$elemMatch ensures each property/value tuple matches the same element.

Answer 2

The property name on first document is property_id and second document is property

Please use $elemMatch with $or to get both the matching documents

db.tests.find({
    properties : {$elemMatch : {$or : [
        {"property" : "PROP1", "value" : "GREEN"},
        {"property" : "PROP2", "value" : "RED"}
    ]}}
})

sample collection

> db.tests.find()
{ "_id" : ObjectId("5c4e3cef9cd12904cfc7ecc0"), "name" : "REGISTER1", "properties" : [ { "property" : "PROP1", "value" : "GREEN" }, { "property" : "PROP2", "value" : "RED" } ] }
{ "_id" : ObjectId("5c4e3cef9cd12904cfc7ecc1"), "name" : "REGISTER2", "properties" : [ { "property" : "PROP2", "value" : "RED" }, { "property" : "PROP1", "value" : "GREEN" } ] }

find

> db.tests.find({properties : {$elemMatch : {$or : [{"property" : "PROP1", "value" : "GREEN"},{"property" : "PROP2", "value" : "RED"}]}}})
{ "_id" : ObjectId("5c4e3cef9cd12904cfc7ecc0"), "name" : "REGISTER1", "properties" : [ { "property" : "PROP1", "value" : "GREEN" }, { "property" : "PROP2", "value" : "RED" } ] }
{ "_id" : ObjectId("5c4e3cef9cd12904cfc7ecc1"), "name" : "REGISTER2", "properties" : [ { "property" : "PROP2", "value" : "RED" }, { "property" : "PROP1", "value" : "GREEN" } ] }
>