How to use RediSearch and RedisJSON to query documents based on a condition (comparison operators)?

Question

I have the following data stored as json using RedisJSON and I want to query a particular set of rows based on input criteria using RediSearch.

Ex Data:

{
    "Ticker": "AAPL",
    "Name": "Apple Inc.",
    "returns": [
        { "Range": { "Begin": "9/6/2014", "End": "4/5/2020"}, "Value": "0.0231"},
        { "Range": { "Begin": "11/12/2011", "End": "14/9/2021"}, "Value": "1.455"},
        { "Range": { "Begin": "1/2/2016", "End": "25/7/2022"}, "Value": "0.436"},
        { "Range": { "Begin": "24/6/2012", "End": "18/5/2016"}, "Value": "2.756"},
        { "Range": { "Begin": "14/8/2017", "End": "8/7/2020"}, "Value": "0.945"},
        { "Range": { "Begin": "9/12/2019", "End": "22/10/2015"}, "Value": "4.256"},
    ]
}

For example if the input given is "1/1/2015", then I would like to fetch only the records which have "Range.Begin" less than the input value. So it should return the following rows from the json document.

{ "Range": { "Begin": "9/6/2014", "End": "4/5/2020"}, "Value": "0.0231"},
{ "Range": { "Begin": "11/12/2011", "End": "14/9/2021"}, "Value": "1.455"},
{ "Range": { "Begin": "24/6/2012", "End": "18/5/2016"}, "Value": "2.756"}

How can I create an index using RediSearch with such a requirement?

Note: All the values stored in json are strings. And it might not be possible to do a comparison on strings in this case. So, please let me know if the comparison works if dates are changed to numerical values like "11122011" from "11/12/2011".

Answer 1

First you need to create an index that will index @Begin.

e.g.

 FT.CREATE myidx ON JSON PREFIX 1 ticker: SCHEMA $.Ticker.returns[*].Range.Begin AS begin TEXT

Then you can utilize this index on your query and last project only the relevant parts.

e.g.

> ft.search myidx '@begin:24/6/2012' RETURN 3 "$.returns[?(@.Range.Begin=='24/6/2012')]" as d DIALECT 3
1) (integer) 1
2) "ticker:apple"
3) 1) "d"
   2) "[{\"Range\":{\"Begin\":\"24/6/2012\",\"End\":\"18/5/2016\"},\"Value\":\"2.756\"}]"