Update query to specific nested document

[kev]

Here is a sample document I am trying to update:

{
  "items": [
    {
      "id": 123,
      "values": [
        {
          "1": [
            "String"
          ],
          "2": [
            "String 2"
          ]
        },
        {
          "1": [
            "String"
          ],
          "2": [
            "String 2"
          ]
        }
      ]
    },
    {
      "id": 456,
      "values": [
        {
          "1": [
            10
          ]
        }
      ]
    },
    {
      "id": 789,
      "values": [
        {
          "1": [
            "String 1",
            "String 2",
            "String 3"
          ],
          "2": [
            "String 1",
            "String 2",
            "String 3"
          ]
        }
      ]
    }
  ]
}

I want to be able to use N1QL to update a specific “values” set (example “1”:[10] ) based on a specific id.

I know how to query this using SELECT, but I am having issues converting that into a proper update statement.

[vsr1]

INSERT INTO default VALUES("k100",{ "items": [ { "id": 123, "values": [ { "1": [ "String" ], "2": [ "String 2" ] }, { "1": [ "String" ], "2": [ "String 2" ] } ] }, { "id": 456, "values": [ { "1": [ 10 ] } ] }, { "id": 789, "values": [ { "1": [ "String 1", "String 2", "String 3" ], "2": [ "String 1", "String 2", "String 3" ] } ] } ] });

CREATE INDEX ix1 ON default (DISTINCT ARRAY item.id FOR item IN items END);
UPDATE default AS d
        SET item.`values` =  [ { "1": [ 20 ] },{ "2": [ 21 ] } ] FOR item IN d.items WHEN item.id = 456 END
        WHERE ANY item IN d.items SATISFIES item.id = 456 END;

[kev]

Seems easy once you actually see the solution. This seems to have worked. Thanks!

My update query uses “use keys” so I assume I can drop the WHERE clause and skip creating the INDEX in this case.

[vsr1]

If USE KEYS you can skip the index. But still keep the WHERE clause so that if the document doesn’t qualify no update is done. If you remove WHERE clause it will update same document again if not qualified.

Iterate objects inside array

I have array: { user:‘roy’, images:[ {img:‘imgUrl1’,date=“today”} {img:‘imgUrl2’,date=“tommorrow”} ] } I want to match the right item in the array when if found. For example: user send url: imgUrl1 I need to make query to find this element inside the array. I tried: select * from db where every item in images satisfies item.image=‘imgUrl1’; But nothing found

[kev]

That is actually very useful information to know, as that behavior is not self-evident. So if the USE KEYS clause fails to find a document, the update query continues and updates all documents unless a “backup” WHERE clause is provided.

[kev]

But my follow-up to that is: If the USE KEYS clause is satisfied, does the WHERE clause still get evaluated?

[vsr1]

NO.

1. USE KEYS can have multiple values i.e USE KEYS [“doc1”,“doc2”,“doc3”]
2. If there is only “doc1”,“doc3” present (If none of is present update is done. no further processing)
3. WHERE clause condition is applied on “doc1”,“doc3”, If “doc3” has item.id = 456 and not "doc1"
   WHERE clause eliminates “doc1” update only qualifies “doc3”
4. SET clause will modify the “doc3”, and update new “doc3”
5. If you don’t have WHERE clause “doc1” is updated as it is because SET … FOR… WHEN… END will leave the document “doc1” as it is. This is what you want to avoid unnecessary update.

[kev]

Understood! Very good internals info to know about. Thanks for the explanation.

Back to my original post, your solution has worked. Im still trying to wrap my head around nested FOR looping to target specific items in nested documents.

So, for example, if I wanted to modify only the value of the “1” key, and leave the “2” key alone, how would I go about doing that using the above structure?

{
  "items": [
    {
      "id": 123,
      "values": [
        {
          "1": [
            "String"
          ],
          "2": [
            "String 2"
          ]
        }
      ]
    }
  ]
}

