Problem with a a IN Select Query

SQL++
1

Based on what i see the select Query whitch uses the IN clause expects an Array of Strings or Numbers when you pass it.
So i created the folowing Query

SELECT META(c).id,
       e.address,
       c.PropertyAddress.formattedAddress
FROM Contacts c
UNNEST c.emails e
WHERE c._type= 'farm'
    AND ARRAY_COUNT(c.emails) > 0
    AND e.status IS MISSING
    AND c.tract IN (
    SELECT raw d.tract_id
    FROM Contacts d USE KEYS 'tract_info::6CAD9D2B-DD86-44A1-916C-30D2906F0ACB')

But for some reason it does not work, after some more research i found the issue to being that the query

SELECT raw d.tract_id
        FROM Contacts d USE KEYS 'tract_info::6CAD9D2B-DD86-44A1-916C-30D2906F0ACB'

returns actually the following result

[[ 15259, 15260, 15261, 15262 ]]

which is an array within an array. So is there a way to tell the query to use the [0] element of the array or do i have to change the select query in the IN clause ?

Here is what the doc looks like which i is retrieved via the IN Query

{
“_id”: “6CAD9D2B-DD86-44A1-916C-30D2906F0ACB”,
“_type”: “tract_info”,
“absent_occupied”: 43,
“carrier_route”: “”,
“created_on”: “2018-12-12T12:13:39.443Z”,
“name”: “Orleans”,
“nickname”: “”,
“owner_occupied”: 105,
“property_count”: 148,
“tract_id”: [
15259,
15260,
15261,
15262
],
“updated_on”: null
}

2

Your tract_id is ARRAY and SELECT subquery as expression (except FROM clause, it unwinds and return each as document) returns another ARRAY.

As you use USE KEYS and one array just do subscript [0] on SELECT

SELECT META(c).id,
       e.address,
       c.PropertyAddress.formattedAddress
FROM Contacts c
UNNEST c.emails e
WHERE c._type= 'farm'
    AND ARRAY_COUNT(c.emails) > 0
    AND e.status IS MISSING
    AND c.tract IN (
    SELECT raw d.tract_id
    FROM Contacts d USE KEYS 'tract_info::6CAD9D2B-DD86-44A1-916C-30D2906F0ACB')[0]

OR

Use ARRAY_FLATTEN

SELECT META(c).id,
       e.address,
       c.PropertyAddress.formattedAddress
FROM Contacts c
UNNEST c.emails e
WHERE c._type= 'farm'
    AND ARRAY_COUNT(c.emails) > 0
    AND e.status IS MISSING
    AND c.tract IN ARRAY_FLATTEN((
    SELECT raw d.tract_id
    FROM Contacts d USE KEYS 'tract_info::6CAD9D2B-DD86-44A1-916C-30D2906F0ACB'),1)

Also If using 6.5 Checkout https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/