Filtering inside objects array

[Irakli]

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 ?

[spinscale (Alexander Reelsen)]

If you are trying to query several fields within an array and expected every array element to be it's own little document, take a look at the nested datatype

[Irakli]

Thank you Alexander for replay, but how can I reach the following logic in case of nested document:

For example: Give me a customer which has transaction between '2019-01-01' and '2019-01-03'
AND transactionCode in (100301, 100501)(both of them) And/Or transacionCode not in (100601,100701) And/Or merchantName in ("a","b")(at least one) And/Or merchantName not in ("c","d");

can I reach this logic with one query ? I think it can't, It will probably need multiple queries with each own aggregations and then each one's result should be connected somehow. So this is not a good idea in case of big data, because it takes a lot of memory for each query result and the time will probably increase greatly.

[spinscale (Alexander Reelsen)]

yes you can, this is merely a question of wrapping bool queries within other bool queries (that at is deepest level with have nested queries).

I am not sure how you bring aggregations in here as your original query did not contain one either.