Multilevel depth query

Elastic Stack Elasticsearch
1

Hello, I started using Elasticsearch for my e-commerce site but I am struggling with a query:

I am trying to do the following SQL query:

SELECT * FROM products WHERE 
((params.value = 'book' AND params.param = 'category'') OR (params.value = 'universal' AND params.param = 'category')) 
AND 
((params.value = 'textile' AND params.param = 'material') OR (params.value = 'sillicon' AND params.param = 'material'))

currently I have this:

"filter": [
        {
          "term": {
            "publicsite": 1
          }
        },
        {
          "nested": { 
            "path": "params",
            "query": {
              "bool": {
                "must": [
                  {
                    "bool": {
                      "should": [
                        {
                          "bool": {
                            "must": [
                              {
                                "term": {
                                  "params.value": "agenda"
                                }
                              },
                              {
                                "term": {
                                  "params.param": "subcategorie"
                                }
                              }
                            ]
                          }
                        },
                        {
                          "bool": {
                            "must": [
                              {
                                "term": {
                                  "params.value": "universala"
                                }
                              },
                              {
                                "term": {
                                  "params.param": "subcategorie"
                                }
                              }
                            ]
                          }
                        }
                      ]
                    }
                  },
                  {
                    "bool": {
                      "should": [
                        {
                          "bool": {
                            "must": [
                              {
                                "term": {
                                  "params.value": "textil"
                                }
                              },
                              {
                                "term": {
                                  "params.param": "material"
                                }
                              }
                            ]
                          }
                        },
                        {
                          "bool": {
                            "must": [
                              {
                                "term": {
                                  "params.value": "silicon"
                                }
                              },
                              {
                                "term": {
                                  "params.param": "material"
                                }
                              }
                            ]
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }

]

The strange thing to me is that if I change to fist "must" into "should" it works but I'm not sure if its 100% corect.

2

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