CouchDB: Linking a document that references an array of different document types

Question

I'm a newbie when it comes to CouchDB. I come from a .NET SQL Server world.

In skimming through CouchDB the Definitive Guide I was like "wut wut this is awesome". Now I'm testing some of the things I learned in hopes of implementing it in the real world.

I just signed up for a Cloudant account a couple of weeks ago and started using it for some testing/learning.

In messing with linked documents, the whole theory behind looks simple and also the strait forward examples on the internet. Where I want to retrieve some information from a document that has an array of different linked documents, whom themselves have arrays of linked documents. Like a multi SQL Server that joins to many to many relationship tables. You'll see the code bellow. Hopefully it makes sense.

Take for example this SQL Query. Assuming that there is only one entry in each of the tables we should get one record back with all of the details for a shoe with a given sku. But if we had multiple shoe sizes we would have to write some more 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 following JSON by SKU at 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});
                        }
                      }
                    }
                  }
                }"
       }
   }
}

There might be a better way of implementing this but I wanted to see if it was possible to have a document that has an array of linked documents whom also have an array of linked documents. But my map/reduce isn't returning anything. All it returns is:

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

]}

I'm guessing that someone would not store all the information on a single document because, say we add a new show size or mark the shoe size as not available, this would mean one would have to pass back all the previous values to CouchDB just to update one field.

Hopefully my question makes sense Oo__oO

Answer 1

The trick with this is get away from thinking in terms of JOINs. Linked documents give you a technique to index one type of document based on properties of another. This works using a combination of two features:

1. CouchDB allows you to specify include_docs=true when querying a view to return the indexed documents along with the view results.
2. You can tell CouchDB to return ANY document by specifying an _id property in the view result. Note that you can still only return one document per result.

As an example, lets 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 could index images by SKU using the map function:

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

It's also important to realise that the map function only operates on the original document that was saved.

I think in your example, the "catalog item" and "shoe color" documents are redundant. You could define a map function to index the "shoe image" and "shoe size" documents by SKU e.g.

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

Assuming this was assigned to the view "item-details", your query:

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

should 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 wanted to combine these results into a single JSON document, you could look at using a list function to generate custom JSON output. However, I'm not sure that gains you much.

It looks like you'd generally be better off with a more granular data model (e.g. each shoe / size combination could be a separate doc) and use map functions to aggregate the data for a given SKU.