How do I make a query that matches documents where a combination of attributes is in a list?

How do I make a query such that given a list: {“column1”:1,“column2”:1},{“column1”:2,“column2”:2},etc…

I only get the documents in a collection where BOTH attributes match BOTH attributes in an object in the list?
So I don’t get documents like {“column1”:1,“column2”:2},{“column1”:2,“column2”:1}
As far as I know I can only do an $and with $in and that doesn’t work because that can return an object where one attribute matches the list but not the other one.

Hi @Alejandro_Carrazzoni,

I think what you are looking for is $elemMatch . Please read the following comment:

Please let me know if you have any additional questions.

Best regards,
Pavel

$elemMatch only works with elements inside an array. I need something like $elemMatch but that works with attributes in the main document, not in sub-documents inside an array

Hello : )

The above code , filters the docs,and keeps a doc,only if it a member on mylist

var mylist = [{"column1":1,"column2":1},{"column1":2,"column2":2}];

insert docs = [{"column1":1,"column2":1},{"column1":2,"column2":2},{"column1":1,"column2":2},{"column1":2,"column2":1}];
{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$project": {
        "_id": 0
      }
    },
    {
      "$match": {
        "$expr": {
          "$in": [
            "$$ROOT",
            mylist           //the var from above
          ]
        }
      }
    }
  ],
  "maxTimeMS": 0,
  "cursor": {}
}

Result (only the members of mylist passed)

{"column1" : 1, "column2" : 1}
{"column1" : 2, "column2" : 2}

Hope it helps.

1 Like

What if I add a column3 attribute and I want to select all columns but only put column1 and column2 in the in expression? How can I do it?
So I have this:

var mylist = [{"column1":1,"column2":1},{"column1":2,"column2":2}];

insert docs = [{"column1":1,"column2":1,"column3":1},{"column1":2,"column2":2",column3":2},{"column1":1,"column2":2,"column3":3},{"column1":2,"column2":1,"column3":4}];

And i want this to be the result

{"column1" : 1, "column2" : 1,"column3":1}
{"column1" : 2, "column2" : 2,"column3":3}

Instead of $$ROOT you can construct the matching document.

{"column1" : "$column1" ,"column2" : "$column2"}
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.