I want to have following filter with elasticsearch (java high level rest client):
If publish_date exists and lower than now
If publish_end_date exists and greater than now
I already have following but it doesn't show those docs which have no publish_date or publish_end_date:
Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.
A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.
Here I'm not sure on how you are calling the search endpoint.
Having a full script that reproduces the problem with Kibana dev console would help a lot to understand what you are doing wrong.
Below is the query which my queryBuilder generates finally-
POST sadaf/_search
{
"query": {
"bool" : {
"must" : [
{
"bool" : {
"should" : [
{
"match_phrase_prefix" : {
"name" : {
"query" : "spi",
"slop" : 0,
"max_expansions" : 50,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
{
"bool" : {
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
{
"bool" : {
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"filter" : [
{
"bool" : {
"must_not" : [
{
"exists" : {
"field" : "publish_date",
"boost" : 1.0
}
}
],
"should" : [
{
"range" : {
"publish_date" : {
"from" : null,
"to" : "now",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"minimum_should_match" : "1",
"boost" : 1.0
}
},
{
"bool" : {
"must_not" : [
{
"exists" : {
"field" : "publish_end_date",
"boost" : 1.0
}
}
],
"should" : [
{
"range" : {
"publish_end_date" : {
"from" : "now",
"to" : null,
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"minimum_should_match" : "1",
"boost" : 1.0
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
}
I can't reproduce anything with that. Please follow the instructions I shared if you need assistance.
And please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.
Or use markdown style like:
```
CODE
```
This is the icon to use if you are not using markdown format:
There's a live preview panel for exactly this reasons.
Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.
Please update your post.
I have edited my code ,you can check the above query-
I want the records which have the publish_date lte now at the same time those records which don't have the publish_date field
And
the records which have the publish_end_date gte now at the same time those records which don't have the publish_end_date field
I have five records, you can find them in below image-
when I am using only the range query like below its working fine-
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
.must(QueryBuilders.rangeQuery("publish_date").lte("now"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
.must(QueryBuilders.rangeQuery("publish_end_date").gte("now"));
When I am using only the mustNot query like below I am getting the desired results-
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
.mustNot(QueryBuilders.existsQuery("publish_date"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
.mustNot(QueryBuilders.existsQuery("publish_end_date"));
But the problem is when I combine both the queries like below I am not getting any result(getting an empty array)-
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
.should(QueryBuilders.rangeQuery("publish_date").lte("now"))
.mustNot(QueryBuilders.existsQuery("publish_date"))
.minimumShouldMatch(1);
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
.should(QueryBuilders.rangeQuery("publish_end_date").gte("now"))
.mustNot(QueryBuilders.existsQuery("publish_end_date"))
.minimumShouldMatch(1);
I am getting empty
Please read again: About the Elasticsearch category
A typical script like this one can be copied and pasted in Kibana Dev Console by any reader. It will definitely help to play with your example and provide a fix for your script.
If you don't provide it, there is a chance that nobody can help.
edited again now you can copy paste the query and I have give some images of my data and how I am building queries in another comment.
Do you mean that I need to create a dataset manually to test your query?
I can for sure. That will require some time on my end and I'm afraid I can't do it.
I have given the full query and sample data , what else you want?All those datas and all in my local system how can I help you tell me?
Something like this:
DELETE index
PUT index/_doc/1
{
"foo": "bar"
}
GET index/_search
{
"query": {
"match": {
"foo": "bar"
}
}
}POST sadaf/_search
{
"query": {
"bool" : {
"filter" :
{
"bool" : {
"must_not" : [
{
"exists" : {
"field" : "publish_date",
"boost" : 1.0
}
}
],
"should" : [
{
"range" : {
"publish_date" : {
"from" : null,
"to" : "now",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"minimum_should_match" : "1",
"boost" : 1.0
}
}
}
}
}
I don't have any index named sadaf. So it is failing.
Please use below bulk API to create the index and documents (the same data on which I want these manipulations)
POST _bulk
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "1" } }
{"name": "spiderman walks","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-01-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "2" } }
{"name": "spiderman climbs", "storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-03-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "3" } }
{"name": "spiderman antman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-05-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "4" } }
{"name": "spiderman batman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "5" } }
{"name": "spiderman superman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
Here is what you should do:
DELETE sadaf
POST _bulk
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "1" } }
{"name": "spiderman walks","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-01-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "2" } }
{"name": "spiderman climbs", "storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-03-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "3" } }
{"name": "spiderman antman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-05-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "4" } }
{"name": "spiderman batman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "5" } }
{"name": "spiderman superman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
GET sadaf/_search
{
"query": {
"bool": {
"filter": {
"bool": {
"should": [
{
"bool": {
"must_not": [
{
"exists": {
"field": "publish_date"
}
}
]
}
},
{
"range": {
"publish_date": {
"to": "now"
}
}
}
]
}
}
}
}
}
If you don't succeed in translating this to Java, let me know.
1 Like
Thanks a lot,The query is working fine and yes please help me to translate it to java,I have tried but failed to make the same query as yours.
getting problem to place both the range and exists inside should.
What did you try so far to mimic the code I shared?
Yes just that part.
This is the code which I have before-
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
.should(QueryBuilders.rangeQuery("publish_date").lte("now"))
.mustNot(QueryBuilders.existsQuery("publish_date"))
And now I am trying to place the rangeQuery and existsQuery inside one should according to your query but failed-
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
.should((QueryBuilders.rangeQuery("publish_date").lte("now"),BoolQueryBuilder.mustNot(QueryBuilders.existsQuery("publish_date")))
I know this is not possible as should can take only one QueryBuilder in the argument,so could you please help me to do so
Something like this:
BoolQueryBuilder boolQuery = QueryBuilders.boolQuery()
.should(QueryBuilders.rangeQuery("publish_date").lte("now"))
.should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("publish_date")));
1 Like
I have just made this query before sometime and I was about to update my last reply but anyways Thanks a lot for your help.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.