CODEWITHSUNDEEP
couchbaserecordsql++
Ted Herrlich
Ted Herrlich

Reputation: 169

How do you modify a Couchbase record that contains a fairly complex object and sub-object?

I have a couchbase database full of records that look like this:

{
  "_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"
}

I can select based on the license key, with a statement like this:

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;

But what I am having trouble with a writing an UPDATE that will let me update the version value.
I think the best one I have tried -- which returns 0 rows -- out of the 60 or so attempts, has been:

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;

Upvotes: 1

Views: 71

Answers (3)

vsr
vsr

Reputation: 7414

UPDATE `server1` AS d
SET v1.version.major = v1.version.major+1
      FOR n:v1 IN d.`keys`
           WHEN v1.expirationDate < 1713744000000
                AND v1.expirationDate > 1701734400000
                AND v1.version.major == 1
                AND v1.version.minor == 1
                AND v1.version.patch == 0
     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;

https://docs.couchbase.com/cloud/n1ql/n1ql-language-reference/update.html#update-for

Upvotes: 0

Michael Reiche
Michael Reiche

Reputation: 533

This works - but you'll need to hard-code and execute for all the values of abc-123-evaluation.

UPDATE `travel-sample` AS d 
    SET d.`keys`.`abc-123-evaluation`.version = {"major": 2, "minor": 1, "patch": 0}
    WHERE
    d.`keys`.`abc-123-evaluation`.expirationDate > 1669670000000 AND
    d.`keys`.`abc-123-evaluation`.expirationDate < 1713744000000 AND
    d.`keys`.`abc-123-evaluation`.version.major == 1 AND
    d.`keys`.`abc-123-evaluation`.version.minor == 1 AND
    d.`keys`.`abc-123-evaluation`.version.patch == 0
      returning d

The predicate is indeed finding the documents, the issue is that the SET is (apparently) setting the values on the (intermediate?) object v, and not on the document.

When I insert your sample document in my bucket, make the changes below to your query, and run it, it returns 1 mutation. But when I fetch look at the document in couchbase, it is not changed. I assume that n1ql makes a temporary object v and updates that. Which is not helpful.

(1) keys needs to be in back-ticks, it's a reserved word. (2) remove the extraneous '=' in '"batch": = 0', and change name from batch to patch; (3) change the second(lower) date in the query so it is less than the one in the sample (4) remove OBJECT_VALUES (curiously, it seems to work the same with or without OBJECT_VALUES) (5) add , d.updated=1 just to show that the document gets updated. (but v.version does not).

insert into `travel-sample` (key, value) values ("1", {
  "_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"
})

 UPDATE `travel-sample` AS d 
    SET v.version = {"major": 2, "minor": 1, "batch": 0}
    WHERE ANY v in d.`keys`
      SATISFIES v.expirationDate < 1713744000000
         AND v.expirationDate > 1669670000000 
         AND v.version.major == 1
         AND v.version.minor == 1
         AND v.version.patch == 0
      END;


 success just now  355.4ms  1 mutations

Upvotes: 1

Matthew Groves
Matthew Groves

Reputation: 26169

The simplest UPDATE that I could think of to write to update that field is:

UPDATE records._default._default 
USE KEYS "doc1"
SET `keys`.`abc-123-evaluation`.`version` = {"major":9, "minor":9, "patch":9}

Upvotes: 0

Related Questions