# Java - build query due to the many fields

**URL:** <https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493>\
**Category:** Elasticsearch\
**Created:** [January 22, 2018, 2:19pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493 "2018-01-22T14:19:19Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 22, 2018, 2:19pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/1 "2018-01-22T14:19:19Z")

</div>

I use Java and Java High Level REST Client.  
I need to build query that will find documents. This query should contain many fields for searching.  
For example, I have such json:

> {  
> "timeStamp": "Fri, 29 Dec 2017 15:32:22 +0000",  
> "value": 314,  
> "operation": "[http://software-testing.ru/library/testing/testing-tools/2638-postman](http://software-testing.ru/library/testing/testing-tools/2638-postman)",  
> "type": "duration",  
> "system":"front-admin"  
> }

and I need find all documents due to the all fields.  
I understand how constract query due to the one field (parametr) but don't know how I can use a lot of fields for query:

> @PostMapping("/findMetricsByValues")  
> @Transactional  
> public ResponseEntity findMetricsByValues(@RequestBody ElasticSearchMetrics metrics){
> 
> ```
> SearchRequest searchRequest = new SearchRequest();
> 
> SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
> sourceBuilder.query(QueryBuilders.termQuery("value", metrics.getValue()));
> searchRequest.source(sourceBuilder);
> SearchResponse searchResponse = null;
> try {
> searchResponse = client.search(searchRequest);
> } catch (IOException e) {
> e.printStackTrace();
> }
> return new ResponseEntity<>(new GenericResponse(searchResponse, CODE_200), HttpStatus.OK);
> }
> 
> ```

Could somebody help with it or give a hint?  
Thanks a lot.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 22, 2018, 2:52pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/2 "2018-01-22T14:52:16Z")

</div>

To format your code, please use the code button, not the citation button.

Basically you want to run a [Bool Query](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-bool-query.html), right?

Have a look at: [https://www.elastic.co/guide/en/elasticsearch/client/java-rest/6.1/java-rest-high-query-builders.html#\_compound\_queries](https://www.elastic.co/guide/en/elasticsearch/client/java-rest/6.1/java-rest-high-query-builders.html#_compound_queries)

I just wrote an example here: [Elastic search depreciated classes](https://discuss.elastic.co/t/elastic-search-depreciated-classes/116232/8?u=dadoonet)

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 22, 2018, 3:02pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/3 "2018-01-22T15:02:29Z")

</div>

I think, Bool Query - it's exactly what I've been searching for.  
I'll try.  
Thanks a lot.

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 22, 2018, 3:16pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/4 "2018-01-22T15:16:46Z")

</div>

And I have one more question.  
What if some of this fields will have null value. For example, in my case "timeStamp" and "system" will be empty, but I want get all document due to the fields that won't have null value be in json:

```
{
	"timeStamp": NULL,
	"value": 314,
	"operation": "http://software-testing.ru/library/testing/testing-tools/2638-postman",
	"type": "accuracy",
	"system": NULL
}

```

@PostMapping("/findMetricsByValues")  
@Transactional  
public ResponseEntity findMetricsByValues(@RequestBody ElasticSearchMetrics metrics){

```
    SearchRequest searchRequest = new SearchRequest();

    SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
    sourceBuilder.query(QueryBuilders.boolQuery()
            .filter(QueryBuilders.termQuery("value", metrics.getValue()))
            .filter(QueryBuilders.termQuery("type", metrics.getType()))
            .filter(QueryBuilders.termQuery("operation", metrics.getOperation()))
            .filter(QueryBuilders.termQuery("operation", metrics.getOperation()))
            .filter(QueryBuilders.termQuery("timeStamp", metrics.getTimeStamp()))
    );
    searchRequest.source(sourceBuilder);
    SearchResponse searchResponse = null;
    try {
        searchResponse = client.search(searchRequest);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return new ResponseEntity<>(new GenericResponse(searchResponse, CODE_200), HttpStatus.OK);
}

```

Than I'll have empty response

What should I do to allow searching by the rest fields in such case ?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 22, 2018, 4:23pm UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/5 "2018-01-22T16:23:38Z")

</div>

So you want to search for documents which have:

- field: foo

or

- field: NULL

Is that right?

You can use a bool query, with a should clause which contains 2 clauses:

- A term query on field `field` with value `foo`
- An exist query (wrapped inside a bool -\> must\_not clause)

I suggest that you first try to write such a query in Kibana console, then translate it to some Java code.

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 8:32am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/6 "2018-01-23T08:32:06Z")

</div>

