2

I need to create a query via URI to filter all data between two dates and also if this date field is null.

For example: I have the field "creation_date" in some objects, however I want that in the resulting also does not appear the objects that the field does not have.

I tried something similar below:

http://localhost//elasticsearch/channels/channel/_search?q=channel.schedule.creation_date:[2018-06-19 TO 2018-12-22] OR channel.schedule.creation_date: NULL

As far as comparing the dates is OK, it works. The problem is to get the NULL values.

Edited Source sample:

 "_source": {
                "channel": {
                    "activated": false,
                    "approved": false,
                    "content": "Jvjv",
                    "creation_date": "2018-06-21T13:06:10.000Z",
                    "facebookLink": "J jv",
                    "id": "Kvjvjv",
                    "instagramId": "Jvjv",
                    "name": "Kbkbkvk",
                    "ownerId": "sZtxdhiNbNY9sr2DtiCzlgJfsqb2",
                    "plan": 0,
                    "purpose": "Jvjv",
                    "recurrence": 1,
                    "segment": "Jvjvjv",
                    "twitterId": "Jvjv",
                    "youtubeId": "Jvj"
                }
            }
        }
Vitor Darela
  • 142
  • 7

1 Answers1

5

You can do this using the NOT(_exists_:field_name) constraint:

Can you try this ?

http://localhost//elasticsearch/channels/channel/_search?q=channel.schedule.creation_date:[2018-06-19 TO 2018-12-22] OR NOT(_exists_:channel.schedule.creation_date)
Val
  • 207,596
  • 13
  • 358
  • 360
  • Thank you for your help, I tried this but didn't work – Vitor Darela Jul 20 '18 at 13:07
  • Can you show a document that is returned but should not? – Val Jul 20 '18 at 13:08
  • In fact it does not return, says that it did not find any value. but in my database today, this field does not exist, ie it should return all data. RETURN: { "took": 1, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 0, "max_score": null, "hits": [] } } – Vitor Darela Jul 20 '18 at 13:13
  • Do you have some idea about that? – Vitor Darela Jul 20 '18 at 14:15
  • Btw if I try with this: //elasticsearch/channels/channel/_search?q=(channel.schedule.creation_date:[2018-06-19 TO 2018-12-22]) OR (_exists_:channel.modification_date) work well, the problem is when put the NOT – Vitor Darela Jul 20 '18 at 15:51
  • In your question you wanted to test the `creation_date` field not the `modification_date` one, maybe that's why – Val Jul 20 '18 at 16:12
  • No, it's just creation_date there, I just put the modification_date to test if _exists_ worked, and the function works. The problem is with the NOT clause when placed does not work as expected to filter the nulls. – Vitor Darela Jul 20 '18 at 16:17
  • Just so we're clear, it's `_exists_` not `exists`. See [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html#_field_names). Can you show two documents, one that matches the first condition and a second that should match the second condition? – Val Jul 20 '18 at 16:37
  • Let me try to be a bit more clear, this is exactly the way I'm running: `http://localhost//elasticsearch/channels/channel/_search?q=channel.schedule.creation_date:[2018-06-19 TO 2018-12-22] OR NOT(_exists_:channel.schedule.creation_date)` if I dont put in "code" StackOverFlow remove the "underline", but yes I put exactly how you show me. – Vitor Darela Jul 20 '18 at 17:32
  • Ok, what about my question in my latest comment? – Val Jul 20 '18 at 18:04
  • About your last comment is that.. I put `_exists_` exactly how you told me.. look my query – Vitor Darela Jul 20 '18 at 18:30
  • No, I meant "Can you show two documents, one that matches the first condition and a second that should match the second condition?" – Val Jul 20 '18 at 18:31
  • I'm sorry I understood now what you mean. with `_exists_`: `{ "took": 1, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 0, "max_score": null, "hits": [] } }` second try with `exist`: `{ "took": 1, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 0, "max_score": null, "hits": [] } }` – Vitor Darela Jul 20 '18 at 18:36
  • Is the same, but when I remove the NOT, used only `_exists_`: return as well `{ "took": 2, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 181, "max_score": 1, "hits": [ { "_index": "channels", "_type": "channel", "_id": "-LEWNtPgMHEl8Ep-_DKD", "_score": 1, "_source": { ` – Vitor Darela Jul 20 '18 at 18:38
  • But I really need is NULL, the ones that do not have this field. – Vitor Darela Jul 20 '18 at 18:40
  • `NOT(_exists_)` matches documents which either do not have the field or have the field whose value is null. I'm interested in seeing the source of your document not the response you get. – Val Jul 20 '18 at 18:43
  • One more thing, when I put NOT EXIST without my range of dates (OR) like: `http://localhost//elasticsearch/channels/channel/_search?NOT(_exists_:channel.schedule.creation_date)` WORKED AS WELL but when I put my OR with range of dates the results not appear: `http://localhost//elasticsearch/channels/channel/_search?q=channel.schedule.creation_date:[2018-06-19 TO 2018-12-22] OR NOT(_exists_:channel.schedule.creation_date)` – Vitor Darela Jul 20 '18 at 19:39
  • Looking at your source, the name of your field is `channel.creation_date` and not `channel.schedule.creation_date` – Val Jul 20 '18 at 20:29
  • But is exactly what I need.. I dont have this field `channel.schedule.creation_date` in this structure so I need search by them.. `channel.creation_date` is another field....... The source that is in the Issue description is exactly what the query should show me. – Vitor Darela Jul 20 '18 at 21:01
  • Can you update your question with the mapping you get from `curl -XGET http://localhost//elasticsearch/channels`? – Val Jul 21 '18 at 04:02
  • Sorry for delay, your query worked, the problem was my data .. its good now, thank you for helpe me. – Vitor Darela Jul 25 '18 at 10:30
  • Btw, I also had problem with that structure of query, but when I replaced `OR NOT(_exists_:channel.schedule.creation_date)` with `OR (NOT _exists_:channel.schedule.creation_date)` it works fine for me. Small nuance, but took some time. – Vladimir Gilevich Mar 26 '19 at 13:47