Return nested document result matches sub document match and parent documentId match

"CANDIDATE-123"
{
  "Id": 123,
  "name" : "abc"
  "type: : "FTE"
  "experiences" :[
  		{
  			"expId": 111,
  			"skill": "NET",
  			"catCode" : "IT",
  			"startDate": "12-01-2010",
  			"endDate" : "12-12-2011"
  			
  		},
  		{
  			"expId": 112,
  			"skill": "Java",
  			"catCode" : "IT",
  			"startDate": "12-01-2013",
  			"endDate" : "12-12-2015"
  			
  		},
  		{
  			"expId": 113,
  			"skill": "Word",
  			"catCode" : "Staffing",
  			 "startDate": "12-01-2017",
  			"endDate" : "12-12-2018"
  		}
  ]
}

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. 

Thanks

SELECT  ARRAY v FOR v IN d.experiences WHEN v.catCode = "IT" END  AS  experiences  
FROM default AS d USE KEYS "CANDIDATE-123";
1 Like

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.

Thanks

If you know keys, you don’t need indexes.


https://index-advisor.couchbase.com/
1 Like

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”

	},
	{
		"expId": 112,
		"skill": "Java",
		"catCode" : "IT",
		"deptCode": 1,
		"startDate": "12-01-2013",
		"endDate" : "12-12-2015"
		
	},
	{
		"expId": 113,
		"skill": "Word",
		"deptCode": 2,
		"catCode" : "Staffing",
		 "startDate": "12-01-2017",
		"endDate" : "12-12-2018"
	}

]
}

“DEPT-1”
{
“id”:1,
“Name”: “DEPT1”
}

“DEPT-2”
{
“id”:2,
“Name”: “DEPT2”
}

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";

Sorry my bad I should asked more clearly. Actually my requirement is

I need the output like this

“experiences” :[
{
“expId”: 111,
“skill”: “NET”,
“catCode” : “IT”,
“deptName” : “YYYYY”, – This deptname should be populated the DEPT document by joining
“startDate”: “12-01-2010”,
“endDate” : “12-12-2011”

	}
	{
		"expId": 113,
		"skill": "Word",
		"deptName" : "XXXXX",
		"catCode" : "Staffing",
		 "startDate": "12-01-2017",
		"endDate" : "12-12-2018"
	}

]

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";