How to query document for field to be null

Elastic Stack Elasticsearch
1

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.
For more options, visit https://groups.google.com/groups/opt_out.

2

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.
For more options, visit https://groups.google.com/groups/opt_out.

3

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 :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 13 août 2013 à 11:35, Hiro Gangwani 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.
For more options, visit https://groups.google.com/groups/opt_out.

4

Dear Team,
Exception 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.
For more options, visit https://groups.google.com/groups/opt_out.

5

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.
For more options, visit https://groups.google.com/groups/opt_out.