CODEWITHSUNDEEP
couchbasesql++
PArt
PArt

Reputation: 1772

How to return pure array to use in subselect

I've a N1QL query like 

SELECT RAW ARRAY_AGG(list.id)
FROM default list
WHERE list.type="list" 
AND "*" IN list.supported

for the following object(s):

{
  "type": "list",
  "id": "*",
  "name": "Everything",
  "listCount": 2,
  "supported": [
     "*",
     "test"
  ]
}

The problem now is, that I'll always get a double array as a result:

[
  [
    "*",
    "test"
  ]
]

How can I now prevent the double array as a result or better: how do I use this result in the following subselect (which always just returns an empty array):

SELECT * 
FROM default server 
WHERE server.type="server" AND ANY listId IN supportedLists SATISFIES 
listId in 

(SELECT RAW ARRAY_AGG(list.id)
FROM default list
WHERE list.type="list" 
AND "*" IN list.supported)

END;

Where server is:

{
  "type": "server",
  "id": "AAABBBCCC",
  "supportedLists": [
    "0",
    "1"
  ],
}

The select

SELECT * 
FROM default server 
WHERE server.type="server" AND ANY listId IN supportedLists SATISFIES 
listId in 

["test", "other features"]

END;

Works fine ... So my problem is definetly the subselect

What I want to archive is to have a list of servers supporting a list (field supportedList) for example for all lists with the feature "test" (field supported)

Upvotes: 1

Views: 172

Answers (1)

PArt
PArt

Reputation: 1772

For anyone having the same issue .... the subselect should be surrounded with ARRAY_FLATTEN((SUBQUERY),1)

See https://forums.couchbase.com/t/in-operator-is-not-working-for-subquery/11041/2

Upvotes: 1

Related Questions