UPDATE query was working in 4.1, but not working in 4.5

SQL++
1

Hi all,

I have an update query that was working in Couchbase 4.1 but does not seem to work when I run the same query in Couchbase 4.5.

The document is

{
  "createdDate": 1473351448505,
  "docType": "userRelation",
  "groupMemberships": [],
  "id": "9ad51940-cad2-46b1-b708-799e13b5541c",
  "lastModifiedDate": 1473352068338,
  "relationKeys": [
    "app-user-id_damian@theexperienceengine.com",
    "username_damian@theexperienceengine.com",
    "chat-id_oceanId-3a83e533-7911-42e5-8e51-960afafcd892",
    "user-id_oceanId-3a83e533-7911-42e5-8e51-960afafcd892",
    "app-user-id_damian@theexperienceengine.com-1-1"
  ]
}

The query is:

UPDATE write SET e = REPLACE(e, 'user-id', 'uiid') FOR e in relationKeys WHEN e LIKE 'user-id%' END WHERE docType = 'userRelation' and id = '9ad51940-cad2-46b1-b708-799e13b5541c';

When I run this query in 4.1 the query updates the user-id_oceanId-3a83e533-7911-42e5-8e51-960afafcd892 value in the relationKeys array to uiid_oceanId-3a83e533-7911-42e5-8e51-960afafcd892.

The update does not work in 4.5.

Any help would be greatly appreciated.

Regards,
Damian.

2

What is happening in 4.5? Is there an error or warning message? Can you post the exact response here.

3

Geraldss,

I will post the exact response tomorrow as I do not have access to the couchbase instance at the moment.

The query does not produce an error and shows a mutation count similar to the result when the query is run on 4.1.

Regards,
Damian.

4

Geralds,

The result when I run the query in 4.5 is:

{
    "requestID": "479d61ab-585e-442c-88a8-e40cdba10a03",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "96.890411ms",
        "executionTime": "96.795218ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 1
    }
}

Regards,
Damian.

5

It says success. What makes you think it did not work?

6

Geraldss,

When you do a select on the document the relationKeys with value starting with user-id does not get updated to uiid.

Regards,
Damian.

7

Ok, got it. Yes, there was a change in 4.5. Try the following.

UPDATE write
SET relationKeys = ARRAY REPLACE(e, 'user-id', 'uiid') FOR e in relationKeys WHEN e LIKE 'user-id%' END
WHERE docType = 'userRelation' AND id = '9ad51940-cad2-46b1-b708-799e13b5541c';
8

Geraldss,

Thanks for the reply. Unfortunately the query did not work correctly. It updated the item in the relationKeys array that started with user-id to uiid correctly but it removed all other items in the relationKeys array. For the update query to work the other items in the relationKeys array should not be removed. The response from the select query on the document after the update query was run was as follows:

[
  {
    "write": {
      "createdDate": 1473351448505,
      "docType": "userRelation",
      "groupMemberships": [],
      "id": "9ad51940-cad2-46b1-b708-799e13b5541c",
      "lastModifiedDate": 1473352068338,
      "relationKeys": [
        "uiid_oceanId-3a83e533-7911-42e5-8e51-960afafcd892"
      ]
    }
  }
]

Regards,
Damian.

9

My bad. How about the following.

UPDATE write
SET relationKeys = ARRAY CASE WHEN e LIKE 'user-id%' THEN REPLACE(e, 'user-id', 'uiid') ELSE e END FOR e in relationKeys END
WHERE docType = 'userRelation' AND id = '9ad51940-cad2-46b1-b708-799e13b5541c';
10

Geraldss,

When I ran the query I got a syntax error. I figured out that a missing THEN was causing the syntax error.

When I ran the query:

UPDATE write
SET relationKeys = ARRAY CASE WHEN e LIKE 'user-id%' THEN REPLACE(e, 'user-id', 'uiid') ELSE e END FOR e in relationKeys END
WHERE docType = 'userRelation' AND id = '9ad51940-cad2-46b1-b708-799e13b5541c';

The query worked correctly.

Thanks for all your patience and help, for me, over the last few days.

Regards,
Damian.

2 Likes