Divide Should query into two parts and combine them with and logic

Elastic Stack Elasticsearch
1 
GET INDEX/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "terms": {
            "deleted": [
              false
            ]
          }
        },
        {
          "terms": {
            "published": [
              true
            ]
          }
        }
      ],
      "should": [
        {
          "nested": {
            "path": "manufacturers",
            "query": {
              "terms": {
                "manufacturers.id": [
                  2452
                ]
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "manufacturers"
                }
              }
            ]
          }
        },
        {
          "nested": {
            "path": "vendors",
            "query": {
              "terms": {
                "vendors.id": [
                  8,9
                ]
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "vendors"
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}

So what i am trying to do... My query logic in should query is simple... manufacturerIds in values OR manufacturersIds not exist Or vendorIds in values OR vendorIds not exist , but what i want to do will look something like this: manufacturerIds in values OR manufacturersIds not exist AND vendorIds in values OR vendorIds not exist

2 
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "terms": {
            "deleted": [
              false
            ]
          }
        },
        {
          "terms": {
            "published": [
              true
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "vendors"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
   "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}

in this query i think i got what i want but when I filter for manufacturer with id 2452 it gives me hits with manufacturerId 22 or 45 and so on what I am doin wrong

3

Querys will give you hits even if its not an exact match. You need filters for exact matches.

4

Just to be sure, do you want:

(manufacturerIds in values OR manufacturersIds not exist) AND (vendorIds in values) OR (vendorIds not exist)

or

(manufacturerIds in values OR manufacturersIds not exist) AND (vendorIds in values OR vendorIds not exist)

or

manufacturerIds in values OR (manufacturersIds not exist AND vendorIds in values) OR vendorIds not exist

?

I guess it's this one:

(manufacturerIds in values OR manufacturersIds not exist) AND (vendorIds in values OR vendorIds not exist)

5

yes u guess it

6

So you need something like:

bool -> filter [
  { bool -> should [{manufacturerIds in values }, { bool -> must_not { exists manufacturersIds }} ] },
  { bool -> should [{vendorIds in values}, { bool -> must_not { exists vendorIds }}] },
]

Something like that. If you did not succeed in building it, could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

7 
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "vendors"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
   "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}

This query works fine, but when I need extra filtering, this gets into wrong results...
extra filterings:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "terms": {
            "deleted": [
              false
            ]
          }
        },
        {
          "terms": {
            "published": [
              true
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "vendors"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}
8

So we can mark your initial question as solved?

May be open a new question with more things you need to solve?

Again, share a full reproduction script.

9 
{
  "took" : 22,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 9707,
      "relation" : "eq"
    },
    "max_score" : 5.0,
    "hits" : [
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "93240",
        "_score" : 5.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 2452
            }
          ],
          "id" : 93240,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "93242",
        "_score" : 5.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 2452
            }
          ],
          "id" : 93242,
          "vendors" : [
            {
              "id" : 24
            },
            {
              "id" : 94
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "93271",
        "_score" : 5.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 2452
            }
          ],
          "id" : 93271,
          "vendors" : [
            {
              "id" : 24
            },
            {
              "id" : 94
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "93274",
        "_score" : 5.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 2452
            }
          ],
          "id" : 93274,
          "vendors" : [
            {
              "id" : 24
            },
            {
              "id" : 94
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "3133",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 166
            }
          ],
          "id" : 3133,
          "vendors" : [
            {
              "id" : 8
            },
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "5713",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 216
            }
          ],
          "id" : 5713,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "5798",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 216
            }
          ],
          "id" : 5798,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "6082",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 222
            }
          ],
          "id" : 6082,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "6134",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 222
            }
          ],
          "id" : 6134,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      },
      {
        "_index" : "onoff-live",
        "_type" : "_doc",
        "_id" : "6171",
        "_score" : 4.0,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 222
            }
          ],
          "id" : 6171,
          "vendors" : [
            {
              "id" : 24
            }
          ]
        }
      }
    ]
  }
}
10

Sorry, but are u meaning result i got in reproduction script?

11

I meant what is described in the link I provided: About the Elasticsearch category

A typical script like this one can be copied and pasted in Kibana Dev Console by any reader. It will definitely help to play with your example and provide a fix for your script.

12

Mapping

PUT index
PUT /index/_mapping
{
  "properties": {
    "categories": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "integer"
        }
      }
    },
    "deleted": {
      "type": "boolean"
    },
    "id": {
      "type": "long"
    },
    "manufacturers": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "integer"
        }
      }
    },
    "vendors": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "long"
        },
        "isDeletedVendor": {
          "type": "boolean"
        },
        "isPublishedVendor": {
          "type": "boolean"
        }
      }
    },
    "published": {
      "type": "boolean"
    }
  }
}

Fill with Data

PUT index/_doc/1
{
  "categories": [
    {
      "id": 9
    }
  ],
  "manufacturers": [
    {
      "id": 2452
    }
  ],
  "vendors": [
    {
      "id": 8,
      "isDeletedVendor": true,
      "isPublishedVendor": true
    },
    {
      "id": 9,
      "isDeletedVendor": true,
      "isPublishedVendor": true
    }
  ],
  "published": true,
  "deleted": false
}

Query

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "terms": {
            "deleted": [
              false
            ]
          }
        },
        {
          "terms": {
            "published": [
              true
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "vendors"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}
13

The Logic Should be something Like that:

Categories in values AND Deleted in False AND Published in True AND ManufacturerIds in values OR manufacturersIds not exist AND vendorIds in values OR vendorIds not exist

14

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