Conditional Where Clause

SQL++
,
1

Hi,

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

I am trying to use condition for specific document type. But I am not sure if it is available or if I can’t find it out. 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 only not other document type. 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© FOR c IN appDetail.group_id_record END
WHERE (appDetail.type = ‘user’ OR appDetail.type = ‘app_detail’ OR appDetail.type = ‘group’)

Now, I want to match a ID for documents of type ‘app_detail’. What is the way to achieve this?

2

this means JOIN default by appDetail.id = META(users).id

this means all document’s id that match appDetail.type = ‘user’ OR appDetail.type = ‘app_detail’ OR appDetail.type = ‘group’ will be find.

if you only want to match ID for documents of type ‘app_detail’, your conditional where clause should be WHERE appDetail.type = 'app_detail'

can you post your sample document,and the result JSON data of what you want to get?

3

This is the result I am getting -

“results”: [
{
“group_id_record”: [
“75c5c7ee2c78c567d87d4ce0c85381e0”
],
“id”: “2d90e5786e76522ee44da8aa65704790”,
“type”: “app_detail”
},
{
“group_id_record”: [
“ae6c71abcf796f7ef052958dc203b67b”,
“75c5c7ee2c78c567d87d4ce0c85381e0”
],
“id”: “c9bfb382fa49c7b714007e2112a37bdd”,
“type”: “app_detail”
},
{
“created_by”: “bce541f098561c019676059e06127fe8”,
“id”: “75c5c7ee2c78c567d87d4ce0c85381e0”,
“name”: “Black Berry”,
“type”: “group”
},
{
“created_by”: “bce541f098561c019676059e06127fe8”,
“id”: “ae6c71abcf796f7ef052958dc203b67b”,
“name”: “Rasp Berry”,
“type”: “group”
},
{
“account_active_type”: 2,
“created_by”: “0”,
“first_name”: “Aaozul”,
“id”: “2”,
“last_name”: “Axim”,
“type”: “user”,
“user_type”: 6
},
{
“account_active_type”: 2,
“created_by”: “0”,
“first_name”: “Aaozul”,
“id”: “1”,
“last_name”: “Axim”,
“type”: “user”,
“user_type”: 6
},
{
“account_active_type”: 0,
“created_by”: “bce541f098561c019676059e06127fe8”,
“first_name”: “Raihan”,
“id”: “2d90e5786e76522ee44da8aa65704790”,
“last_name”: “Haque”,
“type”: “user”,
“user_type”: 4
},
{
“account_active_type”: 2,
“created_by”: “1”,
“first_name”: “Baozul”,
“id”: “bce541f098561c019676059e06127fe8”,
“last_name”: “Axim”,
“type”: “user”,
“user_type”: 0
},
{
“account_active_type”: 0,
“created_by”: “bce541f098561c019676059e06127fe8”,
“first_name”: “Fiverr”,
“id”: “c9bfb382fa49c7b714007e2112a37bdd”,
“last_name”: “Mail”,
“type”: “user”,
“user_type”: 4
}
]

But I want to filter data with specific id, but that filter is only for document type ‘app_detail’. If I write query like this “WHERE appDetail.id = ‘someid’” – then this filters all documents regardless of types. But that WHERE clause is only for documents with specific type like app_detail.

4

Is this what you are expecting.

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

Also you can add condition (appDetail.type = ‘app_detail’ AND appDetail.id = ‘someid’)

5

See CASE expressions for conditional expressions.

6

this is what i wanted!