CouchDB: binding a document that refers to an array of different types of documents

Question

I am new when it comes to CouchDB. I came from the world of .NET SQL Server.

In scrolling through CouchDB The Ultimate Guide, I was like "wut wut, that's awesome." Now I am testing some of the things that I have learned in the hope of realizing it in the real world.

I just signed up for a Cloudant account a few weeks ago and started using it for testing / training.

In a mess with related documents, the whole theory behind looks simple, as well as direct examples on the Internet. Where I want to get some information from a document that has a lot of different related documents that have arrays of related documents. Like a multiple SQL server that connects to many of many relationship tables. You will see the code below. Hope this makes sense.

Take, for example, this SQL query. Assuming that there is only one entry in each of the tables, we must return one entry with all the details for shoes with this skin. But if we had several sizes of shoes, we would have to write another code.

select ci.sku ,sc.color ,ss.size ,si.url from CatalogItem ci join ShoeImages si on ci.sku = si.sku and ci.sku = '656F-PINSEC12' join ShoeSizes ss on ci.sku = ss.sku join ShoeColors sc on ci.sku = sc.sku 

I would like CouchDB to return the next JSON by SKU to https://username.cloudant.com/test/_design/catalogue/_view/item-details?include_docs=true&key=%22656F-PINSEC12%22

 { "_id": "689fe6982f4d604541db67ee4050a535", "_rev": "5-64b5ddd751c51aadfcef1962c2c99c16", "type": "catalogue-item", "sku": "656F-PINSEC12", "upc": "8549875231", "shoe-colors": [ { "color": "black/houndstooth" "shoe-sizes": [ { "size": 5, "IsSizeAvailable": true }, { "size": 6, "IsSizeAvailable": true }, { "size": 7, "IsSizeAvailable": true }, { "size": 8, "IsSizeAvailable": true }, { "size": 9, "IsSizeAvailable": true }, { "size": 10, "IsSizeAvailable": true }, { "size": 11, "IsSizeAvailable": true }, { "size": 12, "IsSizeAvailable": true }, { "size": 13, "IsSizeAvailable": true }, { "size": 14, "IsSizeAvailable": true } ], "shoe-images": [ { "full-images": [ "http://www.someurl.com/full/656F-PINSEC12.jpg" ], "thumbnail-images": [ "http://www.someurl.com/thumb/656F-PINSEC12.jpg" ] } ] } ] } 

Given the following documents and map / reduce:

 //--catalog item { "_id": "689fe6982f4d604541db67ee4050a535", "_rev": "5-64b5ddd751c51aadfcef1962c2c99c16", "type": "catalogue-item", "sku": "656F-PINSEC12", "upc": "8549875231", "shoe-colors": [ { "_id": "bbbb92c3d61ed9f4f0e8111fb20fcf43", "shoe-images": [ { "_id": "7b547bae4ac911c6f05b97eba6cb355a" } ], "shoe-sizes": [ { "_id": "12b6289d558d7ceb5bef725091666ce5" } ] } ] } //--shoe images { "_id": "7b547bae4ac911c6f05b97eba6cb355a", "_rev": "4-4fde0cac1b4b8afc618bbba5b6669193", "type": "shoe-images", "sku": "656F-PINSEC12", "color": "Black/Houndstoot", "full-images": [ "http://www.someurl.com/full/656F-PINSEC12.jpg" ], "thumbnail-images": [ "http://www.someurl.com/thumb/656F-PINSEC12.jpg" ] } //--shoe color { "_id": "bbbb92c3d61ed9f4f0e8111fb20fcf43", "_rev": "2-e5d07c00a0261c231dd2be9b26a6c0dc", "type": "shoe-color", "sku": "656F-PINSEC12", "color": "black/houndstooth" } //--shoe sizes { "_id": "12b6289d558d7ceb5bef725091666ce5", "_rev": "2-192df709f9de1ef27e9e5f4404863bcc", "type": "shoe-sizes", "sku": "656F-PINSEC12", "shoe-color": "black/houndstooth", "shoe-sizes": [ { "size": 5, "IsSizeAvailable": true }, { "size": 6, "IsSizeAvailable": true }, { "size": 7, "IsSizeAvailable": true }, { "size": 8, "IsSizeAvailable": true }, { "size": 9, "IsSizeAvailable": true }, { "size": 10, "IsSizeAvailable": true }, { "size": 11, "IsSizeAvailable": true }, { "size": 12, "IsSizeAvailable": true }, { "size": 13, "IsSizeAvailable": true }, { "size": 14, "IsSizeAvailable": true } ] } //--map/reduce { "_id": "_design/catalog", "_rev": "4-de5baf04b485768de12d78e5a0e5aa5e", "views": { "item": { "map": "function(doc) { if (doc.type === 'catalog-item') { emit([doc.sku, doc], null); if (doc.shoe-colors) { for (var sc in doc.shoe-colors) { emit([doc.sku, Number(sc)+1], {_id: doc.shoe-colors[sc]._id}); for (var si in doc.shoe-colors[sc].shoe-images) { emit([doc.sku, Number(si)+1], {_id: doc.shoe-colors[sc].shoe-images[si]._id}); } for (var sz in doc.shoe-colors[sc].shoe-sizes) { emit([doc.sku, Number(sz)+1], {_id: doc.shoe-colors[sc].shoe-sizes[sz]._id}); } } } } }" } } } 

