not able to query with numeric fields

Hello,

I have a usecase and wanted to know if redis search would be a good fit.

I
have a list of articles, each of which belong to a category, publication, and has a timestamp. A user can select a category, publication(s), and can check out the latest articles. Further, the user
can scroll to the next set of articles before that time.The sql query for that essentially boils down to

select * from articles where catid=1 and publication in (11, 12) where timestamp < 'timestamp when user visited last article' order by timestamp desc limit 10

So, i tried creating a very simple index, with numeric fields.

127.0.0.1:6379> FT.CREATE myIdx SCHEMA catid NUMERIC pubid NUMERIC ts NUMERIC SORTABLE json TEXT
OK
127.0.0.1:6379> FT.ADD myIdx doc1 1.0 FIELDS catid 1 pubid 11 ts 123456 json value1
OK
127.0.0.1:6379> FT.ADD myIdx doc2 1.0 FIELDS catid 1 pubid 12 ts 123457 json value2
OK
127.0.0.1:6379> FT.ADD myIdx doc3 1.0 FIELDS catid 2 pubid 12 ts 123458 json value3
OK
127.0.0.1:6379> FT.SEARCH myIdx @pubid:[10 13]
(error) 13]
127.0.0.1:6379> FT.SEARCH myIdx @pubid:[10]
(error) Syntax error at offset 10 near ‘10’
127.0.0.1:6379> FT.SEARCH myIdx @pubid:10

  1. (integer) 0
    127.0.0.1:6379> FT.SEARCH myIdx @pubid:11
  2. (integer) 0
    127.0.0.1:6379> FT.SEARCH myIdx @json:value3
  3. (integer) 1
  4. “doc3”
    1. “catid”
    2. “2”
    3. “pubid”
    4. “12”
    5. “ts”
    6. “123458”
    7. “json”
    8. “value3”
      127.0.0.1:6379>

As the above shows i am not able to query with the numeric fields(pubid=11). It is probably something silly mistake.

Hi Tuco-

Classic mistake. You were on the right track - just add in the quote marks because the query is a single argument.

FT.SEARCH myIdx “@pubid:[10 13]”

If you want to find by an exact value then set the upper and lower bounds to the same value:

FT.SEARCH myIdx “@pubid:[11 11]”

Hope this helps!

Kyle

Thanks Kyle, it worked.

Also, should the IN clause in a SQL be implemented using the multiple OR conditions?

The below query seems to work well for me.

FT.SEARCH myIdx “(@catid:[1 1] | @catid:[2 2]) @pubid:[12 12] @ts:[123455 inf]” RETURN 2 json ts SORTBY ts DESC LIMIT 0 2

It is essentially “WHERE catid in (1, 2) AND pubid=12 AND ts > 123455”

Thanks