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
})
})
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(`<pre>${JSON.stringify(doc,undefined,3)}</pre>`)
);
} catch (e) {
// display any error message
html.unshift(e.message);
}
view_result.innerHTML = html.join('<hr/>');
}
// 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;
}<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/pouchdb.min.js"></script>
<script src="https://github.com/pouchdb/pouchdb/releases/download/7.1.1/pouchdb.memory.min.js"></script>
<script src="https://github.com/pouchdb/pouchdb/releases/download/7.1.1/pouchdb.find.min.js"></script>
<table>
<tr>
<td>
<button onclick='query(all_docs)'>Show All Docs</button>
</td>
</tr>
<tr>
<td>
<label for='groupID'>Find GroupIDs: </label>
<input type='text' id='groupID' />
<button onclick='query(groups_groupID,gel("groupID").value)'>Mango</button>
</td>
</tr>
<tr>
<td>
<label for='groupIDView'>Find GroupIDs: </label>
<input type='text' id='groupIDView' />
<button onclick='query(groups_groupIDView,gel("groupIDView").value)'>Map/Reduce</button>
</td>
</tr>
<tr>
<td>
<span class='hints'>Enter CSV of groupID's, e.g. <span id='groupIdHints'></span></span>
</td>
</tr>
<tr>
</table>
<div style='margin-top:2em'></div>
<div>
<pre id='view_result'></pre>
</div>