couchbase startkey_docid and complex key

Question

The call below with the matching result set returns a result that does not start with the doc id a4a6cf44-8a82-494a-a2b9-f6a3ec629f17. As the result set below demonstrates the top 3 keys are identical, but startkey_docid has no effect.

Just a couple questions around this

Will startkey_docid work correctly with a complex key? (This apparently works for couchdb)
If yes to the above, is the source of the issue the use of dateToArray(doc.created) in our view?
Is this a bug?

View:

function (doc, meta) 
{
  if(meta.type == "json" && doc.type == "POST") 
  {
    emit([doc.category, dateToArray(doc.created), doc.visibility], null);
  }
}

Call:

?startkey=["auto",[2013,10,10,23,12,0],"EVERYONE"]&endkey=["auto",[2013,12,11,23,12,0],"EVERYONE"]&startkey_docid=a4a6cf44-8a82-494a-a2b9-f6a3ec629f17

Result:

{
    total_rows: 20,
    rows: [{
        id: "a4a6cf44-8a82",
        key: ["auto", [2013, 11, 8, 1, 17, 46], "EVERYONE"],
        value: null
    }, {
        id: "a4a6cf44-8a82-494a-a2b9",
        key: ["auto", [2013, 11, 8, 1, 17, 46], "EVERYONE"],
        value: null
    }, {
        id: "a4a6cf44-8a82-494a-a2b9-f6a3ec629f17",
        key: ["auto", [2013, 11, 8, 1, 17, 46], "EVERYONE"],
        value: null
    }, {
        id: "41070cfc-a85c-424c-9b87-fce0616c77c1",
        key: ["auto", [
        2013, 11, 11, 20, 28, 21], "EVERYONE"],
        value: null
    }, {
        id: "bb275e3c-54da-4e85-8cc3-21defff4e278",
        key: ["auto", [
        2013, 11, 13, 1, 41, 7], "EVERYONE"],
        value: null
    }]
}

Example data set. post_id is the document id.

{
    type: "POST",
    post_id: "a4a6cf44-8a82",
    visibility: "EVERYONE",
    userID: "<user_id>",
    title: "Some title 1",
    category: "auto",
    description: "",
    created: "2013-11-07 17:17:46 -0800",
    modified: "2013-11-07 17:17:46 -0800"
}, {
    type: "POST",
    post_id: "a4a6cf44-8a82-494a-a2b9",
    visibility: "EVERYONE",
    userID: "<user_id>",
    title: "Some title 2",
    category: "auto",
    description: "",
    created: "2013-11-07 17:17:46 -0800",
    modified: "2013-11-07 17:17:46 -0800"
}, {
    type: "POST",
    post_id: "a4a6cf44-8a82-494a-a2b9-f6a3ec629f17",
    visibility: "EVERYONE",
    userID: "<user_id>",
    title: "Some title 3",
    category: "auto",
    description: "",
    created: "2013-11-07 17:17:46 -0800",
    modified: "2013-11-07 17:17:46 -0800"
}, {
    type: "POST",
    post_id: "41070cfc-a85c-424c-9b87-fce0616c77c1",
    visibility: "EVERYONE",
    userID: "<user_id>",
    title: "Some title 4",
    category: "auto",
    description: "",
    created: "2013-11-11 12:28:21 -0800",
    modified: "2013-11-11 12:28:21 -0800"
}, {
    type: "POST",
    post_id: "bb275e3c-54da-4e85-8cc3-21defff4e278",
    visibility: "EVERYONE",
    userID: "<user_id>",
    title: "Some title 5",
    category: "auto",
    description: "",
    created: "2013-11-12 17:41:07 -0800",
    modified: "2013-11-12 17:41:07 -0800"
}

It might be sensitive to the format of the `startkey_docid`, it must not be json-encoded like `startkey`. In my test, these two URLs give different results: http://node.avsej.net:8092/default/_design/test/_view/test?startkey=%22auto%22&startkey_docid=%22a4a6cf44-8a82-494a-a2b9-f6a3ec629f17%22 and http://node.avsej.net:8092/default/_design/test/_view/test?startkey=%22auto%22&startkey_docid=a4a6cf44-8a82-494a-a2b9-f6a3ec629f17 — avsej, Nov 20 '13 at 12:13
Interesting. I noticed you are not using a complex key though. Does it still work if you use a complex key? What version of Couchbase are you using? — Michael, Nov 20 '13 at 17:30
if you will dump some dataset into json, I can check it easily. I mean add it into the question — avsej, Nov 20 '13 at 21:20
Sorry Avsej what are you asking for? The data set for the above result set from the view? — Michael, Nov 20 '13 at 21:53
Yes, so that it would be easier to run the same query to reproduce the case — avsej, Nov 21 '13 at 09:06
Thanks to you two I've finally understand how this `startkey_docid` actually works :). As I understand it works in the same way as startkey/endkey as described [here](http://www.couchbase.com/forums/thread/couchbase-map-function-ignores-long-values-startkey-and-endkey#comment-1009925), but it's simply analyzes string instead of array (in case of complex keys). In @avsej example data is oredered by doc.id, and in your example it's ordered by date. May be this is the reason why it's not working as expected. — m03geek, Nov 21 '13 at 13:32

