Is it possible to delete a specific portion of document in Couchbase using N1QL?

SQL++
1

Please let me know if its possible to delete an element from the children array based on the Id from the below sample document .
i.e. I need to delete all the details of Id=100( delete the entire element in the array)

{
	"results": [{
		"tutorial": {
			"type": "contact",
			"title": "Mr.",
			"fname": "Ian",
			"lname": "Taylor",
			"age": 56,
			"email": "ian@gmail.com",
			"children": [{
					"Id": "100",
					"Details": [{
						"fname": "Abama",
						"age": 17,
						"gender": "F"
					}]

				},
				{
					"Id": "101",
					"Details": [{
						"fname": "Alex",
						"age": 17,
						"gender": "M"
					}]

				}
			],
			"hobbies": [
				"golf",
				"surfing"
			],
			"relation": "cousin"
		}
	}]
}
2

Use UPDATE FOR syntax

UPDATE default AS d
UNSET  d.age
   FOR dt IN c.Details FOR c IN d.children  WHEN c.Id = "100" AND dt.fname = "Alex" END
WHERE d.type = "contact" AND d.email = "ian@gmail.com";
3

“Tutorial” is the bucket name .
I am getting this error "“Keyspace not found in CB datastore keyspace default - cause: No bucket named default”. Could you please help?

4

Removes age field in Details that matches fname “Alex”, underneath children id is “100” of document type “contact” and email “ian@gmail.com”

UPDATE tutorial AS d
UNSET  dt.age
   FOR dt IN c.Details FOR c IN d.children  WHEN c.Id = "100" AND dt.fname = "Alex" END
WHERE d.type = "contact" AND d.email = "ian@gmail.com";

Removes Details underneath children id is “100” of document type “contact” and email “ian@gmail.com”

   UPDATE tutorial AS d 
    UNSET  c.Details
       FOR c IN d.children  WHEN c.Id = "100" END
    WHERE d.type = "contact" AND d.email = "ian@gmail.com";

Removes Details underneath children id is “100” of document type “contact” and email “ian@gmail.com” of document id “ian”

UPDATE tutorial AS d  USE KEYS "ian"
    UNSET  c.Details
       FOR c IN d.children  WHEN c.Id = "100" END
    WHERE d.type = "contact" AND d.email = "ian@gmail.com";

Removes children id is “100” of document type “contact” and email “ian@gmail.com” of document id “ian”

 UPDATE tutorial AS d  USE KEYS "ian"
  SET d.children = ARRAY c  FOR c IN d.children  WHEN c.Id != "100" END
  WHERE d.type = "contact" AND d.email = "ian@gmail.com";
1 Like
5

Could you please give a brief explanation what each of these 4 queries achieve ? I am beginner in N1QL