How to use ANY ... IN ... SATISFIES after CASE WHEN in SELECT statement?

n1ql

#1

I am using Couchbase Server 4.1.0-5005 Enterprise Edition (build-5005)
My doc looks like the following:

{"name":"name1","relationship":[{"id":"id-1","value":"value-1"},{"id":"id-2","value":"value-2"},{"id":"id-3","value":"value-3"}]}

the result that I want to query is:

"results": [
{"name":"name1","value":"value-2"}
]

by id == “id-2”

My N1QL looks like this:

SELECT name,CASE WHEN ANY relation IN relationship SATISFIES relation.id == "id-2" THEN relation.value END AS value where ANY relation IN relationship SATISFIES relation.id == "id-2"  END

I got the following error:

"errors": [
    {
        "code": 3000,
        "msg": "syntax error - at THEN"
    }
]

Am I wrong or N1QL don’t support the grammar? If not,How can I query data what I want?


#2

Hi,

Here are two different queries for this:

Query 1:

SELECT mydb.name, relation.value
FROM mydb UNNEST mydb.relationship AS relation
WHERE relation.id = "id-2"

Query 2:

SELECT name, FIRST relation.value FOR relation IN releationship WHEN relation.id = "id-2" END AS value
FROM mydb
WHERE ANY relation IN relationship SATISFIES relation.id = "id-2" END

#3

It works, Thank you very much!