How to handle null result

N1QL
1

Please help me to handle null result. I want to check a particular value is exist in bucket.
12345 - is exist
67890 - is not exist.

select raw case when res is missing then “Not Available” else “Available” end
from (select Opt1.accountNo as accountNo,ed.* from Opt1
unnest Opt1.productBundles as pb1
unnest pb1.productBundles as pb2
unnest pb2.productComponents as pc1
unnest pc1.extendedDataList as ed
where ed.value=“12345”
LIMIT 1) AS res;

–this query return result as
[
“Available”
]

But

select raw case when res is missing then “Not Available” else “Available” end
from (select Opt1.accountNo as accountNo,ed.* from Opt1
unnest Opt1.productBundles as pb1
unnest pb1.productBundles as pb2
unnest pb2.productComponents as pc1
unnest pc1.extendedDataList as ed
where ed.value=“67890”
LIMIT 1) AS res;

–this query result as
{
“results”:
}

How can I display that “Not available” as an output, please help me here.

2

When from clause subquery produce 0 results it is don’t mean null. It saying no qualified rows.

   SELECT RAW CASE WHEN ARRAY_LENGTH(av) == 0 THEN “Not Available” ELSE  “Available” END
    LET av = ( select Opt1.accountNo as accountNo,ed.* from Opt1
    unnest Opt1.productBundles as pb1
    unnest pb1.productBundles as pb2
    unnest pb2.productComponents as pc1
    unnest pc1.extendedDataList as ed
    where ed. `value` =“67890”
    LIMIT 1);
3

Thanks @vsr1 its worked!

Now if I want to display “67890 is Not Available” then how can i modify the query - its giving me ““msg”: “Ambiguous reference to field ed.”” error when I use
to_string(ed.value)||“Not Available”

4

You need to hard code.

CASE WHEN ARRAY_LENGTH(av) == 0 THEN “67890 Not Available” ELSE “Available” END

5

Thank you, it’s working!