Join query with array of objects

SQL++
,
1

I have the following model:

export class Album {
	id: string;
    type: string;
	title: string;
	photoList: AlbumPhoto[] = [];
}

export class AlbumPhoto {
    photo: Photo;
	description: string;
}

export class Photo {
    id: string;
    type: string;
    name: string;
}

Sample data:

Album JSON:  
   {
      "id": "ALBUM_1"
      "type": "ALBUM",
      "title": "Test Album",
      "photoList": [
        {
          "photo": {
            "id": "PHOTO_1"
          },
          "description": "First photo"
        },
        {
          "photo": {
            "id": "PHOTO_2"
          },
          "description": "Second photo"
        }
      ]
    }

Photo JSON:
{
      "id": "PHOTO_1"
      "type": "PHOTO",
      "name": "photo_1.jpg",
}
{
      "id": "PHOTO_2"
      "type": "PHOTO",
      "name": "photo_2.jpg",
}

I can’t figure out how to create a query that includes the photo object data in the photo list:

   {
      "id": "STY_3ch4i"
      "type": "STY",
      "title": "Test Album",
      "photoList": [
        {
          "photo": {
            "id": "PHOTO_1",
            "type": "PHOTO",
            "name": "photo_1.jpg"
          },
          "description": "First photo"
        },
        {
          "photo": {
            "id": "PHOTO_2",
            "type": "PHOTO",
            "name": "photo_2.jpg"
          },
          "description": "Second photo"
        }
      ]
    }

Thanks

2 
SELECT d.*,
         (SELECT  pl.*, photo
           FROM d.photoList AS pl  
           LET photo = (SELECT RAW p FROM mybucket AS p USE KEYS  pl.photo.id)[0] ) AS photoList
FROM mybucket AS d
WHERE d.type = "ALBUM";
3

That worked great, thank you. I would have never figured it out :slightly_smiling_face: