[SOLVED] Couchbase remove attributes by pattern

Couchbase remove attributes by pattern

I have a Couchbase bucket with simple document objects. Due to a bug in the application unwanted attributes with names that end with '}__modified' were inserted into the bucket. I want to remove all these attributes ("UNSET") from the bucket. What would be the right N1QL syntax to accomplish that?

SELECT * FROM mainstore LIMIT 3

  {
    "mainstore": {
      "class_id": "9853ede22ad54d4da86c6d9abc91110a",
      "class_name": "BLE_34V2",
      "company": "76092d9bb1b148d18c3484f447a554b4",
      "connection_type": "gateway",
      "conns}__modified":45,
      "conns5}__modified":45,
      "clsd5}__modified":45
    },
    "mainstore": {
      "class_id": "6443ede22ad54d4da86c6d9abc91110a",
      "class_name": "BLE_34V2",
      "company": "76092d9bb1b148d18c3484f447a554b4",
      "connection_type": "client",
      "conns}__modified":45,
      "consf5}__modified":46,
      "clsd5}__modified":46
    },
    "mainstore": {
      "class_id": "1343ede22ad54d4da86c6d9abc91110a",
      "class_name": "BLE_34V2",
      "company": "76092d9bb1b148d18c3484f447a554b4",
      "connection_type": "client",
      "dfffs}__modified":45,
      "dffs5}__modified":47,
      "fdss5}__modified":46
    }
  }

Solution

ASSUME those are top level attributes

CREATE INDEX ix1 ON mainstore (
           CONTAINS(encode_json(OBJECT_NAMES(self)), "}")
           ) 
    WHERE CONTAINS(encode_json(OBJECT_NAMES(self)), "}") = true;

If you want remove "}" from field name

UPDATE mainstore AS m
SET m = OBJECT REPLACE(n,"}",""):v FOR n:v IN m END
WHERE CONTAINS(encode_json(OBJECT_NAMES(m)), "}") = true;

If you want remove all the fields that contain "}" from document

UPDATE mainstore AS m
SET m = OBJECT n:v FOR n:v IN m  WHEN POSITION(n,"}") < 0 END
WHERE CONTAINS(encode_json(OBJECT_NAMES(m)), "}") = true;

If you have lot of documents things can be slow. If using EE use eventing and update them. You can find a examples here https://docs.couchbase.com/server/current/eventing/eventing-examples.html