Advanced filtering and sorting with redis (part 1)

Set and sorted set are extremely powerful data types for filtering and sorting stuff with redis.

Basic filtering

Let’s start with something simple. Usually filtering is just a matter of union and intersection. Let’s say: filter all hotels that are 3 or 4 star and have both spa and pool.

For this, we just have to create a set for each of the filter criteria and do union/intersection accordingly.

Suppose we have the following data

hotel id star rating has spa has pool
1 3 yes no
2 3 yes yes
3 3 no no
4 3 no no
5 4 yes yes
6 4 no no
7 4 no no
8 4 no no

Group those item by the property you want to do filter on

sadd hotel:star:3 1 2 3 4
sadd hotel:star:4 5 6 7 8
sadd hotel:spa 1 2 5
sadd hotel:pool 2 5

As with the above example, it would be [UNION of (3,4 star sets)] INTERSECTION [ INTERSECTION of [spa, pool]]

SUNIONSTORE 3or4star hotel:star:3 hotel:star:4
SINTERSTORE spaandpool hotel:spa hotel:pool
SINTER 3or4star spaandpool
# 2 5

And you got hotel id 2 and 5 as the result.

Mutliple columns sorting

Usually, in SQL, you can do multi columns sorting like this

SELECT * FROM mytable
ORDER BY col1 ASC, col2 ASC, col3 DESC

How would you translate this logic to redis?

Actually, this is not my idea but Josiah Calrson’s (author of Redis in Action book). You can find his blog post about this and demo implementation there as well.

The basic idea is: ZINTERSTORE command supports WEIGHTS so we just have to calculate the weight for each column base on their order and sorting direction (ASC, DESC).

If you know the range of the filter criteria in advance, you can save 1 round trip to redis to fetch it.

for sort_col in sort:
    pipe.zrange(sort_col, 0, 0, withscores=True)
    pipe.zrange(sort_col, -1, -1, withscores=True)
ranges = pipe.execute()

One thing to note is that this approach doesn’t work well with non-integer values in mind. You can work around that by converting them to integer. For example, you can convert a non-integer values range from 0 to 10 with precision of 2 by multiplying the value with 100. Something like below:

function normalize(val, precision) {
    return Math.ceil(val * 10 ** precision)