CODEWITHSUNDEEP
couchbasesql++nosql
mpmp
mpmp

Reputation: 2459

Couchbase N1QL: How to aggregate and join from the same bucket with an array of keys

Assume:

Album Document:

{ 
    "albumName": "my album",
    "photos": [photo1Id, photo2Id, photo3Id]
}

Photo document: 

{
    "photoName": "photoName1.img";
    "photoId": "photo1Id";
    "otherData": "Some other data"
}

where the key of the Photo Document is the same as the photoId.

I want to make a SELECT statement that will have this output: 

{ 
    "albumName": "my album",
    "photos": [{
        "photoName": "photoName1.img";
        "photoId": "photo1Id";
        "otherData": "Some other data"
    },{
        "photoName": "photoName2.img";
        "photoId": "photo2Id";
        "otherData": "Some other data"
    },{
        "photoName": "photoName3.img";
        "photoId": "photo3Id";
        "otherData": "Some other data"
    }]
}

Is this possible or do I have to create multiple selects?

Upvotes: 3

Views: 1000

Answers (1)

Troopers
Troopers

Reputation: 5452

Use the NEST clause to output the photos array in the result document.

Example : (I use type to filter on your document type but it's perhaps not your case)

SELECT * FROM bucket albums
NEST bucket photos ON KEYS albums.photos
WHERE albums.type='Album'

Upvotes: 4

Related Questions