Mango Query + Apache CouchDB 3.x.x - sort array by property from array

Question

I have following docs

{
  "_id": "products:info",
  "_rev": "3-47add0ce8ecbcb2c4c7dfefae83c02cd",
  "created": "01-26-2022",
  "products": [
    {
      "brand": "MSI",
      "title": "Summit e16",
      "sku": "1000121"
    },
    {
      "brand": "MSI",
      "title": "Summit e13",
      "sku": "1000120"
    }
  ]
}

I wanted to sort products array by sku property in desc order and get first product sku.

I tried following so far.

Index

{
 "type": "json",
 "partitioned": true,
 "def": {
  "fields": [
   {
    "products[].sku": "asc"
   }
  ]
 }
}

Query

{
   "selector": {
      "products": {
         "$elemMatch": {
            "sku": {
               "$ne": null
            }
         }
      }
   },
   "fields": [
      "_id",
      "products.0.sku"
   ],
   "sort": [
      {
         "products.sku": "desc"
      }
   ]
}

Throwing error

Error running query. Reason: (no_usable_index) No global index exists for this sort, try indexing by the sort fields.

Tried following as well

{"products.sku": "desc"}
{"products.[].sku": "desc"}

It is not clear whether there are many documents each with an array of `products` or that there is a single document with _id `products:info` - which is it? — RamblinRose, Jan 27 '22 at 13:51
Additionally it *may* be the `no_usable_index` error is occurring because `_id` is not specified in `sort`. — RamblinRose, Jan 28 '22 at 02:40
@RamblinRose Just one document with _id `products:info` this document has `products` array. — Pasupathi Rajamanickam, Jan 28 '22 at 05:41
@RamblinRose what do you mean `_id` not specified in sort? I don't need to sort document by `_id`, I want to sort by `document.products[].sku` but as you said I added `_id` to sort object it was throwing same error. — Pasupathi Rajamanickam, Jan 28 '22 at 05:42
`sort` sorts *documents* (that satisfy the selector) according to the sort fields. However there only one document, so there is nothing to sort. Sort is not array sort. — RamblinRose, Jan 28 '22 at 11:39

RamblinRose · Answer 1 · 2022-01-28T15:53:17.823

As commented

'sort' sorts documents (that satisfy the selector) according to the sort fields. However there only one document, so there is nothing to sort. Sort is not array sort.

So the desired outcome cannot be achieved with a _find selector. Since that is so, I will offer a suggestion and an alternative.

First, putting all products into a single file seems like a bad idea - denormalization gone wild? If this is a real world application do note the $elemMatch is an in-memory operation and may result in poor outcomes for a very large product list.

The solution is easily achieved with a view. Consider this map function

function (doc) {
  if(doc._id === "products:info" && doc.products) {    
    doc.products.forEach((product) => { 
      emit(product.sku,{brand: product.brand, title: product.title}); 
    });
  }
}

Given the example document from the OP the view index would look like this

id	key	value
products:info	1000120	{"brand": "MSI", "title": "Summit e13"}
products:info	1000121	{"brand": "MSI", "title": "Summit e16"}

There may be other requirements, but this view covers a lot

Get skus in descending or ascending order; use limit to get first/last/chunks of skus
Leveraging the value field removes the need to load the actual document for specific information
Reduce = _count provides some nice information with regards to (non)null skus for free

In the snippet below the products list is

"products": [{
    "brand": "MSI",
    "title": "Summit e16",
    "sku": "1000121"
  },
  {
    "brand": "Asus",
    "title": "L510",
    "sku": null
  },
  {
    "brand": "MSI",
    "title": "Summit e13",
    "sku": "1000120"
  },
  {
    "brand": "Asus",
    "title": "VivoBook 15 K513",
    "sku": null
  },
  {
    "brand": "MSI",
    "title": "GT72S Dominator Pro",
    "sku": "1000122"
  },
]

and the resulting view index looks like this

