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"}

Answer 1

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.