Question 412393 in Mongodb Question posted in
The official documentation can be found here.

How to refer to sub field in mongodb pipeline when the filed name includes "."

I defined following pipeline in Python. I hit an issue when a label includes . like "1.2.3".

labels.{label} becomes labels.1.2.3
$labels.{k} becomes $labels.1.2.3

pipeline = [
    {
        "$match": {
            "$and": [
                {"deleted": {"$ne": True}},  
                {
                    "$or": [
                        {f"labels.{label}": {"$exists": True}} for label in label_list
                    ]
                }
            ]
        }
    },
    {
        "$project": {
            "_id": 0,
            "command": 1,
            "matching_labels": {
                k: f"$labels.{k}" for k in label_list
            }
        }
    }
]

I asked chatgpt but it failed to help with a solution. Could you help me out?

I tried using . to escape the . and also [] instead of . but neither worked.

Example of documents:

{
  "_id": {
    "$oid": "670cd0bae77a7a8a17abfb71"
  },
  "command": "show alarms ",
  "original": "show alarms",
  "labels": {
    "critical": 2,
    "rx": 1,
    "failure": 1,
    "6.5.3": 1,
    "isis": 1,
    "l2vpn": 1,
    "chassis": 1,
    "ncs-5501": 1,
    "logs": 1,
    "7.5.2": 1,
    "hardware failure": 1,
    "6.6.3": 1
  },
  "merged": true,
  "finalised": true
}
Tags:

2

Answers


  1. 0

    $getField can be used for fields which have periods . or dollars $ in their name. And when that field does not exist, the $type is missing. So use that for $match stage.

    Corresponding to that, in the $project stage, use $setField. Or as I’ve done below, I’m using $replaceWith instead of $project.

    Taking two labels "1.2.3" and "6.5.3" as an example, this would be the aggregation pipeline – each time I’ve repeated a block for those two labels is where you’d replace it in Python with your list comprehension.

    db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          { "$ne": ["$deleted", true]},
          {
            "$or": [
              {
                "$ne": [
                  {
                    "$type": {
                      "$getField": { "field": "1.2.3", "input": "$labels" }
                    }
                  },
                  "missing"
                ]
              },
              {
                "$ne": [
                  {
                    "$type": {
                      "$getField": { "field": "6.5.3", "input": "$labels" }
                    }
                  },
                  "missing"
                ]
              }
            ]
          }
        ]
      }
    }
  },
  { "$set": { "matching_labels": { "$literal": {} } } },
  {
    "$replaceWith": {
      "command": "$command",
      "matching_labels": {
        "$mergeObjects": [
          {
            "$setField": {
              "field": "1.2.3",
              "input": "$matching_labels",
              "value": {
                "$getField": { "field": "1.2.3", "input": "$labels" }
              }
            }
          },
          {
            "$setField": {
              "field": "6.5.3",
              "input": "$matching_labels",
              "value": {
                "$getField": { "field": "6.5.3", "input": "$labels" }
              }
            }
          }
        ]
      }
    }
  }
])

    In this Mongo Playground example, notice that only the fields which exist are set in "matching_labels", because if the value for $setField is missing, then the field does not get set.

    Side note: storing "unknown"/dynamic field names with their values, is an anti-pattern. It should be name-value pairs like labels: [{name: "1.2.3", value: 2}, {name: "other", value: 100}, {...}]. Read about the Attribute Pattern.

    Login or Signup to reply.
  2. 0

    My previous answer is about using $getField and $setField to solve the specific issue you were facing with . in the field names. This answer is an alternative way to get the same results for the label_list provided.

    1. Use $objectToArray to convert your name-value object to a list of objects in this form [{k: "1.2.3", v: 2}, {k: "other", v: 100}, {...}].
    2. Then filter for items where the label in item.k is in your list of labels.
    3. Switch it back to original Object-format using $arrayToObject.
    4. Remove items where there are no matching labels.
    5. Project the necessary fields.

    To make this work in Python, add double-quotes, use label_list in place of ["1.2.3", "6.5.3"], and use Python’s True instead of JavaScript’s true.

    db.collection.aggregate([
  {
    $match: { deleted: { $ne: true } }
  },
  {
    $set: {
      matching_labels: {
        $arrayToObject: {
          $filter: {
            input: { $objectToArray: "$labels" },
            cond: {
              // your `label_list` here
              $in: ["$$this.k", ["1.2.3", "6.5.3"] ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      matching_labels: { $ne: {} }
    }
  },
  {
    "$project": {
      "_id": 0,
      "command": 1,
      "matching_labels": 1
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search