Filtering inside objects array

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 ?

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

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.

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.