如何在Couchbase中批量更新文档内嵌套数组的元素字段值

问题场景 #

你在名为chat的bucket中存储的会话文档结构如下：

{
  "id": "50542400778551298_5001_abcdef",
  "customer": {
    ...
  },
  "seller": {
    ...
  },
  "complaints": [],
  "messages": [
    {
      "id": "2828911d-b96f-4b90-a52c-252d0324ab69",
      "owner": "SELLER",
      "content": "Merhabalar",
      "readingDate": null,
      "createdAt": "2022-06-27T09:16:42.49158+03:00"
    },
    {
      "id": "8cd8c4b7-d599-4dfa-9fa7-be304a3b72b9",
      "owner": "SELLER",
      "content": "İlan hakkında detay bilgi verir misin?",
      "readingDate": null,
      "createdAt": "2022-06-27T09:17:43.329741+03:00"
    },
    {
      "id": "4f0ae912-7d65-4e79-9058-2b048c4d9845",
      "owner": "SELLER",
      "content": "Huhuu",
      "readingDate": null,
      "createdDate": "2022-06-27T09:18:37.491531+03:00"
    },
    {
      "id": "d2dd61b3-7ff1-4139-a411-9a917c00d4f3",
      "owner": "SELLER",
      "content": "Orada kimse var mı?",
      "readingDate": null,
      "createdAt": "2022-06-27T09:18:45.917564+03:00"
    },
    {
      "id": "ea2d8dca-2f8c-4987-9c07-7515a6a0063f",
      "owner": "SELLER",
      "content": "Döner misininiz?",
      "readingDate": null,
      "createdAt": "2022-06-27T09:18:52.80337+03:00"
    }
  ],
  "status": "ACTIVE"
}

需求是将文档中messages数组所有元素的readingDate字段统一更新为指定时间值。你最初编写的N1QL语句如下：

UPDATE c.messages SET c.messages.readingDate = NOW_LOCAL()
FROM chat AS c 
WHERE META().id = '50542400778551298_5001_abcdef';

执行时报语法错误：

[
  {
    "code": 3000,
    "msg": "syntax error - at SET",
    "query": "UPDATE c.messages SET c.messages.readingDate = NOW_LOCAL()\nFROM chat AS c \nWHERE META().id = '50542400778551298_5001_abcdef';"
  }
]

你不想使用先查询数据、再基于CAS值逐元素更新的繁琐方案，希望通过单条N1QL实现需求。

错误原因 #

原语句有两个核心问题：

• N1QL的UPDATE子句后必须直接指定要更新的bucket名称，不能写c.messages这种嵌套路径
• 直接对数组路径赋值无法遍历数组内的每个元素，不能实现批量修改数组内对象字段的效果

正确实现方案 #

使用N1QL内置的ARRAY遍历表达式配合OBJECT_PUT函数，单条语句即可完成原子更新，不需要手动处理CAS或逐元素操作。

1. 更新为固定指定时间 #

如果要把所有readingDate设置为固定值2022-06-27T09:18:52.80337+03:00，语句如下：

UPDATE chat AS c
SET c.messages = ARRAY OBJECT_PUT(m, "readingDate", "2022-06-27T09:18:52.80337+03:00") FOR m IN c.messages END
WHERE META(c).id = '50542400778551298_5001_abcdef';

2. 更新为当前时间 #

如果要设置为语句执行时的本地时间，把固定值替换为NOW_LOCAL()即可：

UPDATE chat AS c
SET c.messages = ARRAY OBJECT_PUT(m, "readingDate", NOW_LOCAL()) FOR m IN c.messages END
WHERE META(c).id = '50542400778551298_5001_abcdef';

逻辑说明 #

• FOR m IN c.messages END 会遍历messages数组的每一个元素，遍历时单个元素临时命名为m
• OBJECT_PUT(m, "readingDate", 目标值) 会对每个元素对象设置readingDate字段，字段已存在时直接覆盖原值（你场景中原值为null，会直接被替换为目标值），字段不存在时自动新增
• 整个ARRAY表达式会生成更新完成的新数组，直接赋值给c.messages即可完成整个文档的更新，操作是数据库层面原子执行的，不会产生并发冲突问题。

扩展用法 #

如果需要批量更新bucket内所有符合条件的文档，只需要修改WHERE子句的筛选规则即可，比如更新所有状态为ACTIVE的文档：

UPDATE chat AS c
SET c.messages = ARRAY OBJECT_PUT(m, "readingDate", NOW_LOCAL()) FOR m IN c.messages END
WHERE c.status = "ACTIVE";

如果使用的Couchbase版本较低不支持OBJECT_PUT，可以用对象移除+新增的组合写法实现相同效果：

UPDATE chat AS c
SET c.messages = ARRAY OBJECT_ADD(OBJECT_REMOVE(m, "readingDate"), "readingDate", NOW_LOCAL()) FOR m IN c.messages END
WHERE META(c).id = '50542400778551298_5001_abcdef';

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