Couchbase N1QL Subquery join issue

Question

SELECT (SELECT RAW subcomments 
FROM comments.subcomments AS subcomments
JOIN users ON subcomments.userId = users.id 
ORDER BY subcomments.createdDate DESC) as subcomments,
ARRAY_COUNT(comments.subcomments) as subcommentCount 
FROM comments where comments.id = "f4f0f481-f0d6-4edf-8a0c-494651becdf2" and projectId= "1"

When I tried to run this query, I received these errors:

[
  {
    "code": 5370,
    "msg": "Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM users.",
    "query": "SELECT (SELECT RAW subcomments \nFROM (Select * from comments.subcomments as sbcm\nJOIN users ON sbcm.userId = users.id ) as a\nORDER BY subcomments.createdDate DESC) as subcomments,\nARRAY_COUNT(comments.subcomments) as subcommentCount \nFROM comments where comments.id = "f4f0f481-f0d6-4edf-8a0c-494651becdf2" and projectId= "1""
  },
  {
    "code": 5010,
    "msg": "Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM users."
  }
]

What should I do? Can you help me?

vsr · Accepted Answer · 2020-10-16T21:06:41.040

At present correlated subqueries requires USE KEYS MB-30813. You can try following

SELECT d.subcommentCount,
       (SELECT RAW sc1
        FROM d.subcomments AS sc1
        ORDER BY sc1.createdDate DESC) AS subcomments
FROM ( SELECT COUNT(sc) AS subcommentCount,
              ARRAY_AGG(sc) AS subcomments
       FROM comments AS c
       LEFT UNNEST c.subcomments AS sc
       JOIN users ON sc.userId = users.id
       WHERE c.id = "f4f0f481-f0d6-4edf-8a0c-494651becdf2" AND c.projectId= "1"
       GROUP BY META(c).id) AS d;

Couchbase N1QL Subquery join issue

1 Answers1