Query with join between documents

Question

I would like to create a view in CouchDb which contains some fields from multiple linked documents. My documents are something like this:

/*Products:*/
{
    "_id": "Products:ABC",
    "doctype": "Products",
    "productCode": "ABC",
    "description": "The best product you ever seen",
    "category_id": "Categories:1",
    "brand_id": "Brands:52"
},
{
    "_id": "Products:DEF",
    "doctype": "Products",
    "productCode": "DEF",
    "description": "DEFinitely a good product",
    "category_id": "Categories:2",
    "brand_id": "Brands:53"
},

/*Categories*/
{
    "_id": "Categories:1",
    "categoryID": "1",
    "description": "Awesome products"
},
{
    "_id": "Categories:2",
    "categoryID": "2",
    "description": "Wonderful supplies"
},

/*Brands*/
{
    "_id": "Brands:52",
    "brandID": "52",
    "description": "Best Items"
},
{
    "_id": "Brands:53",
    "brandID": "53",
    "description": "Great Gadgets"
},

I would like to have a result like this:

/*View results: */
{
    "id": "Products:ABC",
    "key": "Products:ABC",
    "value": {
        "productCode": "ABC",
        "description": "The best product you ever seen",
        "category": {
            "categoryID": "1",
            "description": "Awesome products"
        },
        "brand": {
            "brandID": "52",
            "description": "Best Items"
        }
    }
},
{
    "id": "Products:DEF",
    "key": "Products:DEF",
    "value": {
        "productCode": "DEF",
        "description": "DEFinitely a good product",
        "category": {
            "categoryID": "2",
            "description": "Wonderful supplies"
        },
        "brand": {
            "brandID": "53",
            "description": "Great Gadgets"
        }
    }
},

The goal is to have a result that is a join between the three documents. Is it possibile?

As you can imagine I come from the SQL world, so maybe I am designing the database terribly wrong, so any advice on how to change the documents structure is welcome!

Thanks in advance!

Francesco

Have a look here: http://stackoverflow.com/questions/28339910/how-can-i-translate-my-sql-queries-to-cloudant-couchdb/28339911#28339911 — Chris Snow, Feb 20 '15 at 13:02

score 2 · Accepted Answer · answered Feb 20 '15 at 13:05

There are two ways to do this:

Use the query() API and linked documents (search the page for "linked documents")
Use the relational-pouch plugin

The advantage of the relational-pouch plugin is that, under the hood, it is faster than linked documents because it doesn't rely on building up a map/reduce index. The advantage of linked documents is that it is the more traditional way of solving this problem in CouchDB, and it doesn't rely on an extra plugin. Choose whichever one you like. :)

Edit: re-reading your question, I see you want to join 3 different document types together. Currently that is not possible with linked documents (you can only "join" two types), whereas it is possible with relational-pouch. So I guess that makes the decision easy. :)

Query with join between documents

1 Answers1