Perhaps this is the best way to implement this, but I wanted to see if it is possible to have a document with an array of related documents that also have an array of related documents. But my map / reduction does not return anything. All that it returns:

 {"total_rows":0,"offset":0,"rows":[ ]} 

I assume that someone will not save all the information in one document, because, say, we add a new show size or mark the shoe size as inaccessible, this would mean that we would have to return all previous values ​​for CouchDB just to update one fields.

Hope my question makes sense. Oo__oO

Answer 1

The trick with this is to take your mind off JOIN's point of view. Linked documents provide you with a technique for indexing one type of document based on the properties of another. This works using a combination of two functions:

• CouchDB allows you to specify include_docs = true when you request a view to return indexed documents along with viewing results.
• You can tell CouchDB to return ANY document by specifying the _id property as a result of the view. Please note that you can only return one document per result.

As an example, let's say you had documents

 { "_id": "111", "type", "shoe", "sku": "656F-PINSEC12", "shoe-color": "black/houndstooth", "imageId": "222" } 

and

 { "_id": "222", "type": "image", "full-images": ["http://www.someurl.com/full/656F-PINSEC12.jpg"] "thumbnail-images": ["http://www.someurl.com/thumb/656F-PINSEC12.jpg"] } 

then you can index images using SKU using the map function:

 function(doc) { if(doc.type === "shoe") { emit(doc.sku, {_id: doc.imageId }); } } 

It is also important to understand that the map function only works with the original document that was saved.

I think that in your example the documents “catalog item” and “shoe color” are redundant. You can define a map function to index “shoe image” and “shoe size” documents with SKU, for example.

 function(doc) { if(doc.SKU) { emit(doc.SKU, null); } } 

Assuming this has been assigned to the part-to-detail view, your query:

https://username.cloudant.com/test/_design/catalogue/_view/item-details?include_docs=true&key=%22656F-PINSEC12%22

must return

 { "total_rows":2, "offset":0, "rows": [ { "id":"7b547bae4ac911c6f05b97eba6cb355a", "key":"656F-PINSEC12", "value":null, "doc":{ "_id": "7b547bae4ac911c6f05b97eba6cb355a", "_rev": "4-4fde0cac1b4b8afc618bbba5b6669193", "type": "shoe-images", "sku": "656F-PINSEC12", "color": "Black/Houndstoot", "full-images": [ "http://www.someurl.com/full/656F-PINSEC12.jpg" ], "thumbnail-images": [ "http://www.someurl.com/thumb/656F-PINSEC12.jpg" ] } }, { "id":"12b6289d558d7ceb5bef725091666ce5", "key":"656F-PINSEC12", "value":null "doc":{ "_id": "12b6289d558d7ceb5bef725091666ce5", "_rev": "2-192df709f9de1ef27e9e5f4404863bcc", "type": "shoe-sizes", "sku": "656F-PINSEC12", "shoe-color": "black/houndstooth", "shoe-sizes": [ { "size": 5, "IsSizeAvailable": true }, { "size": 6, "IsSizeAvailable": true }, { "size": 7, "IsSizeAvailable": true }, { "size": 8, "IsSizeAvailable": true }, { "size": 9, "IsSizeAvailable": true }, { "size": 10, "IsSizeAvailable": true }, { "size": 11, "IsSizeAvailable": true }, { "size": 12, "IsSizeAvailable": true }, { "size": 13, "IsSizeAvailable": true }, { "size": 14, "IsSizeAvailable": true } ] } ] } 

If you want to combine these results into a single JSON document, you can see how to use the list function to create custom JSON output. However, I'm not sure that you will win much.

It looks like you would be better off with a more granular data model (for example, each shoe / size combination could be a separate document) and use map functions to aggregate data for a given SKU.