Finding Arrays with empty values

SQL++
1

Hi Team,
I have a situation where there was a large scale data migration, and in process, it appears some documents were loaded improperly with empty array list items (see below document example). I’m trying to find and resolve all instances of this issue, but didn’t find any single function (or even combination) that could identify empty items in an array list as such. Any insight on a N1QL query to find empty items in array lists as per below (not just empty array lists) is greatly appreciated!

    "objectList": [
               {
                            "updatedBy":  "USER",
                            "identifierList":  [{}, {}, {
                                           "updatedBy":  "ABC",
                                           "ntID":  "AB1234-123"
                            }],
                           "seq_no": 15
                }
     ]
2 
UPDATE default d
SET ol.identifierList = ARRAY v FOR v IN ol.identifierList WHEN v != {} END
            FOR ol IN d.objectList END
WHERE .....
3

Thank you, will give that a try!

4

So I realized the data shared with me earlier was incorrect, and that there’s no parent array; rather objectList above is the contents of the document itself, and the data is rather like:
{
“updatedBy”: “USER”,
“identifierList”: [{}, {}, {
“updatedBy”: “ABC”,
“ntID”: “AB1234-123”
}],
“seq_no”: 15
}

I tried to update the query to:

UPDATE default d
SET ol.identifierList = ARRAY v FOR v IN ol.identifierList WHEN v != {} END
FOR ol IN d END
WHERE type = “whateverType”

but then I get an index out of range exception…wondering if that’s because not all of my documents contain the identifierList array, or if I’m just not understanding the syntax.

On a related note, what would be the equivalent select statement to find the {} ? I’ve always used the ANY/SATISFIES combination to loop arrays, but here I have no property to check whether it satisfies a criteria…so I’m kinda at a loss.

Thanks in advance!

5 
UPDATE default d
SET d.identifierList = ARRAY v FOR v IN d.identifierList WHEN v != {} END
WHERE d.type = "whateverType" AND IS_ARRAY(d.identifierList)