Monday, November 21, 2016

Rethinkdb replace multiple fields with object and array as nested field type

I came across a requirement where I need to replace multiple fields in a document. In my case the document has two fields, one as object and other as array. What was needed is modify the nested array and object in the same query.

Let’s take an example document for this case-
{
 "badgeCount" : {
  "09cf79ad-cce7-4826-8a66-e0653eabae4e" : 0,
  "5cdf9a50-b4e3-4240-8ddc-979b25820745" : 1
 },
 "createdDate" : new Date(),
 "deleted" : false,
 "roomName" : "my room",
 "description" : "my room description",
 "id" : "c58f3c08-4d84-41c7-b705-88cd081dfa04",
 "joinedUserIds" : [
  "09cf79ad-cce7-4826-8a66-e0653eabae4e",
  "5cdf9a50-b4e3-4240-8ddc-979b25820745"
 ]
}
This document is about a room, where a user can join a room via joinedUserIds and they have badgeCount which says how many new messages are there in the room. Each item in the array joinedUserIds is a use id and there is a property of the same user id in the badgeCount object.

So, in my case what was needed, when a user leaves a room we need to remove the user id from joinedUserIds and also from badgeCount.

Solution goes like this-
r.db('test').table('room').get("c58f3c08-4d84-41c7-b705-88cd081dfa04").replace(function (s) {
 return s.without({
  badgeCount : {
   "09cf79ad-cce7-4826-8a66-e0653eabae4e" : true
  }
 }).without("joinedUserIds").merge({
  joinedUserIds : s("joinedUserIds").filter(function (id) {
   return id.ne("09cf79ad-cce7-4826-8a66-e0653eabae4e");
  })
 })
})
We have solved this by replace with chained without. First without is removing 09cf79ad-cce7-4826-8a66-e0653eabae4e from badgCount. Result of first without is, it will remove 09cf79ad-cce7-4826-8a66-e0653eabae4e from badgeCount object. Second without removes joinedUserIds and then adds it back with merge and filter.

Tuesday, April 26, 2016

SQL like IN clause in RethinkDB

Let’s consider a simple example of product. We need to get all product where product ids in a given list. A product document goes like this-
[
      {
         “ID”:0,
         “Name”:”Bread”,
         “Description”:”Whole grain bread”,
         “ReleaseDate”:”1992-01-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:4,
         “Price”:”2.5″
      },
      {
         “ID”:1,
         “Name”:”Milk”,
         “Description”:”Low fat milk”,
         “ReleaseDate”:”1995-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”3.5″
      },
      {
         “ID”:2,
         “Name”:”Vint soda”,
         “Description”:”Americana Variety – Mix of 6 flavors”,
         “ReleaseDate”:”2000-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”20.9″
      },
   …
   ]
An equivalent SQL statement in this case will be –
SELECT ID, Name, Description from Products WHERE ID IN (1,2)
To implement this we can use filter with expr and map like below-
r.table('products').filter(function(product) {
        return r.expr([1,2]).contains(product('ID'))
    }).map(function (product) {
        return {
            id : product('ID'),
            name : product('Name'),
            description: product('Description')
        }
    });