CODEWITHSUNDEEP
lucenecloudant
noob
noob

Reputation: 490

Search one doc per user on Cloudant

I have a database containing multiple docs. Each doc has a field of user_id. Assuming these docs belong to two users: 1 and 2. I would like to extract one latest doc only for each user. How should I implement the search index? I now can get a user's all docs by using: user_id:1 But how can I limit doc number to one for each user in one query?

Upvotes: 0

Views: 46

Answers (1)

markwatsonatx
markwatsonatx

Reputation: 3501

Assuming you have a date property on each doc you could accomplish this as follows:

Create the following index:

{
   "index": {
      "fields": [
         {"user_id":"desc"},
         {"date_field":"desc"}
      ]
   },
   "type": "json"
}

To query for the latest doc for user_id == "1" your query would look like this:

{
   "selector": {
      "user_id": "1"
   },
   "fields": [
      "_id",
      "user_id",
      "date_field"
   ],
   "sort": [
      { "user_id": "desc" },
      { "date_field": "desc" }
   ],
   "limit":1
}

If you are trying to return multiple users in a single query then you would have to do something like this:

  1. Add a property called latest or similar

  2. Add an index on user_id and latest:

    {
   "index": {
      "fields": [
         "user_id",
         "latest"
      ]
   },
   "type": "json"
}

  3. Whenever you add a new document find the document for that user_id where latest is true:

    {
   "selector": {
      "user_id": "1",
      "latest" : true
   },
   "fields": [
      "_id",
      "user_id",
      "latest"
   ]
}

  4. Set latest = false on that document

  5. Insert the new document with latest = true

  6. This query will find the latest doc for all users:

    {
   "selector": {
      "user_id": {"$gt":null},
      "latest" : true
   },
   "fields": [
      "_id",
      "user_id",
      "latest"
   ]
}

Upvotes: 0

Related Questions