How to select data in array and how to delete using the data in the array

SQL++
,
1

Hello i’ve go the following data:

“couchbase”:{
“_class”: “EXAMPLE”,
“documentId”: “AAAA”,
“ftes”: [ {“fteId”: “fteExample1” }, { “fteId”: “fteExample2” } ]
}
}

the thing is i want to get the info seraching by the _class and a specific fteID but i’m not sure of how to do it i’ve trying with :
SELECT * FROM couchbase WHERE
_class = “EXAMPLE” and ftes.fteId=“fteExample2”

I’ve also tried with:
SELECT * FROM couchbase WHERE
_class = “EXAMPLE” and ftes[fteId=“fteExample2”]

Also i would like to delete using the fteID for all the situations where there’s the word “Example” so i was thinking of using like %Example% but i don’t know how to search the data in the array so i don’t know how to delete it…

Some help would be quite appreciated, thanks

2

Assuming data:

insert into default (key,value) values("o1",{ "_class": "EXAMPLE", "documentId": "AAAA", "ftes": [ {"fteId": "fteExample1" }, { "fteId": "fteExample2" } ] });

To filter on array elements UNNEST can help, e.g.

 select d.* from default d unnest `ftes` as ftes where d.`_class` = "EXAMPLE" and ftes.`fteId` = "fteExample2";

Or without UNNEST:

select d.* from default d where d.`_class` = "EXAMPLE" and any f in d.`ftes` satisfies f.`fteId` = "fteExample2" end;

This:

delete from default d where any f in d.`ftes` satisfies f.`fteId` like "%Example%" end;

would delete any document that has an ftes element with an fteId containing the pattern “%Example%”.

(An appropriate index would be needed too, of course, to support any of these).

HTH.

2 Likes