# How to query document for field to be null **URL:** \ **Category:** Elasticsearch\ **Created:** [August 13, 2013, 9:20am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196 "2013-08-13T09:20:52Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Hiro\_Gangwani](https://avatars.discourse-cdn.com/v4/letter/h/9fc29f/32.png) [@Hiro\_Gangwani](https://discuss.elastic.co/u/Hiro_Gangwani)\ **Post date:** [August 13, 2013, 9:20am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/1 "2013-08-13T09:20:52Z")
Dear Team, I want to query document for field to be null using Java API. I am using BoolQueryBuilder class and making conditions using QueryBuilders. Here is example listed below. SQL Query : select id,name,salary from candidates where recruiterid is null and salary \> 10. I have converted above query into Java API as given below. BoolQueryBuilder lAllQuery = QueryBuilders.boolQuery(); QueryBuilder lQueryBuilder1 = QueryBuilders.rangeQuery("salary").gt(10.0); How do i add condition for recruitid is null. Using FilterBuilders.missingFilter("recruiterid) it is possible. But i want to add using QueryBuilders. Please advise. Thanks, Hiro -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Hiro\_Gangwani](https://avatars.discourse-cdn.com/v4/letter/h/9fc29f/32.png) [@Hiro\_Gangwani](https://discuss.elastic.co/u/Hiro_Gangwani)\ **Post date:** [August 13, 2013, 9:35am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/2 "2013-08-13T09:35:51Z")
Here is complete code i am trying to execute. BoolQueryBuilder lAllQuery = QueryBuilders.boolQuery(); QueryBuilder lQueryBuilder1 = QueryBuilders.rangeQuery("salary").gt(10.0); Object lValue = null; QueryBuilder lQueryBuilder2 = QueryBuilders.termQuery("recruiterid", lValue); lAllQuery.must(lQueryBuilder1); lAllQuery.must(lQueryBuilder2); SearchRequestBuilder searchBuilder = client.prepareSearch(idxName).setQuery(lAllQuery).setSize(100) .addField("id") .addField("name") .addField("salary"); SearchResponse search = searchBuilder.execute().actionGet(); Code throws exception listed below. Exception in thread "main" org.elasticsearch.action.search.SearchPhaseExecutionException: Failed to execute phase [query\_fetch], total failure; shardFailures {[PYKLaUd2QtuTuxm7IJv7aA][pdf4][0]: SearchParseException[[pdf4][0]: from[-1],size[100]: Parse Failure [Failed to parse source [{"size":100,"query":{"bool":{"must":[{"field":{"refid":"2"}},{"term":{"updatedate":null}}]}},"fields":["refid","countryid","salary","createdate","updatedate"]}]]]; nested: ElasticSearchParseException[failed to parse date field [null], tried both date format [dateOptionalTime], and timestamp number]; nested: IllegalArgumentException[Invalid format: "null"]; } at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction.onFirstPhaseResult(TransportSearchTypeAction.java:260) at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction$3.onFailure(TransportSearchTypeAction.java:213) at org.elasticsearch.search.action.SearchServiceTransportAction.sendExecuteFetch(SearchServiceTransportAction.java:246) at org.elasticsearch.action.search.type.TransportSearchQueryAndFetchAction$AsyncAction.sendExecuteFirstPhase(TransportSearchQueryAndFetchAction.java:75) at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction.performFirstPhase(TransportSearchTypeAction.java:205) at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction.performFirstPhase(TransportSearchTypeAction.java:192) at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction$2.run(TransportSearchTypeAction.java:178) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Kindly suggest how to add clause for null attribute using QueryBuilder and BoolQueryBuilder. Hiro. On Tuesday, 13 August 2013 14:50:52 UTC+5:30, Hiro Gangwani wrote: > Dear Team, > > I want to query document for field to be null using Java API. I am using > BoolQueryBuilder class and making conditions using QueryBuilders. Here is > example listed below. > > SQL Query : select id,name,salary from candidates where recruiterid is > null and salary \> 10. > > I have converted above query into Java API as given below. > > BoolQueryBuilder lAllQuery = QueryBuilders.boolQuery(); > QueryBuilder lQueryBuilder1 = QueryBuilders.rangeQuery("salary").gt(10.0); > > How do i add condition for recruitid is null. Using > FilterBuilders.missingFilter("recruiterid) it is possible. But i want to > add using QueryBuilders. > > Please advise. > > Thanks, > > Hiro -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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:** [August 13, 2013, 9:47am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/3 "2013-08-13T09:47:51Z")
I doubt that your Java code produces this query: source [{"size":100,"query":{"bool":{"must":[{"field":{"refid":"2"}},{"term":{"updatedate":null}}]}},"fields":["refid","countryid","salary","createdate","updatedate"]}]]] Are you sure this trace is related? -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 13 août 2013 à 11:35, Hiro Gangwani [hiro.gangwani@gmail.com](mailto:hiro.gangwani@gmail.com) a écrit : > source [{"size":100,"query":{"bool":{"must":[{"field":{"refid":"2"}},{"term":{"updatedate":null}}]}},"fields":["refid","countryid","salary","createdate","updatedate"]}]]] -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Hiro\_Gangwani](https://avatars.discourse-cdn.com/v4/letter/h/9fc29f/32.png) [@Hiro\_Gangwani](https://discuss.elastic.co/u/Hiro_Gangwani)\ **Post date:** [August 13, 2013, 10:50am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/4 "2013-08-13T10:50:39Z")
Dear Team, Exce_ption posted earlier was not correct. Some problem in the code. Able to execute the program but no results returned._ \* \* _Please suggest the solution._ \* \* \*Hiro \* On Tuesday, 13 August 2013 14:50:52 UTC+5:30, Hiro Gangwani wrote: > Dear Team, > > I want to query document for field to be null using Java API. I am using > BoolQueryBuilder class and making conditions using QueryBuilders. Here is > example listed below. > > SQL Query : select id,name,salary from candidates where recruiterid is > null and salary \> 10. > > I have converted above query into Java API as given below. > > BoolQueryBuilder lAllQuery = QueryBuilders.boolQuery(); > QueryBuilder lQueryBuilder1 = QueryBuilders.rangeQuery("salary").gt(10.0); > > How do i add condition for recruitid is null. Using > FilterBuilders.missingFilter("recruiterid) it is possible. But i want to > add using QueryBuilders. > > Please advise. > > Thanks, > > Hiro -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Hiro\_Gangwani](https://avatars.discourse-cdn.com/v4/letter/h/9fc29f/32.png) [@Hiro\_Gangwani](https://discuss.elastic.co/u/Hiro_Gangwani)\ **Post date:** [August 13, 2013, 11:15am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/5 "2013-08-13T11:15:18Z")
Hi, I added filter builder using missing filter and able to get the records. FilterBuilder lFilterBuilder = FilterBuilders.missingFilter("recruiterid").nullValue(true); Is there way to get the results using QueryBuilder. Thanks in advance for suggesting solution. Hiro. On Tuesday, 13 August 2013 14:50:52 UTC+5:30, Hiro Gangwani wrote: > Dear Team, > > I want to query document for field to be null using Java API. I am using > BoolQueryBuilder class and making conditions using QueryBuilders. Here is > example listed below. > > SQL Query : select id,name,salary from candidates where recruiterid is > null and salary \> 10. > > I have converted above query into Java API as given below. > > BoolQueryBuilder lAllQuery = QueryBuilders.boolQuery(); > QueryBuilder lQueryBuilder1 = QueryBuilders.rangeQuery("salary").gt(10.0); > > How do i add condition for recruitid is null. Using > FilterBuilders.missingFilter("recruiterid) it is possible. But i want to > add using QueryBuilders. > > Please advise. > > Thanks, > > Hiro -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:21am UTC](https://discuss.elastic.co/t/how-to-query-document-for-field-to-be-null/13196/6 "2017-07-06T02:21:21Z")