# Simulate NOT IN select **URL:** \ **Category:** Elasticsearch\ **Created:** [May 26, 2017, 10:57am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245 "2017-05-26T10:57:14Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 26, 2017, 10:57am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/1 "2017-05-26T10:57:14Z")
Hi, i need help how to create one search query in Elasticsearch. Image we have index (login of clients) with 2 columns -\> timestamp (when action happens) and user id. What i would like to find what users have login after some date (for example this year) but not before this date (not previous years) . In sql query would look like this: SELECT userId FROM A WHERE A.timestamp \> [DATE] AND NOT IN (SELECT \* FROM A WHERE A.timestamp \< [DATE]) ... and perfect would be, if returned 'userId' would be unique 🙂 thanks a lot ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 26, 2017, 11:19am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/2 "2017-05-26T11:19:21Z")
That requires a join between documents, which Elasticsearch does not support, so you may need to do it in two queries. ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 26, 2017, 4:39pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/3 "2017-05-26T16:39:26Z")
Hi, thanks for your response. Can you please help me build this two queries ? (or navigate me with some hints) Thank you very much ! ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 29, 2017, 12:15pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/4 "2017-05-29T12:15:20Z")
Hi, i have tried many queries (with compound queries [https://www.elastic.co/guide/en/elasticsearch/reference/current/compound-queries.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/compound-queries.html)) but i have failed so far. How i can search for result with two queries ? Thanks a lot ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 29, 2017, 4:04pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/5 "2017-05-29T16:04:03Z")
I was initially thinking about first getting all users since the cutoff date in one query and then lookup which of these that have been seen previously in another query. If the cutoff timestamp is not recent and the first query results in a lot of users, this will however be very expensive and inefficient. The best way to get the new users since a specific date might be to create an entity-centric index. If you had a separate index with one record per user (user id as key), which contain the timestamp of the users first activity, you could easily use this to get new users after a specific timestamp.. ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 30, 2017, 6:52am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/6 "2017-05-30T06:52:47Z")
Hi, entity-centric index is really good idea. But i would like also try option with "nested query". I was try to create such query (first query to get users since cutoff date and lookup for users which have NOT been seen previously) -\> but without no success. Can you give me any hint ? thanks a lot ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 31, 2017, 6:39pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/7 "2017-05-31T18:39:33Z")
Hi, so i have prepared real example 🙂 image following data : **CREATED** | **USER\_ID** 2017- **01** -01T15:40:00.000Z | **1** 2017- **01** -01T15:40:00.000Z | **2** 2017- **02** -01T15:40:00.000Z | **1** 2017- **02** -01T15:40:00.000Z | **3** when i execute simple \_search query (math all) -\> you can check data here ([https://pastebin.com/RgYfQq0d](https://pastebin.com/RgYfQq0d)) now when i **execute following query** : ``` "query": { "bool": { "must": { "range": { "CREATED": { "gte": "2017-01-31T12:00:00.000Z"} } }, "filter": { "bool": { "must_not": { "range": { "CREATED": { "lte": "2017-01-31T12:00:00.000Z"} } } } } } }, "_source": { "includes": [ "CREATED", "USER_ID" ], "excludes": [] } ``` I would expect to return single instance **USER\_ID** : **3**... but i have **3** and **also 1** What am i doing wrong ? thanks a lot ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 31, 2017, 6:56pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/8 "2017-05-31T18:56:44Z")
How much data and users will you have and need to query across? The approach with two queries will probably only work for reasonably small datasets. ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 31, 2017, 7:01pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/9 "2017-05-31T19:01:24Z")
Hi, query should be executed (fortunately) over "subset" of all our data (in special index). Every day there is increment of ~500 entries (and i have data for 3 years) -\> so till today it is 600 000 entries. Count of different users is less than 100 000. Thanks ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 31, 2017, 7:04pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/10 "2017-05-31T19:04:55Z")
How far back can the cut-off date go? ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [May 31, 2017, 7:07pm UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/11 "2017-05-31T19:07:12Z")
Let say i will execute query at the beginning of each month -\> cut-off date will be previous month. ---
**Author:** ![Martin\_Duris](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Martin\_Duris](https://discuss.elastic.co/u/Martin_Duris)\ **Post date:** [June 4, 2017, 7:21am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/12 "2017-06-04T07:21:00Z")
Hi, so i have tried to do reindex and create new index where it will be only first login (\_id will be USER\_ID). I have executed: ``` POST _reindex { "source": { "index": "mas-test-act-*" }, "dest": { "index": "mas-act" }, "script": { "inline": "ctx._id = ctx._source['USER_ID'];", "lang": "painless" } } ``` Problem: for each user there is **LAST** login. **Can i do reindex without updating existing documents ?** Thanks a lot ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [June 4, 2017, 8:13am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/13 "2017-06-04T08:13:47Z")
I think you may need to do this through a [scripted update](https://www.elastic.co/guide/en/elasticsearch/reference/5.4/docs-update.html#_scripted_updates) as you want to set fields based on the content of the current document in the index, not just the one you are inserting. The Reindex API does however as far as I know not support scripted updates, so it may need to be done outside of Elasticsearch as outlined in [this post on entity-centric indices](https://discuss.elastic.co/t/reindex-api-aggregations/87790). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 2, 2017, 8:13am UTC](https://discuss.elastic.co/t/simulate-not-in-select/87245/14 "2017-07-02T08:13:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.