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

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?

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
1 Like

It works, Thank you very much!

1 Like

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

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

if I run this query I will get a result something like this

{
“name”:“name1”,
“relationship”: {
“id”:“id-2”,
“value”:“value-2”
}
}
Here relationship document is not nested in an Array.

If I want a result something like this.
{
“name”:“name1”,
“relationship”: [{
“id”:“id-2”,
“value”:“value-2”
}]
}
Then what should be the query ?

Can you please suggest ?

SELECT name, ARRAY_ADD([],FIRST relation FOR relation IN relationship WHEN relation.id = "id-2" END) AS relationship FROM mydb WHERE ANY relation IN relationship SATISFIES relation.id = "id-2" END;

HTH.