RethinkDB grouping and defining output

Question

I have a table that contains documents that look similar to this:

{
"title": "title 2",
"content": "This far, no further!",
"category": "Fiction"
}

This is the query I'm using:

r.table('posts').group('title').map(lambda item:
    {'items': item['title']}
).run())

This is the output:

{
  "title 1" : [
    {
      "items" : "title 1"
    },
    {
      "items" : "title 1"
    }
  ],
  "Title 2" : [
    {
      "items" : "Title 2"
    },
    {
      "items" : "Title 2"
    },
    {
      "items" : "Title 2"
    },
    {
      "items" : "Title 2"
    }
  ],
  "title 3" : [
    {
      "items" : "title 3"
    }
  ]
}

However I would like to get an output structure that looks like this:

{
  "Title 1" : {
    "item_count" : 3,
    "items" : [
      {
        "items" : "title 1"
      },
      {
        "items" : "title 1"
      },
      {
        "items" : "title 1"
      }
    ]
  },
  "Title 2" : {
    "item_count" : 2,
    "items" : [
      {
        "items" : "title 2"
      },
      {
        "items" : "title 2"
      }
    ]
  },
  "Title 3" : {
    "item_count" : 1,
    "items" : [
      {
        "items" : "title 3"
      }
    ]
  }
}

How would I create a query to get to this result.

Answer 1

In order to do that, you need to map your query after you executing ungroup in it.

First, the output of your first query looks incorrect. RethinkDB doesn't wouldn't return an object/dictionary after a map operation, it would return an array. The output I got from your query was the following:

[
  {
    "group":  "title 1" ,
    "reduction": [
      {
        "items":  "title 1"
      } ,
      {
        "items":  "title 1"
      }
    ]
  } ,
  {
    "group":  "title 2" ,
    "reduction": [
      {
        "items":  "title 2"
      } ,
      {
        "items":  "title 2"
      }
    ]
  } ,
  ...
]

If what you want is a property called items along with a count of how many items that items property has, you can do the following:

// THIS IS JAVASCRIPT
r.table('posts')
 .group('title')
 .map(function (item) {
    return {
      items: item('title')
    }
 })
 .ungroup()
 .map(function (row) { 
    return {
      'title': row('group'),
      'items': row('reduction'),
      'count': row('reduction').count()
    }
  })

The result of that query will be the following:

[
  {
    "count": 2 ,
    "items": [
      {
        "items":  "title 1"
      },
      {
        "items":  "title 1"
      }
    ],
    "title":  "title 1"
    },
  {
    "count": 2 ,
    "items": [
      {
        "items":  "title 2"
      } ,
      {
        "items":  "title 2"
      }
    ] ,
    "title":  "title 2"
  },
  ...
]

If you want to then turn that query into an object/dictionary so that you can call up a post by it's title, you can turn that array of posts into an object, by using r.object along with r.args. The complete query will look like this:

// THIS IS JAVASCRIPT
r.table('posts')
 .group('title')
 .map(function (item) {
    return {
      items: item('title')
    }
 })
 .ungroup()
 .map(function (row) {
    return {
      'title': row('group'),
      'items': row('reduction'),
      'count': row('reduction').count()
    }
  })
  .do(function (rows) {
    return r.object(r.args(rows.concatMap(function (row) {
      return [row('title'), row];
    })));
  })

The result of that query will be something like this:

{
  "title 1": {
    "count": 2 ,
    "items": [
      {
        "items":  "title 1"
      } ,
      {
        "items":  "title 1"
      }
    ] ,
    "title":  "title 1"
  } ,
  "title 2": {
    "count": 2 ,
    "items": [
      {
        "items":  "title 2"
      } ,
      {
        "items":  "title 2"
      }
    ] ,
    "title":  "title 2"
  } ,
  ...
}