How to write this N1QL query?

SQL++
1

I am trying trying to query a bucket containing documents about “guilds”. A guild can be blacklisted. If a guild is blacklisted, the document has “blacklisted: true”, and if they are not blacklisted, the blacklisted field will be missing entirely.

If a guild is blacklisted, there is no need to fetch the rest of the document, so I came up with this query:

SELECT
CASE WHEN blacklisted
IS MISSING THEN guilds
ELSE ‘blacklisted’
END
AS guild
FROM guilds
WHERE meta().id = “123456789”

This works reasonably well already, if the guild is not blacklisted, the query result will be this:

[
    {
       "guild": {
           <keys here>
       }
    }
]

and if the guild is blacklisted, the result is this:

[
    {
        "guild": "blacklisted"
    }
]

However, I would like to change the result of the query when the guild is blacklisted to this:

[
    {
        "guild": {
            "blacklisted": true
        }
    }
]

How can I achieve this?

2 
SELECT  CASE WHEN g.blacklisted IS MISSING THEN g ELSE  {"blacklisted":true} END AS guild
FROM guilds AS g  USE KEYS "123456789";
1 Like