id	key	value
products:info	null	{"brand": "Asus", "title": "L510"}
products:info	null	{"brand": "Asus", "title": "VivoBook 15 K513"}
products:info	1000120	{"brand": "MSI", "title": "Summit e13"}
products:info	1000121	{"brand": "MSI", "title": "Summit e16"}
products:info	1000122	{"brand": "MSI", "title": "GT72S Dominator Pro"}

The default state of the snippet when executing the view solves the OP requirement.

const getDocsToInstall = () => {
  return [{
      "_id": "_design/products:info",
      "views": {
        "skus": {
          "reduce": "_count",
          "map": "function (doc) {\n  if(doc._id === \"products:info\" && doc.products) {\n    doc.products.forEach((product) => { emit(product.sku,{brand: product.brand, title: product.title}); });\n  }\n}"
        }
      }
    },
    {
      "_id": "products:info",
      "created": "01-26-2022",
      "products": [{
          "brand": "MSI",
          "title": "Summit e16",
          "sku": "1000121"
        },
        {
          "brand": "Asus",
          "title": "L510",
          "sku": null
        },
        {
          "brand": "MSI",
          "title": "Summit e13",
          "sku": "1000120"
        },
        {
          "brand": "Asus",
          "title": "VivoBook 15 K513",
          "sku": null
        },
        {
          "brand": "MSI",
          "title": "GT72S Dominator Pro",
          "sku": "1000122"
        },
      ]
    }
  ]
}

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

const initDb = async() => {
  db = new PouchDB('test', {
    adapter: 'memory'
  });
  await db.bulkDocs(getDocsToInstall());
}

async function query(params) {
  console.info(params)
  const results = await db.query("products:info/skus", params);
  gel('results').innerText = JSON.stringify(results, undefined, 2);
}

async function reduce(opt) {
  const params = {
    reduce: true
  }
  if (opt === null) {
    params.startkey = null;
    params.endkey = "0";
  } else if (opt === undefined) {
    params.startkey = "0"
  }
  return query(params);
}

async function view() {
  // sanity
  if (!parseInt(gel('limit').value)) {
    gel('limit').value = 1
  }
  const params = {
    reduce: false,
    include_docs: gel('include_docs').checked,
    limit: parseInt(gel('limit').value),
    descending: gel('descending').checked
  }
  // adjust keys
  if (gel('null_skus').checked) {
    params.startkey = params.descending ? "0" : null;
    params.endkey = params.descending ? null : "0";
  } else if (params.descending) {
    params.endkey = null;
  } else {
    params.startkey = "0";
  }


  return query(params);
}

(async() => {
  await initDb();
  gel('controls').classList.remove('hide');
})();

.hide {
  display: none
}

.vgap {
  margin-top: 1em;
}

<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>
<pre>
<h3>Map/Reduce Alternative</h3>
</pre>
<div id="controls" class="hide">
  <label for="limit">Limit&nbsp;</label><input id="limit" type="number" min="1" value="1" /><br/>
  <input id="descending" type="checkbox" class='vgap' /><label for="descending">&nbsp;Descending</label>
  <input id="include_docs" type="checkbox" /><label for="descending">&nbsp;Include Docs</label>
  <input id="null_skus" type="checkbox" /><label for="null_skus">&nbsp;Null skus</label>
  <br/>
  <button onclick="view()" class="vgap">Execute View</button>
  <hr/>
  <button onclick="reduce('product')">Reduce: Product count</button>
  <button onclick="reduce(null)">Reduce: Null sku count</button>
  <button onclick="reduce()">Reduce: Non-null sku count</button>
  <hr/>
  <pre>
Results
</pre>
  <hr/>
  <pre id="results"></pre>

Disclaimer

I would normalize products by moving each product into its own document.

Yes looks like mango query is for limited usecase. I ended up doing something similar to this. I got a view and reduce method to get what I wanted. Thanks for trying this out. — Pasupathi Rajamanickam, Jan 29 '22 at 06:10

Mango Query + Apache CouchDB 3.x.x - sort array by property from array

1 Answers1

Disclaimer