get data from nested field by apply must query in elastic search

Question

I have three tables.Their structure like -

public class RcItem{
 @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "rcItem")
    @JsonManagedReference
    private Set<RcItemRegulation> rcItemRegulations = new HashSet<>();
}

public class RcItemRegulation{
@ManyToOne
    @JoinColumn(name = "rc_item_id")
    @Field(type = FieldType.Nested, index = FieldIndex.analyzed, analyzer = "lowercase_keyword", store = true)
    @JsonBackReference
    private RcItem rcItem;

    @ManyToOne
    @JoinColumn(name = "rgltn_id")
    @Field(type = FieldType.Nested, index = FieldIndex.analyzed, analyzer = "lowercase_keyword", store = true)
    private Regulation regulation;
}

public class Regulation{
 @OneToMany(cascade = CascadeType.ALL, mappedBy = "regulation")
    @JsonManagedReference
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    private Set<RcItemRegulation> rcItemRegulations = new HashSet<>();

   @Column(name = "rgltn_full_name")
    @Field(type = FieldType.String, index = FieldIndex.analyzed, analyzer = "lowercase_keyword", store = true)
    private String rgltnFullName;
}

This above data index in my data structure like -

"rcItemRegulations": [
                  {
                     "id": 1,
                     "rcItemRgltnType": "primary",
                     "regulation": {

                        "rgltnFullName": "17 ABC § 1.12(f)(5)(i)(B)"

                     }
                  }]

For this I try the elastic search query -

{"query":{
  "bool" : {
    "must" : {
      "bool" : {
        "must" : [ {
          "term" : {
            "rcItemRegulations.rcItemRgltnType" : "primary"
          }
        }, {
          "term" : {
           "rcItemRegulations.regulation.rgltnFullName" : "17 ABC § 1.12(f)(5)(i)(B)"
          }
        } ]
      }
    }
  }
}
}

This give me blank result array even if this is exist.Please help me to get data from elastic search.

Answer 1

i was looking down at your problem. i have two suggestions for you.

First

Since rcItemRegulations is a array of objects and you are trying to search based on values inside the rcItemRegulations. So i suggest you have them mapped as nested data type . You can use the following mappings. Also since you are doing exact value match, i added a keyword analyzer which will retain the same exact values in the inverted index.

Mappings

{
    "settings": {
        "analysis": {
            "analyzer": {
                "index_analyzer_v1": {
                    "tokenizer": "keyword",
                    "filter": ["lowercase"]
                }
            }
        }
    },
    "mappings": {
        "type_1": {
            "properties": {
                "rcItemRegulations": {
                    "type": "nested",
                    "properties": {
                        "regulation": {
                            "type": "object",
                            "properties": {
                                "rgltnFullName": {
                                    "type": "text",
                                    "analyzer": "index_analyzer_v1"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Secondly you will also need to change the query as you are now querying on nested data type this time. You have to use nested_query

{
    "query": {
        "bool": {
            "must": [{
                "nested": {
                    "path": "rcItemRegulations",
                    "query": {
                        "bool": {
                            "must": [{
                                "term": {
                                    "rcItemRegulations.rcItemRgltnType": "primary"
                                }
                            }, {
                                "term": {
                                    "rcItemRegulations.regulation.rgltnFullName": "17 abc § 1.12(f)(5)(i)(b)"
                                }
                            }]
                        }
                    }
                }
            }]
        }
    }
}

Note: lowercase all the text in the search query.