Reputation: 66
Filter by multiple nested properties
I have index definition like this:
{
"settings": {
"index": {
"number_of_shards": 1,
"number_of_replicas": 0
},
},
"mappings": {
"properties": {
"parameters_for_filter": {
"type": "nested",
"properties": {
"parameters": {
"type": "nested",
"properties": {
"parameter_id": {
"type": "integer"
},
"parameter_value_id": {
"type": "integer"
}
}
}
}
}
}
}
}
This is the exported result for one product:
{
"_index": "product_1_9107bbdeb03269e1142d9822e585008c",
"_type": "_doc",
"_id": "69",
"_version": 1,
"_score": 0,
"_source": {
"id": 69,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 248
}
],
[
{
"parameter_id": 5,
"parameter_value_id": 16
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 254
}
]
]
}
}
}
Another product:
{
"_index": "product_1_9107bbdeb03269e1142d9822e585008c",
"_type": "_doc",
"_id": "83",
"_version": 5,
"_score": 0,
"_source": {
"id": 83,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_value_id": 10,
"parameter_id": 5
},
{
"parameter_value_id": 251,
"parameter_id": 3
},
{
"parameter_value_id": 254,
"parameter_id": 1
}
],
[
{
"parameter_value_id": 16,
"parameter_id": 5
},
{
"parameter_value_id": 121,
"parameter_id": 2
},
{
"parameter_value_id": 254,
"parameter_id": 1
}
]
]
}
}
}
Each sub-array inside parameters_for_filter.parameters represents parameters of individual variants of a product.
What I need to do is get the product with ID 69 for combination of filters:
{{parameter_id: 5, parameter_value_id: 10}, {parameter_id: 1, parameter_value_id: 248}}.
Get the product with ID 83 for combination of filters:
{{parameter_id: 5, parameter_value_id: 10}, {parameter_id: 1, parameter_value_id: 254}}.
Get both products for combination of filters:
{{parameter_id: 5, parameter_value_id: 16}, {parameter_id: 1, parameter_value_id: 254}}.
Get no products for combination of filters:
{{parameter_id: 5, parameter_value_id: 16}, {parameter_id: 1, parameter_value_id: 248}}.
With current query I get the product with all 3 above combinations:
{
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": [
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": [
{
"term": {
"parameters_for_filter.parameters.parameter_id": 5
}
},
{
"terms": {
"parameters_for_filter.parameters.parameter_value_id": [
10
]
}
}
]
}
}
}
},
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": [
{
"term": {
"parameters_for_filter.parameters.parameter_id": 1
}
},
{
"terms": {
"parameters_for_filter.parameters.parameter_value_id": [
248
]
}
}
]
}
}
}
}
]
}
}
}
What would the filter that would accomplish what I need look like or is there a better structure that I could use for parameters of product variants in eshop (without exporting variants as separate objects)?
Upvotes: 1
Views: 228
Answers (2)
Reputation: 16895
We've somehow got to let ES know that the parameters are indeed distinct groups of attributes and upon closer inspection it appear that there's one extra bracket [ ] pair enclosing the params which essentially nullifies the nested-ness b/c everything is one large array.
What I propose is the following: since parameters_for_filter is an object consisting of precisely 1 child, we'll default it to a simple object and we'll add one more level of nested-ness to isolate what I'll call parameter_groups.
So the mapping'll look like this:
PUT eshop
{
"settings": {
"index": {
"number_of_shards": 1,
"number_of_replicas": 0
}
},
"mappings": {
"properties": {
"parameters_for_filter": {
"type": "object", <--
"properties": {
"parameters": {
"type": "nested",
"properties": {
"parameter_groups": { <---
"type": "nested",
"properties": {
"parameter_id": {
"type": "integer"
},
"parameter_value_id": {
"type": "integer"
}
}
}
}
}
}
}
}
}
}
After that let's sync the 2 docs. Notice how the param groups are logically separated:
POST eshop/_doc
{
"id": 69,
"parameters_for_filter": {
"parameters": [
{
"parameter_groups": [
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 248
}
]
},
{
"parameter_groups": [
{
"parameter_id": 5,
"parameter_value_id": 16
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 254
}
]
}
]
}
}
and also id:83
POST eshop/_doc
{
"id": 83,
"parameters_for_filter": {
"parameters": [
{
"parameter_groups": [
{
"parameter_value_id": 10,
"parameter_id": 5
},
{
"parameter_value_id": 251,
"parameter_id": 3
},
{
"parameter_value_id": 254,
"parameter_id": 1
}
]
},
{
"parameter_groups": [
{
"parameter_value_id": 16,
"parameter_id": 5
},
{
"parameter_value_id": 121,
"parameter_id": 2
},
{
"parameter_value_id": 254,
"parameter_id": 1
}
]
}
]
}
}
After that we'll proceed to the query which'll look for a must combination of the two condition, albeit under separate parameter_groups paths:
GET eshop/_search
{
"query": {
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": [
{
"nested": {
"path": "parameters_for_filter.parameters.parameter_groups",
"query": {
"bool": {
"must": [
{
"term": {
"parameters_for_filter.parameters.parameter_groups.parameter_id": 5
}
},
{
"terms": {
"parameters_for_filter.parameters.parameter_groups.parameter_value_id": [
16
]
}
}
]
}
}
}
},
{
"nested": {
"path": "parameters_for_filter.parameters.parameter_groups",
"query": {
"bool": {
"must": [
{
"term": {
"parameters_for_filter.parameters.parameter_groups.parameter_id": 1
}
},
{
"terms": {
"parameters_for_filter.parameters.parameter_groups.parameter_value_id": [
248
]
}
}
]
}
}
}
}
]
}
}
}
}
}
All 4 of your requirements are thereby satisfied.
Upvotes: 1
Reputation: 16172
Adding a working example with index data, search query, and search result (used the same mapping as that given in question)
You can use the filter clause, in place of the must clause, if you want to ignore the scoring
Index Data:
{
"id": 69,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 254
}
]
]
}
}
{
"id": 69,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 248
}
]
]
}
}
{
"id": 69,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 247
}
]
]
}
}
Search Query:
{
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": [
{
"match": {
"parameters_for_filter.parameters.parameter_id": 5
}
},
{
"match": {
"parameters_for_filter.parameters.parameter_value_id": 10
}
}
]
}
}
}
},
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": [
{
"match": {
"parameters_for_filter.parameters.parameter_id": 1
}
},
{
"match": {
"parameters_for_filter.parameters.parameter_value_id": 248
}
}
]
}
}
}
}
]
}
},
{
"bool": {
"must": [
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": [
{
"match": {
"parameters_for_filter.parameters.parameter_id": 5
}
},
{
"match": {
"parameters_for_filter.parameters.parameter_value_id": 16
}
}
]
}
}
}
},
{
"nested": {
"path": "parameters_for_filter.parameters",
"query": {
"bool": {
"must": [
{
"match": {
"parameters_for_filter.parameters.parameter_id": 1
}
},
{
"match": {
"parameters_for_filter.parameters.parameter_value_id": 254
}
}
]
}
}
}
}
]
}
}
]
}
}
}
Search Result:
"hits": [
{
"_index": "64678935",
"_type": "_doc",
"_id": "2",
"_score": 4.0,
"_source": {
"id": 69,
"parameters_for_filter": {
"parameters": [
[
{
"parameter_id": 5,
"parameter_value_id": 10
},
{
"parameter_id": 3,
"parameter_value_id": 251
},
{
"parameter_id": 1,
"parameter_value_id": 248
}
]
]
}
}
}
]
Upvotes: 1
Related Questions
- Elasticsearch search with nested properties
- Elasticsearch filter by nested fields
- Filtering on multiple fields from nested object with Elasticsearch
- Filtering by nested object field value in Elasticsearch
- ElasticSearch: query nested objects, that match filter
- Elastic search query filtering based on nested properties
- Elasticsearch nested object filter
- Elasticsearch nested filter query
- Query Nested Object properties against multiple values in elasticsearch
- Nested query in mapping with multiple nested properties