Aggregate results with join

N1QL
,
1

I have the data like shown below,

collection 1: files

{
   "id":"1234",
   "name":"file1.txt",
   "version":"3.0"
}

collection 2: versions (each json below is separate document)

{
   "id":"xyz",
   "fileId":"1234",
   "version":"2.0"
},
{
   "id":"abc",
   "fileId":"1234",
   "version":"1.0"
}

Can you help with query where i need all the versions of fileId 1234 along with its filename.
Expected result:

[
   {
      "name":"file1.txt",
      "version":"3.0"
   },
   {
      "name":"file1.txt",
      "version":"2.0"
   },
   {
      "name":"file1.txt",
      "version":"1.0"
   }
]

Query i tried (which doesn’t give version 3.0),

SELECT f.name, v.version FROM files f JOIN version v ON f.id=v.fileId 
WHERE f.id="1234"

Appreciate your help!

2

Try:

SELECT f.name, f.version FROM files f WHERE f.id = "1234"
UNION ALL
SELECT f.name, v.version FROM files f JOIN versions v ON f.id = v.fileId and f.version != v.version WHERE f.id = "1234";
2 Likes