Query for comparing Arrays within same document

n1ql

#1

Hi.
I have two Arrays: ‘Category’ and ‘SubCategory’ in the same document.
Both arrays have a field CategoryID.

How can I get The CategoryIDs from ‘SubCategory’ that do not exist within ‘Category’?
I need the n1ql query for this result. Please help.


#2
SELECT subcat.CategoryID 
FROM default AS d 
UNNEST d.SubCategory AS subcat
WHERE subcat.CategoryID NOT IN d.Category[*].CategoryID;

If above query did not work, post sample document and expected output.


#3

It worked. One correction though. You need to specify a comma separated array in the Not in Clause. so the Category array should also be unnested and Raw keyword will be used. Thank you.