[vsr1]

   UPDATE default AS d  USE KEYS ["k100"]
              SET val.`1` =  [ "New String" ]
                     FOR val IN item.`values`  
                           FOR item IN d.items
                     WHEN item.id = 123 AND val.`1` == ["String"]  END
        WHERE ANY item IN d.items SATISFIES item.id = 123 END;

[kev]

I hate to re-open this topic, but I am being blocked again when going an extra layer down in my update query.

Here is the document I am trying to modify:

{
	"level_1": [{
		"code": "123",
		"level_2": [{
			"id": 1,
			"value_1": 456,
			"value_2": 789
		}]
	}]
}

I want to completely replace the specific sub-document in level_2.

This is my query:

UPDATE default USE KEYS 'blah' SET 
y = {"id":1, "value_1":101, "value_2":1010} 
 FOR y in x.level_2 FOR x IN level_1
 WHEN  x.`code` = "123" and y.id = 1 END 
WHERE ...

[vsr1]

@kev

UPDATE default USE KEYS ‘blah’ SET
y = {“id”:1, “value_1”:101, “value_2”:1010}
FOR y in x.level_2 FOR x IN level_1
WHEN x.code = “123” and y.id = 1 END
WHERE …

You need to replace in parent to keep the value attached to document not just y otherwise it may add root of the document.

UPDATE default USE KEYS 'blah' SET 
x.level_2[ARRAY_POS(x.level_2,y)] = {"id":1, "value_1":101, "value_2":1010} 
 FOR y in x.level_2 FOR x IN level_1
 WHEN  x.`code` = "123" and y.id = 1 END 
WHERE ...

OR

 UPDATE default USE KEYS 'blah' SET 
    x.level_2  =  ARRAY CASE WHEN y.id = 1 THEN {"id":1, "value_1":101, "value_2":1010} ELSE y END FOR y IN x.level_2 END 
    FOR x IN level_1
     WHEN  x.`code` = "123" END 
    WHERE ...

[kev]

I tried your first option before my initial post, as it seemed logical, but for some reason it does not work

[kev]

It looks like only option 2 works. Option 1, as logical as it seems, does not. I wonder if this is expected, or a bug.

[vsr1]

Option 1 works only thing it will not handle is duplicates because ARRAY_POS() returns first matched value.
I have tried this and it worked fine.

INSERT INTO default VALUES ("k0001", { "level_1": [{ "code": "123", "level_2": [{ "id": 1, "value_1": 456, "value_2": 789 }, { "id": 5, "value_1": 1456, "value_2": 1789 }] }] });

UPDATE default USE KEYS 'k0001' SET
      x.level_2[ARRAY_POS(x.level_2,y)] = {"id":1, "value_1":101, "value_2":1010}
             FOR y in x.level_2 FOR x IN level_1
                   WHEN  x.`code` = "123" and y.id = 1 END;

[kev]

Very weird that it does not work in my example, even though its the same structure as in my example. I suppose Ill dig more and see why. But even if I copy your example verbatim, it does not update the document.

Thanks anyways!

[vsr1]

What is version of couchbase you are using?

[kev]

I am using CE 4.5.1-2844

[vsr1]

I have tried with couchbase-server-community-4.5.1-2844.x86_64 and it works fine

 select * from default USE KEYS "k0001";
{
    "requestID": "40958db3-dc8a-46a6-b7fb-16ff2df9dd91",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "default": {
                "level_1": [
                    {
                        "code": "123",
                        "level_2": [
                            {
                                "id": 1,
                                "value_1": 101,
                                "value_2": 1010
                            },
                            {
                                "id": 5,
                                "value_1": 1456,
                                "value_2": 1789
                            }
                        ]
                    }
                ]
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.22864ms",
        "executionTime": "2.19631ms",
        "resultCount": 1,
        "resultSize": 639
    }
}