Redis - Sorted set, find item by property value

Question

In redis I store objects in a sorted set. In my solution, it's important to be able to run a ranged query by dates, so I store the items with the score being the timestamp of each items, for example:

#   Score   Value
0   1443476076  {"Id":"92","Ref":"7ADT","DTime":1443476076,"ATime":1443901554,"ExTime":0,"SPName":"7ADT33CFSAU6","StPName":"7ADT33CFSAU6"}
1   1443482969  {"Id":"11","Ref":"DAJT","DTime":1443482969,"ATime":1443901326,"ExTime":0,"SPName":"DAJTJTT4T02O","StPName":"DAJTJTT4T02O"}

However, in other situations I need to find a single item in the set based on it's ID. I know I can't just query this data structure as if it were a nosql db, but I tried using ZSCAN, which didn't work.

ZSCAN MySet 0 MATCH Id:92 count 1

It returns; "empty list or set"

Maybe I need to serialize different? I have serialized using Json.Net.

How, if possible, can I achieve this; using dates as score and still be able to lookup an item by it's ID?

Many thanks,

Lars

Edit:

Assume it's not possible, but any thoughts or inputs are welcome:

Ref: http://openmymind.net/2011/11/8/Redis-Zero-To-Master-In-30-Minutes-Part-1/

In Redis, data can only be queried by its key. Even if we use a hash, we can't say get me the keys wherever the field race is equal to sayan.

Edit 2:

I tried to do:

ZSCAN MySet 0 MATCH *87*

127.0.0.1:6379> ZSCAN MySet 0 MATCH *87*
1) "192"
2) 1) "{\"Id\":\"64\",\"Ref\":\"XQH4\",\"DTime\":1443837798,\"ATime\":1444187707,\"ExTime\":0,\"SPName\":\"XQH4BPGW47FM\",\"StPName\":\"XQH4BPGW47FM\"}"
   2) "1443837798"
   3) "{\"Id\":\"87\",\"Ref\":\"5CY6\",\"DTime\":1443519199,\"ATime\":1444172326,\"ExTime\":0,\"SPName\":\"5CY6DHP23RXB\",\"StPName\":\"5CY6DHP23RXB\"}"
   4) "1443519199"

And it finds the desired item, but it also finds another one with an occurance of 87 in the property ATime. Having more unique, longer IDs might work this way and I would have to filter the results in code to find the one with the exact value in its property.

Still open for suggestions.

Answer 1

I think it's very simple.

Solution 1(Inferior, not recommended)

Your way of ZSCAN MySet 0 MATCH Id:92 count 1 didn't work out because the stored string is "{\"Id\":\"92\"... not "{\"Id:92\".... The string has been changed into another format. So try to use MATCH Id\":\"64 or something like that to match the json serialized data in redis. I'm not familiar with json.net, so the actual string leaves for you to discover.

By the way, I have to ask you did ZSCAN MySet 0 MATCH Id:92 count 1 return a cursor? I suspect you used ZSCAN in a wrong way.

Solution 2(Better, strongly recommended)

ZSCAN is good when your sorted set is not large and you know how to save network roundtrip time by Redis' Lua transaction. This still make "look up by ID" operation O(n). Therefore, a better solution is to change you data model in the following way:

change sorted set from

#   Score   Value
0   1443476076 {"Id":"92","Ref":"7ADT","DTime":1443476076,"ATime":1443901554,"ExTime":0,"SPName":"7ADT33CFSAU6","StPName":"7ADT33CFSAU6"}
1   1443482969 {"Id":"11","Ref":"DAJT","DTime":1443482969,"ATime":1443901326,"ExTime":0,"SPName":"DAJTJTT4T02O","StPName":"DAJTJTT4T02O"}

to

#   Score   Value
0   1443476076 Id:92
1   1443482969 Id:11

Move the rest detailed data in another set of hashes type keys:

#   Key   field-value field-value ...
0   Id:92 Ref-7ADT DTime-1443476076 ...
1   Id:11 Ref-7ADT DTime-1443476076 ...

Then, you locate by id by doing hgetall id:92. As to ranged query by date, you need do ZRANGEBYSCORE sortedset mindate maxdate then hgetall every id one by one. You'd better use lua to wrap these commands in one and it will still be super fast!

Data in NoSql database need to be organized in a redundant way like above. This may make some usual operation involve more than one commands and roundtrip, but it can be tackled by redis's lua feature. I strongly recommend the lua feature of redis, cause it wrap commands into one network roundtrip, which are all executed on the redis-server side and is atomic and super fast!

Reply if there's anything you don't know