AWS DMS文档模式下无法通过转换规则访问_doc列问题咨询

问题背景 #

测试AWS Database Migration Service（DMS）转换规则，源数据库为AWS DocumentDB（兼容MongoDB），目标为AWS RDS PostgreSQL，采用**文档模式（document mode）**迁移。该模式下目标表默认生成_doc列存储完整Mongo文档JSON，_id列存储ObjectId字符串。

尝试通过add-column操作从_doc列提取内容生成新列，但发现转换规则的表达式中无法访问$_doc（返回空值），但$_id可以正常访问，无论目标类型设为string还是clob均存在此问题。

测试详情 #

源DocumentDB集合数据

[ { _id: ObjectId("6479f558e1000ae07668a13b"), FOO: 'bar' } ]

使用的DMS映射JSON

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "719174426",
      "rule-name": "719174426",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "add-column",
      "value": "COPY_OF_ID_AS_CLOB",
      "old-value": null,
      "data-type": {
        "type": "clob"
      },
      "expression": "$_id"
    },
    {
      "rule-type": "transformation",
      "rule-id": "719122613",
      "rule-name": "719122613",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "add-column",
      "value": "COPY_OF_ID_AS_STRING",
      "old-value": null,
      "data-type": {
        "type": "string",
        "length": "200",
        "scale": ""
      },
      "expression": "$_id"
    },
    {
      "rule-type": "transformation",
      "rule-id": "719057983",
      "rule-name": "719057983",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "add-column",
      "value": "COPY_OF_DOC_AS_STRING",
      "old-value": null,
      "data-type": {
        "type": "string",
        "length": "200",
        "scale": ""
      },
      "expression": "$_doc"
    },
    {
      "rule-type": "transformation",
      "rule-id": "649580464",
      "rule-name": "649580464",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "add-column",
      "value": "COPY_OF_DOC_AS_CLOB",
      "old-value": null,
      "data-type": {
        "type": "clob"
      },
      "expression": "$_doc"
    },
    {
      "rule-type": "selection",
      "rule-id": "641760382",
      "rule-name": "641760382",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "include",
      "filters": []
    }
  ]
}

目标PostgreSQL表结构

column_name      |     data_type
-----------------------+-------------------
 _id                   | character varying
 _doc                  | text
 COPY_OF_DOC_AS_CLOB   | text
 COPY_OF_DOC_AS_STRING | character varying
 COPY_OF_ID_AS_STRING  | character varying
 COPY_OF_ID_AS_CLOB    | text
(6 rows)

目标表数据结果

_id                   | 6479f558e1000ae07668a13b
_doc                  | { "_id" : { "$oid" : "6479f558e1000ae07668a13b" }, "FOO" : "bar" }
COPY_OF_DOC_AS_CLOB   |
COPY_OF_DOC_AS_STRING |
COPY_OF_ID_AS_STRING  | 6479f558e1000ae07668a13b
COPY_OF_ID_AS_CLOB    | 6479f558e1000ae07668a13b

问题原因 #

DMS文档模式下，_doc列是DMS在源数据读取完成后，迁移到目标端时才构建的衍生列，并非源DocumentDB中的原生字段。而转换规则的表达式解析逻辑是在DMS读取源数据的阶段执行的，此时_doc还未生成，因此无法通过$_doc直接引用该列的值。

而_id是源DocumentDB集合中的原生字段，所以可以在表达式中通过$_id直接获取对应值。

解决方案 #

要实现复制完整文档内容或提取文档字段生成新列，需要直接操作源文档对象，而非DMS生成的_doc列：

1. 复制完整文档内容 #

使用$代表整个源文档对象，通过JSON()函数将其序列化为JSON字符串，效果与_doc列内容一致：

{
  "rule-type": "transformation",
  "rule-id": "719057983",
  "rule-name": "719057983",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "%",
    "table-name": "%"
  },
  "rule-action": "add-column",
  "value": "COPY_OF_DOC_AS_STRING",
  "old-value": null,
  "data-type": {
    "type": "string",
    "length": "2000",
    "scale": ""
  },
  "expression": "JSON($)"
}

针对CLOB类型，只需调整data-type配置即可，表达式保持不变。

2. 提取文档中的特定字段 #

如果只需要提取文档中的某个字段（比如FOO），可以直接通过$.字段名引用：

{
  "rule-type": "transformation",
  "rule-id": "xxxxxx",
  "rule-name": "xxxxxx",
  "rule-target": "column",
  "object-locator": {
    "schema-name": "%",
    "table-name": "%"
  },
  "rule-action": "add-column",
  "value": "FOO_VALUE",
  "old-value": null,
  "data-type": {
    "type": "string",
    "length": "200",
    "scale": ""
  },
  "expression": "$.FOO"
}

验证效果 #

修改转换规则后重新执行迁移，COPY_OF_DOC_AS_STRING和COPY_OF_DOC_AS_CLOB列将填充与_doc列完全一致的JSON内容。

内容的提问来源于stack exchange，提问作者rharrington