Java Elasticsearch - Querying nested element fields with And

Question

I have the following structure in ES (Also dataset for this example)

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_index": "hotels",
        "_type": "_doc",
        "_id": "7148",
        "_score": 1.0,
        "_source": {
         
          "places": [
            {
              "placeId": 3,
              "type": "MAIN",
              "name": "pruggern"
            }
          ]
        }
      },
      {
        "_index": "hotels",
        "_type": "_doc",
        "_id": "7147",
        "_score": 1.0,
        "_source": {
         
          "places": [
            {
              "placeId": 3,
              "type": "MAIN",
              "name": "pruggern"
            }
          ]
        }
      },
      {
        "_index": "hotels",
        "_type": "_doc",
        "_id": "7146",
        "_score": 1.0,
        "_source": {
          "places": [
            {
              "placeId": 3,
              "type": "AROUND",
              "name": "pruggern"
            },
            {
              "placeId": 1,
              "type": "MAIN",
              "name": "schladming"
            }
          ]
        }
      }
    ]
  }
}

Out of this dataset for a given place and type I need all the hotels

For example if I query for placeId 3 and type MAIN, I should get hotels with id 7148 and 7147

I query for placeId 1 and 3 and type MAIN then all the 3 offers should be returned

What I tried so far

BoolQueryBuilder placeQueryWithType = boolQuery();
    placeQueryWithType.minimumShouldMatch(1);

    placeQueryWithType.should(termsQuery(PLACES + ".placeId", placeIds)); //array of places

    placeQueryWithType.must(termQuery(PLACES + ".type", placeType.name())); //Either MAIN or AROUND
    mainQuery.must(placeQueryWithType);

This did not work as it was giving me all the offers. I debugged the code to see the query and it looked something like this

{
  "query": {
            "bool" : {
              "must" : [
                {
                  "bool" : {
                    "must" : [
                      {
                        "term" : {
                          "places.type" : {
                            "value" : "MEETING_POINT",
                            "boost" : 1.0
                          }
                        }
                      }
                    ],
                    "should" : [
                      {
                        "terms" : {
                          "places.placeId" : [
                            3
                          ],
                          "boost" : 1.0
                        }
                      }
                    ],
                    "adjust_pure_negative" : true,
                    "minimum_should_match" : "1",
                    "boost" : 1.0
                  }
                }
              ],
              "adjust_pure_negative" : true,
              "boost" : 1.0
      }
    }
}

Could be that the query just satisfies one of the condition and it fails

I also tried to generate a query something like this where must clause has a should and must but still dont work

{
  "query":{
  "bool" : {
    "must" : [
      {
        "bool" : {
          "should" : [
            {
              "bool" : {
                "must" : [
                  {
                    "terms" : {
                      "places.placeId" : [
                        3
                      ],
                      "boost" : 1.0
                    }
                  },
                  {
                    "term" : {
                      "places.type" : {
                        "value" : "MEETING_POINT",
                        "boost" : 1.0
                      }
                    }
                  }
                ],
                "adjust_pure_negative" : true,
                "boost" : 1.0
              }
            }
          ],
          "adjust_pure_negative" : true,
          "minimum_should_match" : "1",
          "boost" : 1.0
        }
      }
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
}
}

Thank you

When querying objects in arrays, you might need to use the nested field type to get the correct hits. See the docs: elastic.co/guide/en/elasticsearch/reference/7.17/nested.html — ODDminus1
– ODDminus1, Commented Apr 13, 2023 at 10:55

rabbitbr · Accepted Answer · 2023-04-13 12:30:48Z

1

Did this query and it works as per the requirement of the question.

{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "places",
            "query": {
              "bool": {
                "must_not": [
                  {
                    "terms": {
                      "places.placeId": [
                        3
                      ]
                    }
                  },
                  {
                    "term": {
                      "places.type": {
                        "value": "MAIN"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

answered Apr 13, 2023 at 12:30

rabbitbr

3,2712 gold badges8 silver badges20 bronze badges

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

1 Comment

Sandeep Nair Over a year ago

I ran to error and got an error that places is not nested. So i think I found the problem, it seems the structure is not nested after all.

Collectives™ on Stack Overflow

Java Elasticsearch - Querying nested element fields with And

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related