Combine range query with term

[Stella_Sunshine (Sara)]

Hey everybody,
the title seems as if the solution is easy... but my problem is a bit more tricky.
I have several products which can have several articles and each article has a price list with different price list ids.

This is a snippet of my elastic document:

"article": [
{
"price_list": [
{
"price_list_id": 1,
"price_net": 756.26
},
{
"price_list_id": 2,
"price_net": 688.2
},
{
"price_list_id": 3,
"price_net": 688.2
},
{
"price_list_id": 4,
"price_net": 739.45
}
]
}
]

And I need to get all products which have at least one article with the price_list_id 4 AND a price_net range with gte = 413 and lte = 702.

If I try this :

"bool": {
"must": [
{
"range": {
"article.price_list.price_net": {
"gte": 413,
"lte": 702
}
}
},
{
"term": {
"article.price_list.price_list_id": 4
}
}
]
}

the above snippet matches. But it should not because the price_net with the price_list_id = 4 is not between 413 and 702

Has anyone a solution for my problem?

Thanks a lot.

[spinscale (Alexander Reelsen)]

If you are searching for multiple fields within an array, then you need to take a look at the nested datatype and the nested query

hope this helps!

--Alex

[Stella_Sunshine (Sara)]

Thanks a lot. That helped.

For all others:

I adapted the mapping

"price_list": {
"type": "nested",
"properties": {
"price_list_id": {
"type": "keyword"
},
"price_net": {
"type": "float"
}
}
}

and my query looks like this now:

"query": {
"bool": {
"must": [
{
"nested": {
"path": "article.price_list",
"query": {
"bool": {
"must": [
{
"range": {
"article.price_list.price_net": {
"gte": 413,
"lte": 702
}
}
},
{
"term": {
"article.price_list.price_list_id": 4
}
}
]
}
}
}
}
]
}