RethinkDB indexing two fields with time

Question

I have RethinkDB with data/table let say "news" with huge number of data row :

[
  {
    "author": "author1",
    "category_id": "business",
    "country": "id",
    "created_at": "Wed Aug 15 2018 09:26:52 GMT+07:00",
    "description": "description",
    "id": "74c25662-7f94-47ef-8a7e-5091924819a9"
  },

  {
    "author": "author2",
    "category_id": "business",
    "country": "id",
    "created_at": "Wed Aug 15 2018 09:26:52 GMT+07:00",
    "description": "description2",
    "id": "74c25662-7f94-47ef-8a7e-5091924819a9"
  },

  {
    "author": "author3",
    "category_id": "sport",
    "country": "id",
    "created_at": "Wed Aug 15 2018 09:26:52 GMT+07:00",
    "description": "description3",
    "id": "74c25662-7f94-47ef-8a7e-5091924819a9"
  },

  {
    "author": "author3",
    "category_id": "business",
    "country": "id",
    "created_at": "Wed Aug 15 2018 09:26:52 GMT+07:00",
    "description": "description4",
    "id": "74c25662-7f94-47ef-8a7e-5091924819a9"
  }
  .....
]

I need to create index for category_id and created_at (timestamp) and query for certain category and filter by day now (certain date) only. I want to optimize and speed up for query result

I can do it in javascript by filter like this for category_id business and day 15 :

r.table("news").filter({category_id: 'business'}).filter(
    r.row("created_at").day().eq(15)
)

But how I can create index for category_id and created_at and query it by certain day of created_at.

r.table("news").indexCreate( ???

thanks

Answer 1

Should be something like this:

r.table('news').indexCreate('businessAndDate', function (doc) {
  return doc('category_id').add(doc('created_at').day().coerceTo('string'));
});

Then you can:

r.table('news').getAll('business15', {index: 'businessAndDate'})

You may want to insert a separator between the data (like 'business-15'), and use more than just the day (otherwise, why bother having a full timestamp?), that's up to you and how many .add you're willing to write. ;) According to the reference, this is called an arbitrary index.