Hi there,
My issue is following:
I have the following structure in elastic index, two document examples are here:
{
"customerID" : 2,
"customerStatus" : "active",
"customerName" : "customerName2",
"customerAge" : 32,
"customerGender" : "female",
"customerTenure" : 5,
"customerLocationLong" : 44.783333,
"customerLocationLat" : 41.716667,
"customerLocation" : "tbilisi",
"customerTransactions" : [
{
"tansactionUniqueID" : "uniq4",
"transactionCategoryCode" : 100301,
"transactionParentCategoryCode" : 100300,
"transactionMerchantName" : "ori nabiji",
"transactionDate" : "2019-01-01"
},
{
"tansactionUniqueID" : "uniq5",
"transactionCategoryCode" : 100501,
"transactionParentCategoryCode" : 100500,
"transactionMerchantName" : "wissol",
"transactionDate" : "2019-01-02"
},
{
"tansactionUniqueID" : "uniq6",
"transactionCategoryCode" : 100401,
"transactionParentCategoryCode" : 100400,
"transactionMerchantName" : "",
"transactionDate" : "2019-01-03"
}
]
},
{
"customerID" : 3,
"customerStatus" : "active",
"customerName" : "customerName3",
"customerAge" : 42,
"customerGender" : "male",
"customerTenure" : 10,
"customerLocationLong" : 34.111111,
"customerLocationLat" : 31.222331,
"customerLocation" : "kutaisi",
"customerTransactions" : [
{
"tansactionUniqueID" : "uniq7",
"transactionCategoryCode" : 100301,
"transactionParentCategoryCode" : 100300,
"transactionMerchantName" : "nikora",
"transactionDate" : "2019-01-01"
},
{
"tansactionUniqueID" : "uniq8",
"transactionCategoryCode" : 100501,
"transactionParentCategoryCode" : 100500,
"transactionMerchantName" : "wissol",
"transactionDate" : "2019-01-02"
}
]
}
Then I have the following query :
{
"size" : 1000,
"query" : {
"bool" : {
"must" : [
{
"range" : {
"customerTransactions.transactionDate" : {
"from" : "2019-01-03",
"to" : "2019-01-04",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
},
{
"bool": {
"must": [
{
"term": {
"customerTransactions.transactionCategoryCode": {
"value": 100301
}
}
},
{
"term": {
"customerTransactions.transactionCategoryCode": {
"value": 100501
}
}
}
]
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
"_source" : {
"includes" : [
"customerGender",
"customerID",
"customerAge",
"customerTenure",
"customerLocation"
],
"excludes" :
}
}
and the result is:
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 3.0,
"hits" : [
{
"_index" : "test",
"_type" : "_doc",
"_id" : "2",
"_score" : 3.0,
"_source" : {
"customerAge" : 32,
"customerGender" : "female",
"customerLocation" : "tbilisi",
"customerID" : 2,
"customerTenure" : 5
}
}
]
}
}
So I wanted to get documents where transactionDate would be between "from" : "2019-01-03",
"to" : "2019-01-04" AND transactionCategoryCode would be (100301 & 100501), but query result was customer - 2 which have transactionCategory (100301 & 100501)(correct) but not in referenced time range "from" : "2019-01-03", "to" : "2019-01-04"(incorrect). How can I filter correctly ?