Update on condition

flaviu · January 7, 2021, 4:05pm

I have a simple document

{
value: 5
}

is it possible to make an update of this value just if the updated value is bigger?

somethig like

update value set value = 6 if value <6 ?

Is there a way to update this on multiple documents having a pair of document id and value?

vsr1 · January 7, 2021, 4:38pm

Use WHERE clause, once WHERE clause is true, it always mutated. To avoid any mutation condition should be in WHERE clause.

The following uses IndexScan update every document qualify in the bucket.

UPDATE default d
SET d.`value` = 6
WHERE d.`value` < 6;

If you know the document keys use the following

UPDATE default d USE KEYS [ "key1, "key2",......]
SET d.`value` = 6
WHERE d.`value` < 6;

flaviu · January 7, 2021, 4:46pm

but if I have a different value for each key how can I do it?

vsr1 · January 7, 2021, 4:52pm

Option 1:

        PREPARE p1 FROM 
       UPDATE default d USE KEYS  $key
       SET d.`value` =  $val
       WHERE d.`value` <  $val;
    
       Execute p1 for each key and val by setting $key, $val

Option2: For limited values (say you have 10)

       UPDATE default d USE KEYS  OBJECT_NAMES($obj)
       SET d.`value` =  $obj.[META(d).id]
       WHERE d.`value` <  $obj.[META(d).id];

       $obj   =  {"k01":5, "k02":10, "k07":25}

Option 3: Use SDKs get SUBDOC field with cas and do subdoc mutate when needed

flaviu · January 7, 2021, 5:03pm

ok, let me make this even harder:)

so, I have a document of this structure

DOCUMENT 1
“d”: {
“chart_data”: {
“293778748”: 174,
“311395856”: 446,
“329218549”: 74,
“359917414”: 238,
“372836496”: 418,
“387301602”: 457,
“403858572”: 330,
“406889139”: 61,
“420009108”: 176,
“431946152”: 8,
“457446957”: 349,
“466965151”: 322
}
}

and another document with the key app:key_id_of_chart_data_array

with a document of the format:

DOCUMENT 2
{
“uuid”: “070899ba-5e93-4905-bc1d-375d636f7eff”,
}

DOCUMENT 3
{
value: 3
}
now I need to update the*value" from DOCUMENT3 (document key meta:docuemnt2uuid value) with the value of the document1->chart_data->array_value if the value is bigger than the one from document3 value

Is this something possible to be done?

LATER EDIT:
using this query:

SELECT OBJECT TO_STR(v3.document_key):TO_STR(d.chart.[TO_STR(v3.app_id)]) FOR v3 IN app_details END
FROM tracker AS d USE KEYS "document_id"
LET app_key = (
    SELECT RAW "meta:" || d2.app_uuid || ":" || d.cc AS metakey
    FROM `app-live` AS d2 USE KEYS ARRAY "app:a2t:" || v FOR v IN OBJECT_NAMES(d.chart_apps) END),
app_details = (
    SELECT app_storage.app_app AS app_id,
           `key` AS document_key
    FROM `app-live` AS d3 USE KEYS ARRAY v2 FOR v2 IN app_key END);

I am getting this document:

[
{
“$1”: {
“meta:00105f4b-fb76-4db7-8b19-643ec749191b:us”: “260”,
“meta:001c4696-75cd-40c8-a9d1-f604f612dd23:us”: “286”,
“meta:01275a85-53f5-4fd2-9666-064dfd60abc4:us”: “301”,
}
}
]

now, should I do something like :

UPDATE default d USE KEYS  OBJECT_NAMES($obj)
       SET d.`value` =  $obj.[META(d).id]
       WHERE d.`value` <  $obj.[META(d).id];

is the object structure (I just generated ) correct for the update query you mentioned?

vsr1 · January 7, 2021, 5:51pm

Hi @flaviu,

You are almost close. You can use MERGE, because no UPDATE JOINS.
Change USING subquery that generates id, val instead of OBJECT

MERGE INTO default AS m
USING (SELECT "meta:"|| d3.uuid AS id,  d2.chart_data.[SUBSTR(META(d3).id, 4)] AS val
       FROM (SELECT d1.chart_data FROM default AS d1 USE KEYS "DOCUMENT 1 KEY") AS d2
       JOIN default AS d3 ON KEYS (ARRAY "app:"|| v FOR v IN OBJECT_NAMES(d2.chart_data) END)) AS s
ON KEY s.id
WHEN MATCHED THEN UPDATE SET m.`value` = s.val WHERE m.`value` < s.val;

flaviu · January 7, 2021, 9:05pm

Thank you @vsr1 , it seems to work corectly.

I have two more questions:

if some of the documents don’t have the “value” in the document object, is there any way in which I can create this value if it is missing in the document? if is missing to create at put the “val” if exists to update the “value” with the “val” if “val” si bigger than “value” (something similar to mutateIn)
is it possible instead of updating a document to create another another one with the ket meta:uuid:p:1 and if the document exists to update the value in ti