I just want to get result(records) of existing metrics when one or a few of the fields will be NULL and other fields will exist in ES.  
But when one of the fields will be incorrect (there no such records in ES) than I won't get any result (records).

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 8:40am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/7 "2018-01-23T08:40:02Z")

</div>

```
SearchRequest searchRequest = new SearchRequest();

        SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
        sourceBuilder.query(QueryBuilders.boolQuery()
                .should(QueryBuilders.termQuery("value", metrics.getValue()))
                .should(QueryBuilders.termQuery("type", metrics.getType()))
                .should(QueryBuilders.termQuery("system", metrics.getSystem()))

        );
        searchRequest.source(sourceBuilder);

```

In such way when one of the fields will be NULL, ES will find records according to other fields even if one of the fields will be inccorrect (there not such record in ES). But I need when one of the fields won't exsist in ES, but not NULL, then I don't get any result.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 23, 2018, 8:48am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/8 "2018-01-23T08:48:09Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you want to do exactly. Please, try to keep the example as simple as possible (like one or two fields should be enough).

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 9:13am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/9 "2018-01-23T09:13:41Z")

</div>

```
POST _search
{
  "query": {
    "bool" : {
     "filter": [
        { "term" : { "value" : "test" } },
        { "term" : { "type" : "testType" } }
      ],
 }
}

```

but I don't know how to add NULL condition. So, for example value can be "test" or NULL, than I will get records.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 23, 2018, 9:30am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/10 "2018-01-23T09:30:11Z")

</div>

I can't reproduce with that. Please read carefully [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). There is an example of what is a recreation script.  
Here I can't just copy and paste and run.

BTW there is a typo here:

```auto
      ],

```

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 9:46am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/11 "2018-01-23T09:46:34Z")

</div>

```
 POST _search
   {
  "query":{
    "bool":{
     "filter":[
        { "term" : { "value" : "test"} },
        { "term" : { "type" : "testType" } }
      ]
 }
}
}
```

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 9:50am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/12 "2018-01-23T09:50:29Z")

</div>

Can I add in this query condition: if meaning of "value" will be NULL then just don't execute this term ? In other case just execute the term with other terms.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 23, 2018, 10:01am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/13 "2018-01-23T10:01:06Z")

</div>

Ok. Apparently, I'm not clear enough about what is a reproduction script. So let me show you what happens when I run your script:

```auto
POST _search
   {
  "query":{
    "bool":{
     "filter":[
        { "term" : { "value" : "test"} },
        { "term" : { "type" : "testType" } }
      ]
 }
}
}

```

```auto
{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 9,
    "successful": 9,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 0,
    "max_score": null,
    "hits": []
  }
}

```

I have nothing. Well. I have no data to search for.  
From that, I can't help. Is that more clear? I'm going to copy and paste here the full example that is available at [https://discuss.elastic.co/t/about-the-elasticsearch-category/21:](https://discuss.elastic.co/t/about-the-elasticsearch-category/21:)

```auto
DELETE index
PUT index/type/1
{
  "foo": "bar"
}
GET index/type/_search
{
  "query": {
    "match": {
      "foo": "bar"
    }
  }
}

```

When I run this ^^^, I'm getting:

```auto
{
  "took": 14,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 0.2876821,
    "hits": [
      {
        "_index": "index",
        "_type": "type",
        "_id": "1",
        "_score": 0.2876821,
        "_source": {
          "foo": "bar"
        }
      }
    ]
  }
}

```

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 23, 2018, 10:09am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/14 "2018-01-23T10:09:01Z")

</div>

Ok, let's forget about privious example.  
I have another way of executing my task.  
Here example:

```
sourceBuilder.query(QueryBuilders.boolQuery()
                .filter(QueryBuilders.termQuery("value", metrics.getValue()))

```

I need: if metrics.getValue() will respond with NULL than just don't execute this term.  
Can I do it in some way?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [January 23, 2018, 10:27am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/15 "2018-01-23T10:27:22Z")

</div>

```
BoolQueryBuilder bqb = QueryBuilders.boolQuery();
if (metrics.getValue() != null) {
  bqb.filter(QueryBuilders.termQuery("value", metrics.getValue());
}
sourceBuilder.query(bqb);
```

---

<div class="post-metadata">

**Author:** ![Nikita\_Krasnov](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@Nikita\_Krasnov](https://discuss.elastic.co/u/Nikita_Krasnov)\
**Post date:** [January 24, 2018, 8:28am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/16 "2018-01-24T08:28:27Z")

</div>

Thanks, that helps.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 21, 2018, 8:28am UTC](https://discuss.elastic.co/t/java-build-query-due-to-the-many-fields/116493/17 "2018-02-21T08:28:43Z")

</div>

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