How to query PouchDB with SQL-like operators

Question

Being relatively new to PouchDB/CouchDB, I'm still trying to wrap my head around how to use the map/reduce correctly in different cases.

Supposed that I have document structure like this:

{
  _id: 'record/1',
  labels: {
    // many-to-many relationship
    'label/1': true, // let's assume that this is 'Label A'
    'label/3': true, // 'Label C'
    'label/4': true // 'Label D'
  }
},
{
  _id: 'record/2',
  labels: {
    'label/1': true, // 'Label A'
    'label/2': true // 'Label B'
  }
}

What are the correct ways to define view for db.query function to search:

records with 'Label A' or 'Label B'
records with 'Label A' and 'Label B'

Have a look [here](http://stackoverflow.com/questions/28339910/how-can-i-translate-my-sql-queries-to-cloudant-couchdb). The answer *multiple WHERE and OR clauses* should help you. — Chris Snow, Mar 02 '15 at 11:38

score 1 · Answer 1 · answered Mar 02 '15 at 17:23

1

There are no OR operations in PouchDB/CouchDB mapreduce queries, so you would have to break it up into two separate queries.

Eventually these kinds of operations will be supported in pouchdb-find, but as of this writing, $or hasn't been implemented yet.

answered Mar 02 '15 at 17:23

nlawson

11,510
4
40
50

The plugin looks promising. Although I can't use it for this case but I'm hoping to use it in some other cases. – Kenny Ki Mar 06 '15 at 10:36

Kenny Ki · Accepted Answer · 2015-03-06T15:42:52.727

As much as I wanted to use the pouchdb-find plugin, I couldn't find a way to achieve what I needed. Instead I used a workaround:

Change the document structure to store label IDs in an array

{_id: 'record/1', name: 'Record 1', labels: ['label/1', 'label/2', 'label/3']},
// may not be sorted when being stored
{_id: 'record/2', name: 'Record 2', labels: ['label/1', 'label/5', 'label/7', 'label/3']},
{_id: 'record/3', name: 'Record 3', labels: ['label/2', 'label/3', 'label/4', 'label/5']}

Create a design document

It will emit multiple complex keys for each record to represent all possible label-mappings in an ascending order. The map function will utilize a recursive process to generate the keys:

{
  _id: '_design/records-with-labels',
  views: {
    'records-with-labels': {
      map: function(doc) {
        // important: sort them so that the required keys to be created are lesser
        var labelIds = doc.labels.sort();
        var lastIx = labelIds.length - 1;

        var emitKey = function emitKey(currentKey, currentIx) {
          console.log('emitting: ' + currentKey.join(',') + ' for ' + doc._id);
          emit(currentKey, null);

          var nextIx = currentIx + 1;

          for (var jumpIx = nextIx + 1; jumpIx <= lastIx; jumpIx++) {
            var jumpedLabelId = labelIds[jumpIx];
            var jumpingKey = currentKey.concat([jumpedLabelId]);

            console.log('emitting: ' + jumpingKey.join(',') + ' for ' + doc._id);
            emit(jumpingKey, null);
          }

          if (nextIx > lastIx) {
            return;
          }

          var nextLabelId = labelIds[nextIx];

          currentKey.push(nextLabelId);

          emitKey(currentKey, currentIx + 1);
        };

        labelIds.forEach(function(labelId, i) {
          emitKey([labelId], i);
        });

      }.toString()
    }
  }
}

For example, the document record/1 will have these keys generated:

emitting: label/1 for record/1
emitting: label/1,label/3 for record/1
emitting: label/1,label/2 for record/1
emitting: label/1,label/2,label/3 for record/1
emitting: label/2 for record/1
emitting: label/2,label/3 for record/1
emitting: label/3 for record/1

Querying

I just need to ensure that the query labels are sorted in ascending order.

To query records that have 'label/1' and 'label/3':

Db.query('records-with-labels', {
  key: ['label/1', 'label/3']
});

To query records that have 'label/3' or 'label/3':

Db.query('records-with-labels', {
  keys: [['label/1'], ['label/3']]
});

This will give us duplicated records that has both labels, but a reduce function should help eliminating them.

Conclusion

For now I don't know if there is a better solution, but this is good enough for me because in my case, a record will not have too many labels.

If you have better suggestions, please comment or edit the answer.

score 0 · Answer 3 · answered Mar 16 '16 at 17:11

0

This is an older post. However, It is possible to use underscore.js to help with some of the queries. It can help pull out the data you want without having to make several trips to the database (unless you want).

answered Mar 16 '16 at 17:11

eDriven_Levar

361
3
10

How to query PouchDB with SQL-like operators

3 Answers3

Change the document structure to store label IDs in an array

Create a design document

Querying

Conclusion

Linked