Search code examples
couchbasesql++

Filter documents using n1ql

I have two types of documents with a relation one to many and I need to get only one document basing on the latest date value. Here it is an example:

Document Type (Singer):

{
  "id":"223-34fd-35325-dsf-343f",
  "resourceType":"Singer",
  "name":"Paul"
}

Document Type (Album1):

{
   "id":"456-d435-43g346-43-436tf-345g",
   "name":"Amazing night",
   "author":"223-34fd-35325-dsf-343f",
   "creationDate": "2017-05-12T07:57:51.458Z"
}

Document Type (Album2):

{
   "id":"878-ffe6-43gs56-5hn7-ffgdd-345hd",
   "name":"Be quiet",
   "author":"223-34fd-35325-dsf-343f",
   "creationDate": "2017-05-11T13:43:05.580Z"
}

Well, I need to get a list of Singers with only one album (which was the last added, basing on creationDate). In my case I have to get singer "Paul" and the album "Amazing night". Is this either possible?

Solution

  • If You need Album document use the following query.

    SELECT singer, MAX([a.creationDate, a])[1] latestAlbum
FROM default a
WHERE a.author IS NOT MISSING
GROUP by a.author
LETTING singer = (SELECT RAW s.name FROM default s USE KEYS a.author)[0];