Conditions in WHERE clause in Couchbase

Question

I am newbie to couchbase. Cannot find online what I am trying to achieve.

I am trying to use condition for specific document type. My condition will be like this - if document type is 'type1' then find specific id in that document and document type 'type2' then find another id in that document type only. My query is like below -

SELECT DISTINCT appDetail.id, appDetail.group_id_record
FROM default appDetail
LEFT JOIN default users ON KEYS appDetail.id
LEFT JOIN default groups ON KEYS ARRAY 'app_detail::' || TO_STRING(c) FOR c 
IN appDetail.group_id_record END
WHERE (appDetail.type = 'user' OR appDetail.type = 'app_detail')

I assume I need additional condition in JOIN may be?

Answer 1

Use the CASE conditional operator

SELECT DISTINCT CASE  WHEN appDetail.type = 'user' THEN appDetail.id WHEN appDetail.type = 'app_detail' THEN appDetail.detailId END, appDetail.group_id_record
FROM default appDetail
LEFT JOIN default users ON KEYS appDetail.id
LEFT JOIN default groups ON KEYS ARRAY 'app_detail::' || TO_STRING(c) FOR c 
IN appDetail.group_id_record END
WHERE (appDetail.type = 'user' OR appDetail.type = 'app_detail')

Be carefull with DISTINCT in your query because you group same id although the type is different (I don't think you want this result)