Google Pub/Sub update Firestore sub collection

Question

I am trying to update a field in a subcollection every 2 minutes. I am changing the field "Status" to "Off" if the timestamp is older than 3 minutes old. I have this code that someone helped me with and It works with a collection but I dont know what collection path to use to access all documents that have this subcollection in them.

After 5 minutes I want the "Status" Field to be "Off" if the timestamp is more than 3 minutes old.

const snap = await db.collection("MX") works but only for 1 layer deep. Each document in userProfiles has a subCollection of MX.

I have tried wildcards and several different collection paths.

exports.updateIdle = functions.pubsub.schedule('every 2 minutes').onRun(async () => {

const db = admin.firestore();
// this is what line I am trying to figure out
  const snap = await db.collection("userProfiles") 
    .where("Status", "=", "On")
    .where("Timestamp", "<", admin.firestore.Timestamp.fromMillis(Date.now() - 3 * 60000))
    .get();


  await Promise.all(snap.docs.map(doc => doc.ref.update({Status: 'Off'})));

  console.log('this goes off every two minutes')
});

EDIT: userProfile -> each doc -> 4 sub collections (MX, employees, settings, logs)

I have documents stored inside of MX. These documents will have a Status and a Timestamp.

I want this pub/sub to check all of the documents stored inside of the MX subcollection.

And if the timestamp is 3-5 minutes old, and Status = On. I want to change the Status to Off

So I am checking all MX subcollections. In this case there is 4 documents in userProfiles and there is a MX subcollection inside each document.

I appreciate any and all help. I hope I am explaining this well enough. Thanks.

Do you confirm that, in other words, you are looking for a solution to find documents of the MX collection that have an MX sub-collection — Renaud Tarnec
Yes. All docs inside userProfiles have an MX subcollection that I would like to access. — Yewla
And you know that these docs do have an MX collection. Is your problem how to access the docs of this MX collection? — Renaud Tarnec
Also, is there a specific document in the MX subcollection of a userProfile that you want to update? Or you want to update all docs of this userProfile/MX subcollection? — Renaud Tarnec
Yes. I want this pub/sub to look at all of the docs in "MX", and if the "Timestamp" is more than 3 minutes old I would like to change "Status" to "Off" — Yewla

Renaud Tarnec Renaud Tarnec · Accepted Answer · 2019-12-02T15:51:18

If you want to "update all docs in any MX subcollection that has an old timestamp" the following should work, using Promise.all():

exports.updateIdle = functions.pubsub.schedule('every 2 minutes').onRun(async () => {

    const db = admin.firestore();

    const querySnapshot = await db.collection("userProfiles") 
        .where("Status", "=", "On")
        .where("Timestamp", "<", admin.firestore.Timestamp.fromMillis(Date.now() - 3 * 60000))
        .get();
    
    const promises = [];
    querySnapshot.forEach(doc => {
        const docRef = doc.ref;
        promises.push(docRef.collection('MX').get());
    });
    
    const snapshotArrays = await Promise.all(promises);
    // snapshotArrays contains an array of arrays of QueryDocumentSnapshots

    const promises1 = [];
    
    snapshotArrays.forEach(snapArray => {
        snapArray.forEach(snap => {
            promises1.push(snap.ref.update({
                Status: "Off"
            }))
        })
    });
    
    return Promise.all(promises1);
    
});

Of course, I make the assumption that your initial query to find the userProfile docs with a "timestamp that is old" is correct.

Update based on your comment

I understand that the filtering on "timestamp is 3-5 minutes old, and Status = On" is to be done at the level of the MX documents. The following should work then (we just move the where clauses to the MX collections). Note however that in this case we read all the userProfile documents (each read costing a standard document read).

exports.updateIdle = functions.pubsub.schedule('every 2 minutes').onRun(async () => {

    const db = admin.firestore();

    const querySnapshot = await db.collection("userProfiles").get();

    const promises = [];
    querySnapshot.forEach(doc => {
        const docRef = doc.ref;
        promises.push(docRef.collection('MX').where("Status", "=", "On")
        .where("Timestamp", "<", admin.firestore.Timestamp.fromMillis(Date.now() - 3 * 60000)).get());
    });

    const snapshotArrays = await Promise.all(promises);
    // snapshotArrays contains an array of arrays of QueryDocumentSnapshots

    const promises1 = [];

    snapshotArrays.forEach(snapArray => {
        snapArray.forEach(snap => {
            promises1.push(snap.ref.update({
                Status: "Off"
            }))
        })
    });

    return Promise.all(promises1);

});

If you still encounter errors, try without the where clauses as follows, and refien your where clauses in order to get the desired MX documents selection.

//....
const querySnapshot = await db.collection("userProfiles").get();

const promises = [];
querySnapshot.forEach(doc => {
    const docRef = doc.ref;
    promises.push(docRef.collection('MX').get());
});
//....

Google Pub/Sub update Firestore sub collection

1 Answers

Update based on your comment