N1Query help with nested array documents

SQL++
1

Hello,
I am new to couchbase and having a document of below structure with nested arrays

{
  "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"
  		}
  ]
}

I want to have to write select query that returns output as shown below. Also notice I would omitted some columns inside the nested array. And IT and Staffing will have its unique code. If IT means then 10 and staffing means 20. I can achieve this by having POJO at java level. But trying to find out whether with query itself is possible.

{
  "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"
  		}
  ]
}

Any help would be appreciated.

Thanks

2

Both documents are same not sure what exactly your question.

SELECT d.*,
        (SELECT e.expId, e.skill  FROM d.experiences AS e WHERE .....) AS experiences
FROM default AS d
WHERE .............;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/selectclause.html

3

Perfect and it worked as I iexpected.