Above I provided one sample document that we have in Couchbase. Like this several document stored in our couch. For me I need to
retreive only the experiences that has catCode = “IT” AND parent document (meta().id = 'CANDIDATE-123"
I am able to achieve using UNNEST but it lists matching records as individual array which I don’t want. I want output like shown below
"experiences" :[
{
"expId": 111,
"skill": "NET",
"catCode" : "IT",
"startDate": "12-01-2010",
"endDate" : "12-12-2011"
}
{
"expId": 113,
"skill": "Word",
"catCode" : "Staffing",
"startDate": "12-01-2017",
"endDate" : "12-12-2018"
}
]
I need some thing like this
select d.experiences as experiences from default d where meta().id = 'CANDIDATE-123'
AND experiences.catCode = 'IT'
I tried with ARAY ANY/EVERY (Satisfies) but I am not able to retrieve the above results. If anyone has any solution please let me know.
Excellent. It worked.I will check for this syntax and see how it work. Just another question. As of now I have set only default index at the bucket level. Do I have to set any additional indexes? If so please do let me know. Also is there is a way to achieve the solution you provided using bucket lookupIn API via code.
Thank you @vsr1. I will check the document that you sent. One more quick question see the below documents
“CANDIDATE-123”
{
“Id”: 123,
“name” : “abc”
"type: : “FTE”
“experiences” :[
{
“expId”: 111,
“skill”: “NET”,
“catCode” : “IT”,
“deptCode”: 1,
“startDate”: “12-01-2010”,
“endDate” : “12-12-2011”
Using the query that you provided earlier can I get the Name field by joining id with deptCode? As of now I have plans to have the name field at candidate document level itself. But if join is possible then considering the remove deptName from candidate level.
SELECT ARRAY {"id":v.deptCode, "Name": "DEPT-"||TOSTRING(v.deptCode)} FOR v IN d.experiences END AS departments
FROM default AS d USE KEYS "CANDIDATE-123";
SELECT d.*,
(SELECT e.*,(SELECT RAW dep.Name FROM default AS dep USE KEYS "DEPT-"|| TO_STRING(e.deptCode)[0]
FROM d.experiences AS e ) AS experiences
FROM default AS d USE KEYS "CANDIDATE-123";