CouchDB display distinct values

Question

I have a document like below,

 {
     "id": "7d9fdc2f4846544d62da3421bf011b31",
     "al": [
       { "id16": "0x1d42",
         "pos": {
                "x": 10.32,
                "y": 11.13,
                "z": 1.22
                },
           "resultTime": "2020-06-01T20:45:34.976Z"
       },
      { "id16": "0x1342",
          "pos": {
                "x": 0.32,
                "y": 1.13,
                 "z": 13.22
                 },
        "resultTime": "2021-06-01T20:45:34.976Z"
      }
     .
     .
     .
     ],
      "Timestamp": 272179,
      "Oid": "Onion1",
     
    }

and Design document is like below

{
 "id": "_design/GetALwithAnchorID",
 "key": "_design/GetALwithAnchorID",
 "value": {
  "rev": "32-6db6c4e105336d47a6c8e7e8458ee345"
 },
 "doc": {
  "_id": "_design/GetALwithAnchorID",
  "_rev": "32-6db6c4e105336d47a6c8e7e8458ee345",
  "views": {
   "GetALwithAnchorID": {
    "map": "function (doc) {\n\n  for (var i=0; i<doc.al.length; i++) { \n    emit(doc.al[i].id16, doc.al[i].pos);\n    }\n    \n}\n\n",
    "reduce": "_approx_count_distinct"
   }
  },
  "language": "javascript"
 }
}

when I query the view like

http://127.0.0.1:5984/rtls/_design/GetALwithAnchorID/_view/GetALwithAnchorID?group_level=1&key=%220x1d42%22

I get the results as below

{"rows":[
{"key":"0x1d42","value":1}
]}

But I want distinct values of id16 and pos of id16. and to sort these distinct values by time and display the values of pos instead of "value":1 when Iquery?

thank you in advance.

Answer 1

OK so not quite the same as this similar answer. Anyone coming across this Q/A, I recommend reading over that answer.

Consider the following emit for your given doc structure:

doc.al.forEach(e => emit(
  [e.pos.x, e.pos.y, e.pos.z, e.resultTime], // key
  [e.id16, e.pos, e.resultTime]) // value
));    

   

The emit's complex key visualized in the index (loosely not verbatim):

[-3,-2,-1,"2017-10-28T22:56:58.852Z"]
[-3,-2,-1,"2019-01-23T03:33:20.958Z"] **
. . .
[0,0,0,"2016-05-27T01:38:36.305Z"]
[0,0,0,"2016-12-27T05:17:02.255Z"] **
. . .
[1,2,3,"2016-11-14T17:31:59.468Z"]
[1,2,3,"2017-07-17T07:52:38.180Z"] **

Where each ** the last item in the pos group and significantly the most recent resultTime. All due to CouchDB's collation.

Working with CouchDB demands understanding the B-tree, and it's documentation has a great rundown of it in its Reduce/Rereduce documentation.

Now consider this reduce function:

function(keys,values,rereduce) {               
   return values[0];
}

It doesn't look terribly impressive, but further consider calling the view with these parameters:

{
    reduce: true,
    group_level: 1,
    descending: true
}

By reversing the order of the index scan with descending the reduce function is guaranteed to return the most recent row with respect to resultTime of any given pos group.

Here's a simple demo using pouchDB. It generates 6 documents with random resultTime's and randomly selects pos from a pool of 3. Have a look at the design doc.

async function showReduceDocs(view) {
  let result = await db.query(view, {
    reduce: true,
    group_level: 1,
    descending: true
  });
  // show   
  debugger;
  gel('view_reduce').innerText = result.rows.map(row => `${JSON.stringify(row.value)}`.split(',').join(', ')).join('\n');

  return result;
}

async function showViewDocs(view) {
  let result = await db.query(view, {
    reduce: false,
    include_docs: false
  });
  //show   
  gel('view_docs').innerText = result.rows.map(row => JSON.stringify(row.key))
    .join('\n');
}

function getDocsToInstall(count) {
  // design document
  const ddoc = {
    "_id": "_design/SO-66231293",
    "views": {
      "id16": {
        "map": `function (doc) {          
           doc.al.forEach((e) => emit([e.pos.x, e.pos.y, e.pos.z, e.resultTime],[e.id16, e.pos, e.resultTime]));           
        }`,
        "reduce": `function(keys,values,rereduce) {               
           return values[0];
        }`
      }
    }
  };

  // create a set of random documents.
  let docs = new Array(count);
  let docId = 65;
  const posSeed = [{
      x: 0,
      y: 0,
      z: 0
    },
    {
      x: 1,
      y: 2,
      z: 3
    },
    {
      x: -3,
      y: -2,
      z: -1
    }
  ];
  const dateSeed = [new Date(2000, 0, 1), new Date(), 0, 24];
  while (count--) {
    let n = 6;
    let doc = {
      _id: String.fromCharCode(docId++),
      al: new Array(n)
    };

    while (n-- > 0) {
      doc.al[n] = {
        "id16": "0x000" + n,
        "pos": posSeed[Math.floor(Math.random() * 100) % 3],
        "resultTime": randomDate(...dateSeed).toISOString()
      };
    }

    docs[count] = doc;
  }

  docs.push(ddoc);
  return docs;
}

const db = new PouchDB('SO-66231293', {
  adapter: 'memory'
});

(async() => {
  // install docs and show view in various forms.
  await db.bulkDocs(getDocsToInstall(6));
  gel('content').classList.remove('hide')
  showReduceDocs('SO-66231293/id16');
  showViewDocs('SO-66231293/id16');
})();

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

/*
https://stackoverflow.com/questions/31378526/generate-random-date-between-two-dates-and-times-in-javascript/31379050#31379050
*/
function randomDate(start, end, startHour, endHour) {
  var date = new Date(+start + Math.random() * (end - start));
  var hour = startHour + Math.random() * (endHour - startHour) | 0;
  date.setHours(hour);
  return date;
}

<script src="https://cdn.jsdelivr.net/npm/pouchdb@7.1.1/dist/pouchdb.min.js"></script>
<script src="https://github.com/pouchdb/pouchdb/releases/download/7.1.1/pouchdb.memory.min.js"></script>
<div id='content' class='hide'>
  <div>View: reduce</div>
  <pre id='view_reduce'></pre>
  <hr/>
  <div>View: complex key</div>
  <pre id='view_docs'></pre>
</div>

Edit Amended the demo snippet according to OP's comments.