$in query with in an array with a list of values is not working

Working with Data
, ,
1

Hi,

I have the following collection

[
  {

    "fiedl1": "value1",
    "field2": "value2",
    "master": [
      {
        "attributes": {
          "key": [
            "FIRSTKEY"
          ]
        },
        "Status": "ACTIVE"
      },
      {
        "attributes": {
          "key": [
            "SECONDKEY"
          ]
        },
        "Status": "ACTIVE"
      },
      {
        "attributes": {
          "key": [
            "THIRDKEY"
          ]
        },
        "Status": "ACTIVE"
      }
    ]
  }
]

I’m trying to filter only master.attributes.key in [‘FIRSTKEY’, ‘SECONDKEY’] and expecting a response as shown below

[
  {

    "field1": "value1",
    "field2": "value2",
    "master": [
      {
        "attributes": {
          "key": [
            "FIRSTKEY"
          ]
        },
        "Status": "ACTIVE"
      },
      {
        "attributes": {
          "key": [
            "SECONDKEY"
          ]
        },
        "Status": "ACTIVE"
      }
    ]
  }
]

I tried the following aggregate command

db.collection.aggregate([
  {
    $match: {
      "master.attributes.key": {
        $in: [
          "FIRSTKEY",
          "SECONDKEY"
        ]
      }
    }
  },
  {
    $project: {
      master: {
        $filter: {
          input: "$master",
          as: "master",
          cond: {
            $in: [
              "$$master.attributes.key",
              [
                "FIRSTKEY",
                "SECONDKEY"
              ]
            ]
          }
        }
      },
      _id: 0
    }
  }
])

But I got below response instead of filtered . Appreciate any help here

[
  {
    "master": []
  }
]
2

The issue is that localizationKey in the source document is an array but in

you use it as it was a simple string value. The following should work:

 $in: [
              "$purpose.attributes.localizationKey",
              [
                [ "cs_profiling" ] ,
                [ "cs_research" ]
              ]
            ]

Note that if localizationKey in the original document contains anything else than the single word as shared then it will not work. Since it is an array, then, one day, it will contain something else than a single word. Using

{ $ne : { { $setIntersection … } , [ ] } }

might then be a better idea.

3

Thanks @steevej I tried the below query, but still empty master response

db.collection.aggregate([
  {
    $match: {
      "master.attributes.key": {
        $in: [
          "FIRSTKEY",
          "SECONDKEY"
        ]
      }
    }
  },
  {
    $project: {
      master: {
        $filter: {
          input: "$master",
          as: "master",
          cond: {
            $in: [
              "$$master.attributes.key",
              [
                ["FIRSTKEY"],
                ["SECONDKEY"]
              ]
            ]
          }
        }
      },
      _id: 0
    }
  }
])

[
  {
    "master": []
  }
]
4

You have modified each and every sample documents from your original post.

Sorry but I do not work on moving targets.

The query I supplied worked in the original documents.

5

Apologies, for renaming the fields. I tried the solution here https://mongoplayground.net/ but I got an empty response

6

The below command worked. Thanks @steevej for your help

db.collection.aggregate([
  {
    $match: {
      "master.attributes.key": {
        $in: [
          "FIRSTKEY",
          "SECONDKEY"
        ]
      }
    }
  },
  {
    $project: {
      master: {
        $filter: {
          input: "$master",
          as: "master",
          cond: {
            $in: [
              "$$master.attributes.key",
              [
                [
                  "FIRSTKEY"
                ],
                [
                  "SECONDKEY"
                ]
              ]
            ]
          }
        }
      }
    }
  }
])
1 Like
7

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