N1ql query delete orphaned documents

SQL++
1

Due to an import error, I have a bunch of documents (1.4M) that are referencing documents that no longer exists.

For instance, I have the following event document:

{
    "time": 18239,
    "recordId": "jhw8er23rh92",
    "type": "event"
}

But the document with meta().id == jhw8er23rh92 no longer exists. So I would like to delete this event document (and all 1.4M) of them with a DELETE query.

I’ve tried many things, but I can only seem to get the event documents with recordId that actually do exist with:

select d.recordId from my_bucket d where d.recordId is not null.

Any ideas?

2

Deleting 1.4M documents is lot of mutations and it can take really long time.

You can find the documents that refrencing as follows

If you already know the which value you are looking.

SELECT RAW META(d).id 
FROM default AS d
WHERE d.type = "event" AND d.recordId = "jhw8er23rh92";

If you don’t know which value you are looking and corresponding document doesn’t exist

SELECT RAW META(l).id 
FROM default AS l
LEFT JOIN default AS r 
ON KEYS l.recordId
WHERE l.type = "event" AND r IS MISSING;