SET an array property with a filter array property

SQL++
1

Hi,

I have the following documents on my db:

[
{
	"id": "item00",
	"sku": "item00SKU",
	"type": "item",
	"deleted": true,
	"kits": []
},
{
	"id": "item01",
	"sku": "item01SKU",
	"type": "item",
	"deleted": false,
	"kits": []
},
{
	"id": "item01",
	"sku": "item01SKU",
	"type": "item",
	"deleted": false,
	"kits": []
},
{
	"id": "item03",
	"sku": "item03SKU",
	"type": "item",
	"deleted": false,
	"kits": [
		{
		  "id": "item00",
		  "kitOp": "op00"
		},
		{
		  "id": "item01",
		  "kitOp": "op00"
		},
	]
}

]

After updating my item00 and item03, I need to update the other items that could have that item asociated as a kitt; so I have this N1QL:

UPDATE Bucket_P t
SET t.kits = t kits filtered without item00"
WHERE t.type=ā€˜itemā€™ AND t.deleted=false

yes I donot have the idea to clear to translated this to a n1ql, but the result should be for example for item03:

{
ā€œidā€: ā€œitem03ā€,
ā€œskuā€: ā€œitem03SKUā€,
ā€œtypeā€: ā€œitemā€,
ā€œdeletedā€: false,
ā€œkitsā€: [
{
ā€œidā€: ā€œitem01ā€,
ā€œkitOpā€: ā€œop00ā€
},
]
}

2 
CREATE INDEX ix1 ON Bucket_P(deleted, DISTINCT ARRAY  v.id FOR v IN kits END) WHERE type = "item";
UPDATE Bucket_P t
SET t.kits =  ARRAY v1 FOR v1 IN t.items WHEN v1.id != "item00" END
WHERE t.type= "item" AND t.deleted=false AND ANY v IN t.kits SATISFIES v.id = "item00" END;

You donā€™t want to mutate document (writing same document again) if it doesnā€™t have ā€œitem00ā€, so add ANY ā€¦ to WHERE clause.

1 Like
3

In fact like I said there could be many items to be removed from kit items; so I changed the query to:

UPDATE Bucket_P t
SET t.kits = ARRAY v1 FOR v1 IN t.kits WHEN v1.id NOT IN ['item00', 'item01'] END, t.deleted = ARRAY_LENGTH(ARRAY v1 FOR v1 IN t.kits WHEN v1.id NOT IN ['item00', 'item01'] END) = 0
WHERE t.type = 'item' AND t.deleted = false AND ANY v IN t.kits SATISFIES v.id IN ['item00', 'item01'] END;

It seems working fine, there are two doubts left; on this case if the kit array is empty after the filtering the deleted field should be changed to true; so is like Iā€™m doing twice this filtering on my approach.

About the other doubt could u please explain why do I need to create an Index? also this index I think it should be created only once right?

4

If you need deleted set, you must filter twice. You will not able to access the new value from previous SET clause.
Couchbase needs index. If you donā€™t have matching secondary index or primary index query will return error. Index creation is only once.

5

I tried the query without index and it seems working fine; I already have a primary index.

I mean on this specific case why does the index is needed? could you please, explain on more detail on this specific case?

6

If specific index is there query runs fast. Primary index can be slow (Primary Uses for Couchbase Primary Index. | The Couchbase Blog).

Take example: Your bucket has 100K documents, only 10 documents has kits word you are looking.
Primary index will qualify all 100K documents, Fetch 100K documents and apply filter and discard most except 10.
If you have specific secondary index, Index can give less items, in this case 10, Fetch 10 documents and apply filter and discard nothing.

1 Like