score 1 · Answer 1 · edited May 23 '17 at 12:24

Okay, this is an old thread, but for reference sake, I'll post my findings and try to explain this functionality the best I can.

Example

Given a database of over 9,000 movie documents, each containing attributes like title, year, rating, genre, etc., define a view MovieIndexView with index name MovieIndex for this database that emits the following complex (but rather simple) key for filtering movies by rating, and sorting by year:

function (doc) {
    emit([doc.rating, doc.year], doc);
}

The view can be queried with sorted rows using techniques similar to the way the OP has shown:

- Request -
GET /movies_db/_design/MovieIndex/_view/MovieIndexView?
startkey=["R",{}]
&endkey=["R",0]
&descending=true
&limit=6

- Response -
{
    "total_rows": 9411,
    "offset": 1,
    "rows": [
        {
            "id": "2802144",
            "key": [
                "R",
                2014
            ],
            "value": {
                "_id": "2802144",
                "_rev": "1-924e12ba1f1144e3a453bbd8978acc5c",
                "title": "Kingsman: The Secret Service",
                "year": 2014,
                "rating": "R",
                "runtime": "129 min",
                "genre": [
                    "Action",
                    "Adventure",
                    "Comedy"
                ],
                "director": "Matthew Vaughn"
            }
        },
        <...>
        {
            "id": "2278388",
            "key": [
                "R",
                2014
            ],
            "value": {
                "_id": "2278388",
                "_rev": "1-c38b7f5eb43abfd59fb8514277290e46",
                "title": "The Grand Budapest Hotel",
                "year": 2014,
                "rating": "R",
                "runtime": "99 min",
                "genre": [
                    "Adventure",
                    "Comedy",
                    "Drama"
                ],
                "director": "Wes Anderson"
            }
        }
    ]
}

Note we only pulled the first 6 documents by setting the limit variable (I pasted only the first and last relevant items).

Pagination

A first shot at pagination for the next sequence of rows could be straight forward. Simply set the startkey of the next request to the key value of the last item in the previous set, while keeping the value for endkey unchanged:

startkey=["R",2014]
&endkey=["R",0]
&descending=true
&limit=6

Do you see a problem with this approach? This new start key fails to specify which of the multiple movie items with the same value for startkey (movies with the same rating, that came out the same year) the response should start with. While there is nothing syntactically wrong with the request, you will notice that the returned rows will not start with the next sequential item we want, but rather with another arbitrary item in the database with a matching value for key.

This is where startkey_docid comes in handy:

- Request -
GET /movies_db/_design/MovieIndexView/_view/MovieIndex?
startkey=["R",2014]
&endkey=["R",0]
&descending=true
&limit=6
&startkey_docid=2278388

This request will return rows starting with the correct item in the sequence, providing the best means of paginating the rows from the view.

The Takeaway

A startkey_docid must be used together with a relevant startkey for your pagination to work.

I hope this helps anyone who may be busting their heads on this subtle feature.

Sources

CouchDB Official Docs: http://docs.couchdb.org/en/latest/couchapp/views/collation.html

Interesting thread exchange: http://grokbase.com/t/couchdb/user/091defx51x/sort-by-date-and-find-by-key

Similar SO topic: https://stackoverflow.com/questions/10142850/need-a-couchdb-trick-to-sort-by-date-and-filter-by-group#=

score 0 · Answer 2 · edited Jul 05 '17 at 19:42

0

You can find more information on how to use startkey_docid properly here: http://blog.couchbase.com/startkeydocid-behaviour

edited Jul 05 '17 at 19:42

Vadim Kotov

8,084
8
48
62

answered Nov 26 '13 at 17:52

Brett L

124
2
5

1

Can you explain further how it applies to this example? The first 3 keys are identical – Michael Nov 26 '13 at 18:28

couchbase startkey_docid and complex key

2 Answers2

Example