1

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)?

Improve this question
4
  • So you'd like to explicitly exclude {{parameter_id: 5, parameter_value_id: 10}, {parameter_id: 1, parameter_value_id: 254}} or all combinations that don't fulfil the 2 either/or combinations? Commented Nov 4, 2020 at 11:47
  • The second option is correct. I only want to get the result if both of those parameters and their values are contained in one of the sub-arrays of parameters_for_filter. Commented Nov 4, 2020 at 12:03
  • OK can you include a few more sample docs that should not match? I mean, under the current query, I'd expect the whole parent doc to be returned b/c both conditions are present in some of the parameters. Also, are you familiar with inner hits in nested docs? elastic.co/guide/en/elasticsearch/reference/6.8/… ? Commented Nov 4, 2020 at 12:20
  • I added one more product and desired returned values. I want the parent to be returned so that is not the problem. I looked at inner hits but cannot really understand how I could use them in this scenario. Commented Nov 4, 2020 at 13:38

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

We're still testing but it looks really promising and I'll definitely let you know if it works.
1

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
                }
              ]
            ]
          }
        }
      }
    ]
Improve this answer

3 Comments

Thank you. Problem with this is that the data can't very well look like this. That object with ID 69 is a product. It has some variants and those variants have each their own parameters. We need to find a product that has a variant which matches all the selected parameters. We can change the format of those parameters within the exported product object but we don't want to export variants as individual objects.
@LukášKielar if you index the same sample index document (that you have added in the question), and run the above search query, you will get your required search result (I have already tested it locally). Please try to run the search query once, with your data as well, and let me know if now your issue is resolved :)
Well, it seems to work for this particular query but if I wanted to filter exactly like this it would look like {{"parameter_id": 5, "parameter_value_id": [10, 16]}, {"parameter_id": 1, "parameter_value_id": [248, 254]}}. Both parameters need to be present but there would be OR between their values. If I use your solution for {{parameter_id: 5, parameter_value_id: 16}, {parameter_id: 1, parameter_value_id: 248}} it still returns product even though I don't expect any.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.