MongoDB:使用聚合将ObjectId转换为DBRef。

huangapple go评论120阅读模式
英文:

MongoDB: Aggregation to convert ObjectId to DBRef

问题

以下是翻译好的部分:

"Is it possible to have an aggregation that converts an objectId column into a dbRef? I am attempting to use aggregation and $merge to transform 1 table with an ObjectId column into a DBRef one

{
$addFields: {
name: "$field",
parentId: {
$cond: {
if: { $ne: ["$parentId", null] },
then: DBRef("parents", "$parentId"),
else: null
}
}
},
},

tried this but got

> An object representing an expression must have exactly one field"

英文:

Isit possible to have an aggregation that converts an objectId column into a dbRef? I am attempting to use aggregation and $merge to transform 1 table with an ObjectId column into a DBRef one

{
    $addFields: {
        name: "$field",
        parentId: {
            $cond: {
                if: { $ne: ["$parentId", null] },
                then: DBRef("parents", "$parentId"),
                else: null
            }
        }
    },
},

tried this but got

> An object representing an expression must have exactly one field

答案1

得分: 1

I think it is. Internally DBRefs are stored in this structure in MongoDB,

{ "$ref" : <value>, "$id" : <value>, "$db" : <value> }

So when you execute your query, the parentId becomes of this form, and you get an error because MongoDB interprets any key beginning with $ to be an operator in the aggregation pipeline. You can overcome this using, $setField like this:

db.collection.aggregate([
  {
    "$addFields": {
      "parentIdCopy": {
        $cond: {
          if: {
            $ne: [
              "$parentId",
              null
            ]
          },
          then: {
            "$setField": {
              "field": {
                $literal: "$ref"
              },
              "input": {},
              "value": "parents"
            },
            
          },
          else: null
        }
      }
    }
  },
  {
    "$addFields": {
      "parentIdCopy": {
        $cond: {
          if: {
            $ne: [
              "$parentId",
              null
            ]
          },
          then: {
            "$setField": {
              "field": {
                $literal: "$id"
              },
              "input": "$parentIdCopy",
              "value": "$parentId"
            },
            
          },
          else: null
        }
      }
    }
  },
  {
    "$set": {
      "parentId": "$parentIdCopy",
      parentIdCopy: "$$REMOVE"
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace",
      
    }
  }
])

In this query, we are manually generating the object in DBRef format, rather than using the constructor. Playground link.

英文:

I think it is. Internally DBRefs are stored in this structure in MongoDB,

{ &quot;$ref&quot; : &lt;value&gt;, &quot;$id&quot; : &lt;value&gt;, &quot;$db&quot; : &lt;value&gt; }

So when you execute your query, the parentId becomes of this form, and you get an error because MongoDB interprets any key beginning with $ to be an operator in the aggregation pipeline. You can overcome this using, $setField like this:

db.collection.aggregate([
  {
    &quot;$addFields&quot;: {
      &quot;parentIdCopy&quot;: {
        $cond: {
          if: {
            $ne: [
              &quot;$parentId&quot;,
              null
            ]
          },
          then: {
            &quot;$setField&quot;: {
              &quot;field&quot;: {
                $literal: &quot;$ref&quot;
              },
              &quot;input&quot;: {},
              &quot;value&quot;: &quot;parents&quot;
            },
            
          },
          else: null
        }
      }
    }
  },
  {
    &quot;$addFields&quot;: {
      &quot;parentIdCopy&quot;: {
        $cond: {
          if: {
            $ne: [
              &quot;$parentId&quot;,
              null
            ]
          },
          then: {
            &quot;$setField&quot;: {
              &quot;field&quot;: {
                $literal: &quot;$id&quot;
              },
              &quot;input&quot;: &quot;$parentIdCopy&quot;,
              &quot;value&quot;: &quot;$parentId&quot;
            },
            
          },
          else: null
        }
      }
    }
  },
  {
    &quot;$set&quot;: {
      &quot;parentId&quot;: &quot;$parentIdCopy&quot;,
      parentIdCopy: &quot;$$REMOVE&quot;
    }
  },
  {
    &quot;$merge&quot;: {
      &quot;into&quot;: &quot;collection&quot;,
      &quot;on&quot;: &quot;_id&quot;,
      &quot;whenMatched&quot;: &quot;replace&quot;,
      
    }
  }
])

In this query, we are manually generating the object in DBRef format, rather than using the constructor. Playground link.

huangapple
  • 本文由 发表于 2023年5月25日 12:15:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76328900.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定