Redis multi column range query

Question

I have users as Redis Hashes and want to find similar users (given a particular user) based on salary and age.

<user>
  <id>101</id>
  <name>Neo</name>
  <age>30</age>
  <salary>300</salary>
  ....
</user>

So, in this case I need to find users who are close to my age AND salary close to my salary both within some given limits. In SQL, I would hypothetically do something like

SELECT id, abs(age - 30) as agediff, abs(salary - 300) as saldiff FROM
 USERS WHERE 
(age BETWEEN 25 35) AND (salary BETWEEN 250 350) ORDER BY agediff ASC, saldiff ASC

Can we do this, say using ZINTERSTORE, in such a way that the resulting set is ordered by user similarity like in the SQL?

Answer 1

This is not exactly as easy as a SQL query. You need set some keys etc.

Nevertheless here is what I think is the way to do it. You would need to create two sorted sets with user id as member and age/salary as score.

ZADD index:user:age 30 101 # 101 is id of user whose name is Neo
ZADD index:user:salary 300 101 

Create intermediate sets for both conditions

# Condition 1: age 25-30
ZUNIONSTORE temp:age 1 index:user:age WEIGHTS 1
ZREMRANGEBYSCORE temp:age 0 24
ZREMRANGEBYSCORE temp:age 36 INF
# Condition 2: Repeat above for salary
# Now create result set which is intersection of both above sets
ZINTERSTORE temp:result 2 temp:salary temp:age # 2 is no. of sets to intersect
# Verify result
ZRANGE temp:result 0 -1

Finally

• remove temp keys
• Run all these commands using MULTI/EXEC so that there is only one round trip