vsr1 · January 7, 2021, 9:16pm

@flaviu,

In SELECT query where value is projected change that IFMISSINGORNULL(expr, defaultval) AS val
For every match you want create new one. change id in select projection as you mentioned.
Add WHEN NOT MATCHED THEN INSERT INTO …

Lookup MERGE MERGE | Couchbase Docs
Or you can user INSERT INTO … SELECT … Examples 16-18 INSERT | Couchbase Docs

flaviu · January 7, 2021, 10:59pm

ok, the #2 it is working corectly, but unfortunately the #1 I cannot make it work, can you please give me some additionnal hints?

vsr1 · January 7, 2021, 11:07pm

@flaviu,

example:

IFMISSINGORNULL(TO_STR(d.chart.[TO_STR(v3.app_id)]) , 10) AS val

If still need help , Can u share the query, so that it makes easy

flaviu · January 7, 2021, 11:11pm

MERGE INTO `app-live` AS m USING (
    SELECT "meta:"|| d3.app_uuid || ":" || d2.country_code || ":e:" || "1" AS id,
           d2.chart_apps.[TO_STR(d3.apple_id)] AS val,
           d2.chart_timestamp AS time_stamp,
           d2.chart_type AS chart_type,
           d2.platform AS platform,
           d2.genre AS genre
    FROM (
        SELECT d1.country_code AS country_code ,
               d1.chart_apps,
               d1.chart_timestamp,
               d1.chart_type,
               d1.platform,
               d1.genre
        FROM `tracker` AS d1 USE KEYS "app-rank_apple_top-free_gb_ipad_7003_1597590000") AS d2
        JOIN `app-live` AS d3 ON KEYS (ARRAY "app:a2t:"|| v FOR v IN OBJECT_NAMES(d2.chart_apps) END)
    LIMIT 2) AS s ON KEY s.id WHEN MATCHED THEN UPDATE
SET m.app_apple_store.[s.chart_type][s.platform][TO_STR(s.genre)].highest_rank = s.val
WHERE m.app_apple_store.[s.chart_type][s.platform][TO_STR(s.genre)].highest_rank < s.val WHEN NOT MATCHED THEN INSERT ( {"app_apple_store": { s.chart_type:{ s.platform:{ TO_STR(s.genre): { "highest_rank": s.val, "highest_rank_timestamp": s.time_stamp }}}}, "key": s.id, "type": "app-extra", "version": 1, "created_timestamp": TRUNC(NOW_MILLIS()/1000,0) } );

the update doesn’t seem to work. I think I have an error here: ** SET m.app_apple_store.[s.chart_type][s.platform][TO_STR(s.genre)].highest_rank = s.val** or here: ** WHERE m.app_apple_store.[s.chart_type][s.platform][TO_STR(s.genre)].highest_rank < s.val**

but there may be situations in which an existent document with a different “genre” would need to update the same document, so, in this case I need the update to be able to add sub-documents

vsr1 · January 7, 2021, 11:26pm

@flaviu, try this

Due to nested nature and dynamic field you need dot before .[xx] like
m.app_apple_store.[s.chart_type].[s.platform].[s.genre].highest_rank
each one treat as field it must evaluate to string

Do string casting inside vs repeating many times on genre
SET only creates field when parent field present. If parent is MISSING it will not. If needed you should create one using multiple set or OBJECT creation.

MERGE INTO `app-live` AS m USING (
    SELECT "meta:"|| d3.app_uuid || ":" || d2.country_code || ":e:" || "1" AS id,
           d2.chart_apps.[TO_STR(d3.apple_id)] AS val,
           d2.chart_timestamp AS time_stamp,
           d2.chart_type AS chart_type,
           d2.platform AS platform,
           d2.genre AS genre
    FROM (
        SELECT d1.country_code AS country_code ,
               d1.chart_apps,
               d1.chart_timestamp,
               d1.chart_type,
               d1.platform,
               TO_STR(d1.genre) AS genre
        FROM `tracker` AS d1 USE KEYS "app-rank_apple_top-free_gb_ipad_7003_1597590000") AS d2
        JOIN `app-live` AS d3 ON KEYS (ARRAY "app:a2t:"|| v FOR v IN OBJECT_NAMES(d2.chart_apps) END)
    LIMIT 2) AS s ON KEY s.id
WHEN MATCHED THEN
     UPDATE SET m.app_apple_store.[s.chart_type].[s.platform].[s.genre] =
             OBJECT_CONCAT(IFMISSING(m.app_apple_store.[s.chart_type].[s.platform].[s.genre],{}),{"highest_rank" : s.val})
      WHERE m.app_apple_store.[s.chart_type].[s.platform].[s.genre].highest_rank < s.val  OR
            m.app_apple_store.[s.chart_type].[s.platform].[s.genre] IS MISSING
