I have an issue when I want to write a query against nested arrays on RedisSearch.
I have stored many keys; a sample of those values is JSON:
{
"Id": "100000",
"RoomName": "Electronics",
"DailySpecs": [
{
"RoomId": "100000",
"DateTime": "2023-05-26T00:00:00+03:30",
"DateTimeLong": 1685046600,
"BeforeDiscountAmount": 19586000,
"AfterDiscountAmount": 7834400,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-05-27T00:00:00+03:30",
"DateTimeLong": 1685133000,
"BeforeDiscountAmount": 149579000,
"AfterDiscountAmount": 74789500,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-05-28T00:00:00+03:30",
"DateTimeLong": 1685219400,
"BeforeDiscountAmount": 13934000,
"AfterDiscountAmount": 9753800,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-05-29T00:00:00+03:30",
"DateTimeLong": 1685305800,
"BeforeDiscountAmount": 176372000,
"AfterDiscountAmount": 141097600,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-05-30T00:00:00+03:30",
"DateTimeLong": 1685392200,
"BeforeDiscountAmount": 22503000,
"AfterDiscountAmount": 20252700,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-05-31T00:00:00+03:30",
"DateTimeLong": 1685478600,
"BeforeDiscountAmount": 78353000,
"AfterDiscountAmount": 31341200,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-01T00:00:00+03:30",
"DateTimeLong": 1685565000,
"BeforeDiscountAmount": 195242000,
"AfterDiscountAmount": 136669400,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-02T00:00:00+03:30",
"DateTimeLong": 1685651400,
"BeforeDiscountAmount": 72294000,
"AfterDiscountAmount": 50605800,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-03T00:00:00+03:30",
"DateTimeLong": 1685737800,
"BeforeDiscountAmount": 164136000,
"AfterDiscountAmount": 131308800,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-04T00:00:00+03:30",
"DateTimeLong": 1685824200,
"BeforeDiscountAmount": 111217000,
"AfterDiscountAmount": 77851900,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-05T00:00:00+03:30",
"DateTimeLong": 1685910600,
"BeforeDiscountAmount": 123948000,
"AfterDiscountAmount": 99158400,
"IsInstantDay": 0,
"IsBlocked": "1"
},
{
"RoomId": "100000",
"DateTime": "2023-06-06T00:00:00+03:30",
"DateTimeLong": 1685997000,
"BeforeDiscountAmount": 77782000,
"AfterDiscountAmount": 70003800,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-07T00:00:00+03:30",
"DateTimeLong": 1686083400,
"BeforeDiscountAmount": 101413000,
"AfterDiscountAmount": 60847800,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-08T00:00:00+03:30",
"DateTimeLong": 1686169800,
"BeforeDiscountAmount": 1545000,
"AfterDiscountAmount": 927000,
"IsInstantDay": 0,
"IsBlocked": "1"
},
{
"RoomId": "100000",
"DateTime": "2023-06-09T00:00:00+03:30",
"DateTimeLong": 1686256200,
"BeforeDiscountAmount": 64861000,
"AfterDiscountAmount": 58374900,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-10T00:00:00+03:30",
"DateTimeLong": 1686342600,
"BeforeDiscountAmount": 139339000,
"AfterDiscountAmount": 97537300,
"IsInstantDay": 0,
"IsBlocked": "1"
},
{
"RoomId": "100000",
"DateTime": "2023-06-11T00:00:00+03:30",
"DateTimeLong": 1686429000,
"BeforeDiscountAmount": 109163000,
"AfterDiscountAmount": 54581500,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-12T00:00:00+03:30",
"DateTimeLong": 1686515400,
"BeforeDiscountAmount": 172948000,
"AfterDiscountAmount": 121063600.0,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-13T00:00:00+03:30",
"DateTimeLong": 1686601800,
"BeforeDiscountAmount": 92064000,
"AfterDiscountAmount": 73651200,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-14T00:00:00+03:30",
"DateTimeLong": 1686688200,
"BeforeDiscountAmount": 50504000,
"AfterDiscountAmount": 20201600,
"IsInstantDay": 0,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-15T00:00:00+03:30",
"DateTimeLong": 1686774600,
"BeforeDiscountAmount": 95269000,
"AfterDiscountAmount": 38107600,
"IsInstantDay": 1,
"IsBlocked": "0"
},
{
"RoomId": "100000",
"DateTime": "2023-06-16T00:00:00+03:30",
"DateTimeLong": 1686861000,
"BeforeDiscountAmount": 138475000,
"AfterDiscountAmount": 55390000,
"IsInstantDay": 0,
"IsBlocked": "0"
}]
In the above JSON, the DailySpecs represents the daily specifications of each room, including blocked or unblocked, AfterDiscountAmount(Price after discount), and the DateTime.
I want to select rooms with no block days(IsBlocked=="0") and have a specific average for AfterDiscountAmount between two specific days.
Created index information is :
1) "index_name"
2) "roomBox-idx"
3) "index_options"
4) (empty list or set)
5) "index_definition"
6) 1) "key_type"
2) "JSON"
3) "prefixes"
4) 1) "RoomBox"
5) "default_score"
6) "1"
7) "attributes"
8) 1) 1) "identifier"
2) "$.RoomName"
3) "attribute"
4) "RoomName"
5) "type"
6) "TEXT"
7) "WEIGHT"
8) "1"
2) 1) "identifier"
2) "$.DailySpecs[*].RoomId"
3) "attribute"
4) "DailySpecs_RoomId"
5) "type"
6) "TAG"
7) "SEPARATOR"
8) "|"
9) "SORTABLE"
10) "UNF"
3) 1) "identifier"
2) "$.DailySpecs[*].DateTimeLong"
3) "attribute"
4) "DailySpecs_DateTimeLong"
5) "type"
6) "NUMERIC"
7) "SORTABLE"
8) "UNF"
4) 1) "identifier"
2) "$.DailySpecs[*].AfterDiscountAmount"
3) "attribute"
4) "DailySpecs_AfterDiscountAmount"
5) "type"
6) "NUMERIC"
7) "SORTABLE"
8) "UNF"
5) 1) "identifier"
2) "$.DailySpecs[*].IsBlocked"
3) "attribute"
4) "DailySpecs_IsBlocked"
5) "type"
6) "TAG"
7) "SEPARATOR"
8) "|"
9) "SORTABLE"
10) "UNF"
I've written the following query but seems it just consider the first element of nested array in query:
"FT.AGGREGATE" "roomBox-idx" "@DailySpecs_IsBlocked:{0} @DailySpecs_DateTimeLong:[1685046600 inf] @DailySpecs_DateTimeLong:[-inf (1685392200] " "GROUPBY" "1" "@DailySpecs_RoomId" "REDUCE" "COUNT" "0" "AS" "COUNT" "REDUCE" "AVG" "1" "@DailySpecs_AfterDiscountAmount" "AS" "ADA_AVG" "FILTER" " @ADA_AVG <= 900000000 && @ADA_AVG >= 0" "LIMIT" "0" "10"
Where I made a mistake?
Thanks.
