How do I create a usable index for an array of objects using pouchdb-find?

Question

I only want to get data with groups that match at least with one of those groupIDs. I have tried a few solutions but I still did it wrong. Assuming we have data like this.

const groupIDs = ['124', '245', '678', '999', '111'];

const data = [
  {
    _id: 'example:123',
    _rev: 'example',
    name: 'John',
    groups: [
      { groupID: '124', type: 'ADMIN' },
      { groupID: '345', type: 'PRACTITIONER' },
      { groupID: '678', type: 'PATIENT' },
    ]
  },
  {
    _id: 'example:456',
    _rev: 'example2',
    name: 'Yoda',
    groups: [
      { groupID: '124', type: 'ADMIN' },
      { groupID: '345', type: 'PRACTITIONER' }
    ]
  },
];

This is my attempt:

db.createIndex({
  index: { fields: ['groups.[].groupID'] }
})
.then((result) => {
  db.find({
    selector: { groups.groupID: { $in: groupIDs } },
    use_index: results.name
  })
})

user_4964791 · Answer

Here's one of surely many ways to achieve the desired result with Mango. I've also included an example Map/Reduce.

For Mango, the highlights are creating the index and selector. The index goes like so

 db.createIndex({
    index: {
       fields: ['groups'],
       ddoc: 'my-index'
    }
 });

Note I prefer to name the index (ddoc).

So we need combination operators that work with array fields, of which are the $in and $elemMatch operators.

From the CouchDB find documentation^[1]:

The $elemMatch operator matches and returns all documents that contain an array field with at least one element matching the supplied query criteria.

Further, the $in documentation states

The document field must exist in the list provided.

Great! Let's use the $elemMatch and $in combination operators to find a set of groupIDs in the groups array.

 selector: {
    groups: {
      $elemMatch: {
         groupID: { $in: groupIDs }
      }
   }

The following snippet demonstrates both a Mango query using the above selector and a Map/Reduce solution using the keys query option.

The significance between the two is that the Map/Reduce produces a document for every match, meaning there may be redundant documents; the Mango result set returns a document if it matches one or more times.

// convenience
const gel = id => document.getElementById(id);

const all_docs = 'allDocs';
const groups_groupID = 'groupID';
const groups_groupIDView = 'groupIDView';
const g_view_result = 'view_result';
const g_groupIdHints = 'groupIdHints';

const queryFns = {};
// show all the documents used in this example.
queryFns[all_docs] = () => db.allDocs({
  include_docs: true
});
// Mango - find one or more groupIDs in the groups array.
// @groupIDs is an array of one or more values.
queryFns[groups_groupID] = (groupIDs /*array*/ ) => {
  const query = {
    selector: {
      groups: {
        $elemMatch: {
          groupID: {
            $in: groupIDs
          }
        }
      }
    },
    use_index: "my-index"
  }
  return db.find(query);
}

// Map/Reduce - query the groupIDView for one or more groupIDs
// @groupIDs is an array of one or more values.
queryFns[groups_groupIDView] = (groupIDs /*array*/ ) => db.query(
  groups_groupIDView, {
    include_docs: true,
    reduce: false,
    keys: groupIDs
  });

// execute a query named in queryFns
const query = async(fnName, csv) => {
  const html = [];
  const view_result = gel(g_view_result);

  try {
    // convert csv to array
    const groupIds = (csv || '').split(',').map(id => id.trim());
    let docs = await queryFns[fnName](groupIds);

    // Ensure docs is an array of documents (e.g. db.allDocs vs db.find result)
    // Beware the SO 'Tidy' function smashes the null coalesce operator 
    docs = docs.rows ?? docs.docs;

    html.push(`Matches: ${docs.length || 0}`);
    // collect the docs for view
    docs.forEach(doc =>
      html.push(`${JSON.stringify(doc,undefined,3)}`)
    );

  } catch (e) {
    // display any error message
    html.unshift(e.message);
  }

  view_result.innerHTML = html.join('');
}

// canned demo documents
function getDocsToInstall() {
  return [{
      _id: 'example:123',
      //_rev: 'example',
      name: 'John',
      groups: [{
          groupID: '124',
          type: 'ADMIN'
        },
        {
          groupID: '345',
          type: 'PRACTITIONER'
        },
        {
          groupID: '678',
          type: 'PATIENT'
        },
      ]
    },
    {
      _id: 'example:456',
      // _rev: 'example2',
      name: 'Yoda',
      groups: [{
          groupID: '124',
          type: 'ADMIN'
        },
        {
          groupID: '345',
          type: 'PRACTITIONER'
        }
      ]
    },
  ]
}

//
// init db 
//
let db;

(async() => {

  db = new PouchDB('test', {
    adapter: 'memory'
  });

  // install the docs into the db
  const docs = getDocsToInstall();
  await db.bulkDocs(docs);

  // create a mango index for the 'groups' field.
  db.createIndex({
    index: {
      fields: ['groups'],
      ddoc: "my-index"
    }
  });
  // declare groups_groupIDView map/reduce index for groups.groupID
  const ddoc = {
    _id: '_design/' + groups_groupIDView,
    views: {
      groupIDView: {
        map: function(doc) {
          if (doc.groups instanceof Array) {
            doc.groups.forEach(g => {
              emit(g.groupID);
            })
          }
        }.toString()
      }
    }
  };
  // install the map/reduce design doc
  await db.put(ddoc);

  // candy - gather up and display groupIDs for this demo
  const hints = {};
  docs.forEach(doc => doc.groups.forEach(g => hints[g.groupID] = 1));
  gel(g_groupIdHints).innerText = Object.keys(hints).join(', ');

})();

.hide {
  display: none
}

.label {
  text-align: right;
  margin-right: 1em;
}

.hints {
  font-size: smaller;
}






  
    
      
    
  
  
    
      Find GroupIDs: 
      
      
    
  
  
    
      Find GroupIDs: 
      
      
    
  
  
    
      Enter CSV of groupID's, e.g.

¹ CouchDB find - Combination Operators

How do I create a usable index for an array of objects using pouchdb-find?

1 Answers