CODEWITHSUNDEEP
scalaindexingcouchbase
Developus
Developus

Reputation: 1462

How to filter dates in Couchbase and Scala

I have a simple json:

{
 "id": 1,
 "name": "John",
 "login": "2019-02-13"
}

This kind of documents are stored in Couchbase, but for now I would like to create index (or list in some other, well time way) which should filter all documents where login is older then 30 days. How should I create it in Couchbase and get this in Scala?

For now I get all documents from database and filter them in API, but I think it is not very good way. I would like to filter it on database side and retrieve only documents which have login older then 30 days.

Now, in Scala I have only the method only to get docs by id:

bucket.get(id, classOf[RawJsonDocument])

Upvotes: 1

Views: 245

Answers (2)

vsr
vsr

Reputation: 7414

The following query is more efficient because it can push the predicate to IndexScan when index key matched with one side of predicate relation operator. If you have expression that derives from index key, it gets all the values and filter in the query engine.

CREATE INDEX ix_login_date ON mybucket (login);
SELECT u.*
FROM mybucket AS u
WHERE u.login < DATE_ADD_STR(NOW_STR(), 'day', -30) ;

Upvotes: 2

Matthew Groves
Matthew Groves

Reputation: 26151

I would recommend taking a look at N1QL (which is just SQL for JSON). Here's an example:

SELECT u.*
FROM mybucket u
WHERE DATE_DIFF_STR(NOW_STR(), login, 'day') > 30;

You'll also need an index, something like:

CREATE INDEX ix_login_date ON mybucket (login);

Though I can't promise that's the best index, it will at least get you started.

I used DATE_DIFF_STR and NOW_STR, but there are other ways to manipulate dates. Check out Date Functions in the documentation. And since you are new to N1QL, I'd recommend checking out the interactive N1QL tutorial.

Upvotes: 2

Related Questions