Query Elastic DSL - Search query using spring boot data

Question

I have the following properties file generated via Java and spring boot data elasticsearch. The file is generated in a User.java class and the property "friends" is a List where Friends is a Fiends.java file, both class file act as the model. Essentially I want to produce a select statement but in Query DSL Language using Spring Boot Data. The index is called user.

So I am trying to achieve the following SELECT * FROM User where (userName ="Tom" OR nickname="Tom" OR friendsNickname="Tom") AND userID="3793"

or (verbose-dsl)

match where (userName="Tom" OR nickname="Tom" OR friendsNickname="Tom") AND userID="3793"

"mappings": {
  "properties": {
    "_class": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "userName": {
      "type": "text"
    },
    "userId": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "friends": {
      "type": "nested",
      "properties": {
        "firstName": {
          "type": "text"
        },
        "lastName": {
          "type": "text"
        },
        "age": {
          "type": "text"
        },
        "friendsNickname": {
          "type": "text"
        }
      }
    },
    "nickname": {
      "type": "text"
    }
  }
}

I have tried the following code but return 0 hits back from a elastic search but no dice returns no hits

BoolQueryBuilder query =
        QueryBuilders.boolQuery()
            .must(
                QueryBuilders.boolQuery()
                    .should(QueryBuilders.matchQuery("userName", "Tom"))
                    .should(QueryBuilders.matchQuery("nickname", "Tom"))
                    .should(
                        QueryBuilders.nestedQuery(
                            "friends",
                            QueryBuilders.matchQuery("friendsNickname", "Tom"),
                            ScoreMode.None)))
            .must(QueryBuilders.boolQuery().must(QueryBuilders.matchQuery("userID", "3793")));

Apologies if this seems like a simple question, My knowledge on ES is quite thin, sorry if this may seem like an obvious answer.

Answer 1

Use friends.friendsNickname and also user termsQuery on userId.keyword

`

.must(QueryBuilders.boolQuery()
    .should(QueryBuilders.matchQuery("userName", "Tom"))
    .should(QueryBuilders.matchQuery("nickname", "Tom"))
    .should(QueryBuilders.matchQuery("friends.friendsNickname", "Tom"))
  )
  .must(QueryBuilders.termsQuery("userId.keyword", "3793"));

`

Although I recommend changing userName, userID to keyword.

"userId": {
      "type": "keyword",
      "ignore_above": 256,
      "fields": {
        "text": {
          "type": "text"
        }
      }
    }

Then you don't have to put keyword so you just have to put userId instead of userId.keyword. If you want to have full-text search on the field is use userId.text. The disadvantage of having a text type is that you can't use the field to sort your results that's why I encourage ID fields to be of type keyword.

Answer 2

Great start!!

You just have a tiny mistake on the following line where you need to prefix the field name by the nested field name, i.e. friends.friendsNickname

...
QueryBuilders.matchQuery("friends.friendsNickname", "Tom"),
...                          ^
                             |
                          prefix

Also you have another typo where the userID should read userId according to your mapping.