Search code examples
subquerycouchbasesql++

N1QL value NOT IN subquery which returns an array

I am trying to write a N1QL query which returns all records of whose id is not in an array. This array is found in another document.

My Playlist document example: {"id":1,"name":"playlist1","tracksId":[1,2]}

My Track document example: {"id":1,"title":"songtitle"}

To be specific, i want my query to return all tracks of which id is not found in the tracksId array of my Playlist document.

Here is my best attempt at solving this problem using a subquery:

SELECT * FROM Track
WHERE id NOT IN (SELECT tracksId FROM Playlist WHERE id = 1)

This unfortunately doesn't work since my subquery returns an array inside a field. Does anyone know a way to solve this?

Solution

  • By default N1QL query gives ARRAY of JOSN objects. The right of side of IN or NOT IN must be ARRAY of left side data type.

    Subquery projects one element use RAW avoid the object like below.

    SELECT * FROM Track
WHERE id NOT IN (SELECT RAW tracksId FROM Playlist WHERE id = 1)

    In your case tracksId is another ARRAY. You can flatten the nested ARRAY or UNNEST and create one level ARRAY

    SELECT * FROM Track
WHERE id NOT IN ARRAY_FLATTEN((SELECT RAW tracksId FROM Playlist WHERE id = 1),1);

    OR

     SELECT * FROM Track
 WHERE id NOT IN (SELECT RAW track FROM Playlist AS p UNNEST p.tracksId As track WHERE p.id = 1);