N1QL query to retrieve results based on matching criteria in array

SQL++
1

I need some help with retrieving results from my bucket based on a value in a nested array in my document.
Running the query below:

SELECT * FROM server-bucket-name AS blacklist WHERE blacklist.type=“blacklist”;

"results": [
     {
         "blacklist": {
             "list": [
                 {
                     "blackListedBy": "Jason",
                     "entryDateTime": "09172015221459",
                     "lookupID": "U-53267",
                     "memberID": "M-983-34A",
                     "reasonID": "809"
                 },
                 {
                     "blackListedBy": "System",
                     "entryDateTime": "09172015221459",
                     "lookupID": "U-02246",
                     "memberID": "M-526-92P",
                     "reasonID": "605"
                 },
                 {
                     "blackListedBy": "Debbie",
                     "entryDateTime": "09172015221459",
                     "lookupID": "U-82354",
                     "memberID": "M-675-J45",
                     "reasonID": "352"
                 }
             ],
             "type": "blacklist"
         }
     }  ]

I tried the N1QL statement below:

SELECT *
FROM server-bucket-name AS blacklist
WHERE type="blacklist"
AND EVERY blacklisted IN blacklist.list
SATISFIES blacklisted.lookupID = “U-53267” END;

Statement above is successful with no results, also tried using an unnest statement geraldss recommended here:

SELECT b.lookupID
FROM server-bucket-name b
UNNEST b.list l
WHERE l.lookupID = “U-53267”;

That returned no results as well. When I reconstruct these statements for the tutorial bucket I also have running on my sandbox I do get results back.

Keep wondering what it is I’m missing. Any help would be greatly appreciated.

Thanks

2

try this pls; ANY will ensure to return the document when 1 of the items in the “list” satisfies your lookupID
SELECT *
FROM server-bucket-name AS blacklist
WHERE type="blacklist"
AND ANY blacklisted IN blacklist.list
SATISFIES blacklisted.lookupID = “U-53267” END;

3

Thanks cihangirb.

I did try “ANY” earlier, it returns all items in the array. Results below:

"results": [
    {
        "blacklist": {
            "list": [
                {
                    "blackListedBy": "Jason",
                    "entryDateTime": "09172015221459",
                    "lookupID": "U-53267",
                    "memberID": "M-983-34A",
                    "reasonID": "809"
                },
                {
                    "blackListedBy": "System",
                    "entryDateTime": "09172015221459",
                    "lookupID": "U-02246",
                    "memberID": "M-526-92P",
                    "reasonID": "605"
                },
                {
                    "blackListedBy": "Debbie",
                    "entryDateTime": "09172015221459",
                    "lookupID": "U-82354",
                    "memberID": "M-675-J45",
                    "reasonID": "352"
                }
            ],
            "type": "blacklist"
        }
    }
]

It’s the same thing when I use “ANY” on the tutorial bucket as well (which is the same thing that happens here), all results are returned, using “EVERY” just returns the desired resultset (only in tutorial bucket on sandbox and N1QL Query Tutorial site).

4

if you want to only return a subset of the array you need to use unnest. can you assemble the restul of the statement you’d like to see and we can work backwards from the result to construct the query.

5

I guess I’m just a little confused and don’t really understand the language well.

Had to go back and read statement you made earlier “ANY will ensure to return the document when 1 of the items in the “list” satisfies your lookupID”.

Meaning the * in the SELECT statement is what’s returning the whole document despite the fact that it satisfies my lookupID. So going by your recent statement to unnest, I ran the query below and got the desired results

SELECT list
FROM server-bucket-name b
UNNEST b.list
WHERE b.type="blacklist"
AND list.lookupID=“U-53267”;

"results": [
    {
        "list": {
            "blackListedBy": "Jason",
            "entryDateTime": "09172015221459",
            "lookupID": "U-53267",
            "memberID": "M-983-34A",
            "reasonID": "809"
        }
    }
]

Would this be the proper way to extract desired results in cases like this?

Thanks again for your help.