Help building N1ql Query

Hi hopefully someone will help me build a n1ql query I do not even know if it is possible to build it.

I have a Bucket that contains documents that have the following structure:
Session_Bucket
{
“sessionId”: “005c6f8e-1826-403b-9298-f3e7e4c5242c”,
“visitorId”: “adc65828-3b77-4dd7-b002-16f3f3c94fe2”,
“companyId”: “963258741”,
“aryEvents”: [
{eventTime: 1446176283628,
eventId: c4b2f83f-6b12-4173-be53-539f73cf1489},
{eventTime: 1446176283628,
eventId: c4b2f83f-6b12-4173-be53-539f73c1563}
]
}

I have another bucket that contains documents structured like this;
Events_Bucket
{
“eventId”: “c4b2f83f-6b12-4173-be53-539f73cf1489”,
“visitorId”: “d1ad0141-36e2-4116-87d4-64d2b8ced5f2”,
“companyId”: 123456,
“eventType”: “soft_bounce”,
“eventTime”: 1443801047,
“mandrillMessageId”: “b3a4ebd0ae354d28b86d185dbfbc288b”,
“emailAddress”: "example.webhook@mandrillapp.com"
}

{
“eventId”: “c4b2f83f-6b12-4173-be53-539f73cf1563”,
“visitorId”: “d1ad0141-36e2-4116-87d4-64d2b8c1563”,
“companyId”: 123456,
“eventType”: “soft_bounce”,
“eventTime”: 1443801047,
“mandrillMessageId”: “b3a4ebd0ae354d28b86d185dbfbc288b”,
“emailAddress”: "example.webhook@mandrillapp.com"
}

I would like to create a query that will give a result that looks like the following.

{
“sessionId”: “005c6f8e-1826-403b-9298-f3e7e4c5242c”,
“visitorId”: “adc65828-3b77-4dd7-b002-16f3f3c94fe2”,
“companyId”: “963258741”,
“aryEvents”: [
{
** “eventId”: “c4b2f83f-6b12-4173-be53-539f73cf1489”,**
** “visitorId”: “d1ad0141-36e2-4116-87d4-64d2b8ced5f2”,**
** “companyId”: 123456,**
** “eventType”: “soft_bounce”,**
** “eventTime”: 1443801047,**
** “mandrillMessageId”: “b3a4ebd0ae354d28b86d185dbfbc288b”,**
** “emailAddress”: "example.webhook@mandrillapp.com"**
** }, **
** {**
** “eventId”: “c4b2f83f-6b12-4173-be53-539f73cf1563”,**
** “visitorId”: “d1ad0141-36e2-4116-87d4-64d2b8c1563”,**
** “companyId”: 123456,**
** “eventType”: “soft_bounce”,**
** “eventTime”: 1443801047,**
** “mandrillMessageId”: “b3a4ebd0ae354d28b86d185dbfbc288b”,**
** “emailAddress”: "example.webhook@mandrillapp.com"**
** }**
** ]**
}

I pretty much need to nest the events in the array of event by its eventId which is always the same.

Hi, try something like this:

SELECT *
FROM Session_Bucket s
NEST Events_Bucket e ON KEYS s.aryEvents[*].eventId;
2 Likes

Thank you. Your query returned what I wanted