WHEN NOT MATCHED THEN
      INSERT ( {"app_apple_store": { s.chart_type:{ s.platform:{ s.genre: { "highest_rank": s.val, "highest_rank_timestamp": s.time_stamp }}}}, "key": s.id, "type": "app-extra", "version": 1, "created_timestamp": TRUNC(NOW_MILLIS()/1000,0) } );

flaviu · January 7, 2021, 11:48pm

so to create an entire path I need to do something like set a, set a.b , set a.b.c ?

does this work in the same query

I tried now just with just one SET m.app_apple_store.[s.chart_type] = 123 and it is not creating it

vsr1 · January 7, 2021, 11:53pm

to work SET a.b.c a, b must be object

{“a”:{“b”:{ }}}

flaviu · January 7, 2021, 11:55pm

let me be more specific:
this it the document

{
  "app_apple_store": {
    "top-free": {
      "ipad": {
        "6014": {
          "highest_rank": 485,
          "highest_rank_timestamp": 1597590000
        }
      }
    }
  },
  "created_timestamp": 1610063622,
  "key": "meta:44d24669-c51f-4aa0-a358-01d5662c1676:gb:e:1",
  "sub_type": "app-extra",
  "type": "tracker-app",
  "version": 1
}

after the update should be:

{
  "app_apple_store": {
    "top-free": {
      "ipad": {
        "6014": {
          "highest_rank": 485,
          "highest_rank_timestamp": 1597590000
        },
        "7003":{
          "highest_rank": 485,
          "highest_rank_timestamp": 1597590000
        },
      }
    }
  },
  "created_timestamp": 1610063622,
  "key": "meta:44d24669-c51f-4aa0-a358-01d5662c1676:gb:e:1",
  "sub_type": "app-extra",
  "type": "tracker-app",
  "version": 1
}

but could also be updated to :

{
  "app_apple_store": {
    "top-free": {
      "ipad": {
        "6014": {
          "highest_rank": 485,
          "highest_rank_timestamp": 1597590000
        },
        "7003":{
          "highest_rank": 54,
          "highest_rank_timestamp": 1597590000
        }},
    "top-paid": {
      "iphone": {
        "6014": {
          "highest_rank": 123,
          "highest_rank_timestamp": 1597590000
        },
        "7003":{
          "highest_rank": 456,
          "highest_rank_timestamp": 1597590000
        }
      }
      }
    }
  },
  "created_timestamp": 1610063622,
  "key": "meta:44d24669-c51f-4aa0-a358-01d5662c1676:gb:e:1",
  "sub_type": "app-extra",
  "type": "tracker-app",
  "version": 1
}

vsr1 · January 8, 2021, 1:09am

@flaviu,

Replace UPDATE … WHERE

UPDATE SET m.app_apple_store = IFMISSING(m.app_apple_store, {}),
             m.app_apple_store.[s.chart_type] = IFMISSING(m.app_apple_store.[s.chart_type], {}),
             m.app_apple_store.[s.chart_type].[s.platform] = IFMISSING(m.app_apple_store.[s.chart_type].[s.platform], {}),
             m.app_apple_store.[s.chart_type].[s.platform].[s.genre] = IFMISSING(m.app_apple_store.[s.chart_type].[s.platform].[s.genre].[s.genre], {}),
             m.app_apple_store.[s.chart_type].[s.platform].[s.genre].highest_rank = CASE WHEN m.app_apple_store.[s.chart_type].[s.platform].[s.genre].highest_rank < s.val THEN s.val ELSE m.app_apple_store.[s.chart_type].[s.platform].[s.genre].highest_rank < s.val END

flaviu · January 8, 2021, 2:04am

yeey, seems to be working

I have another question regarding this:

SELECT “test” || d2.genre as genre

return

{
  genre: null
}

but

SELECT d2.genre as genre

returns:

{
  genre: 6016
}

is this a bug? or I am just tired or stupid?

vsr1 · January 8, 2021, 4:38am

@flaviu,

it is number. You can concat only strings. JSON 123 is different from “123”, type is on value not on fieldname. So no implicit cast. If needed one must do explicit cast.

“test” || TO_STR(d2.genre) AS genre

flaviu · January 8, 2021, 3:13pm

thank you, you are going a great job!

Topic		Replies	Views
Update multiple documents in a single query SQL++	1	3870	November 8, 2017
Trying to UPDATE a doc, need help, New to N1QL SQL++	3	508	June 18, 2022
All documents mutated when only a single document modified Couchbase Server query , n1ql	6	424	May 23, 2023
UPSERT only a single document SQL++	6	4620	September 1, 2016
Update / Delete queries with where clause SQL++	12	1337	February 20, 2019

Update on condition

Related topics