如何更新Couchbase中含复杂嵌套对象的记录的version字段？

问题场景 #

现有Couchbase数据库中的文档结构如下：

{"_class": "com.discussoftware.server.shared.model.bo.licenseBo","customerId": "A1A2","term": "SUBSCRIPTION","keys": {"abc-123-evaluation": {"active": true,"expirationDate": 1669680000000,"key": "abc-123-evaluation","name": "base product, floating, subscription","notBefore": 1667088000000,"options": [{"option": "base","quantity": 1},{"option": "secondary","quantity": 1},{"option": "teriary","quantity": 1},{"option": "ops","quantity": 1}],"units": 1,"version": {"major": 1,"minor": 1,"patch": 0}}},"master": "def-456","productId": "a1a2a3a4","supportId": "A1A2A3"}

可以通过以下SELECT语句正确筛选出符合条件的文档：

SELECT  *
FROM `server1` AS d
WHERE ANY v in OBJECT_VALUES(d.keys) 
   SATISFIES v.expirationDate < 1713744000000 
     AND v.expirationDate > 1701734400000
     AND v.version.major == 1
     AND v.version.minor == 1
     AND v.version.patch == 0
   END;

但执行以下UPDATE语句时返回0行，无法完成version字段的更新：

UPDATE `server1` AS d 
SET v.version = {"major": 2, "minor": 1, "batch": 0}
WHERE ANY v in OBJECT_VALUES(d.keys)
  SATISFIES v.expirationDate < 1713744000000
     AND v.expirationDate > 1701734400000
     AND v.version.major == 1
     AND v.version.minor == 1
     AND v.version.patch == 0
  END;

解决方案 #

原UPDATE语句失效的原因是：ANY ... SATISFIES仅用于筛选文档，WHERE子句中的迭代变量v无法直接在SET子句中引用进行修改。需要通过重构keys对象的方式，遍历其中的每个键值对，对符合条件的子对象单独更新version字段。

正确的UPDATE语句如下：

UPDATE `server1` AS d
SET d.keys = OBJECT k: IF(
    v.expirationDate < 1713744000000 
    AND v.expirationDate > 1701734400000
    AND v.version.major == 1
    AND v.version.minor == 1
    AND v.version.patch == 0,
    OBJECT_MERGE(v, {"version": {"major": 2, "minor": 1, "patch": 0}}),
    v
) FOR k, v IN d.keys END
WHERE ANY v in OBJECT_VALUES(d.keys)
  SATISFIES v.expirationDate < 1713744000000
     AND v.expirationDate > 1701734400000
     AND v.version.major == 1
     AND v.version.minor == 1
     AND v.version.patch == 0
  END;

语句说明 #

• OBJECT k: ... FOR k, v IN d.keys END：遍历d.keys中的每个键值对，重新构建整个keys对象
• IF(条件, 更新逻辑, 原对象)：对每个子对象v判断是否符合筛选条件，符合则更新version，不符合则保持原样
• OBJECT_MERGE(v, {"version": ...})：合并原对象和新的version字段，避免覆盖子对象的其他属性
• 保留原WHERE子句：确保只处理包含符合条件子对象的文档，避免无意义的更新操作

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