# Elastic DSL Query **URL:** \ **Category:** Elasticsearch\ **Created:** [April 24, 2023, 1:15pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656 "2023-04-24T13:15:45Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![vrviji](https://avatars.discourse-cdn.com/v4/letter/v/9dc877/32.png) [@vrviji](https://discuss.elastic.co/u/vrviji)\ **Post date:** [April 24, 2023, 1:15pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/1 "2023-04-24T13:15:45Z")
Hello All, I would like to do a self join in DSL. Is that possible in Elastic 7.17. Basically i want to search a index for a certain Error message and if the latest status of the order is completed i dont want to include the error record. It requires to do a self join in SQL . Can it be done in DSL query? orderid Error\_message Newstatus 1 null completed 1 null inprogress 1 Error inprogress 1 null inprogress 2 error inprogress 2 null inprogress I want to display only orderid 2 and its error message in this case. Anyone has any inputs? please do help ---
**Author:** ![Wave](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wave/32/117242_2.png) [@Wave](https://discuss.elastic.co/u/Wave)\ **Post date:** [April 28, 2023, 4:59pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/2 "2023-04-28T16:59:32Z")
Hi @vrviji, You are probably discovering that the concept of joins doesn't really exist in the elastic world. Could you provide a simple example of the data you are working with to understand better. I bet we can figure out your use case, but it's the easiest to get some data and play with it to see what can be done. If you could provide some examples of the data and then an example of what you are looking for returned that would be the best. ---
**Author:** ![vrviji](https://avatars.discourse-cdn.com/v4/letter/v/9dc877/32.png) [@vrviji](https://discuss.elastic.co/u/vrviji)\ **Post date:** [April 30, 2023, 9:52am UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/3 "2023-04-30T09:52:12Z")
Thanks for your response. The sample data is as follows. I have three fields orderid and status and Error field. orderid Error\_message status 1 null completed 1 null inprogress 1 Not approved inprogress 1 null inprogress 2 Service NA inprogress 2 null inprogress Order may be in error message and gets completed once its corrected. In a day, i want to see how many orders are in error status and its message. I want to display error message only if the status of the order is "in progress". If the order is completed, I don't want error message even if it exists. In this sample i want to return only one document which has error\_message "Service NA" Please suggest what is the right way to create functional reports like this? Many a thanks for your time ---
**Author:** ![Wave](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wave/32/117242_2.png) [@Wave](https://discuss.elastic.co/u/Wave)\ **Post date:** [May 2, 2023, 7:16pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/4 "2023-05-02T19:16:28Z")
This one seems tricky. I've got some code that is returning back data, but not as simply as you'd probably want. It uses sub-aggregations. I'll share it just in case I don't get back to this for a bit. Another option would be looking at updating an existing document as the order progresses. ---
**Author:** ![vrviji](https://avatars.discourse-cdn.com/v4/letter/v/9dc877/32.png) [@vrviji](https://discuss.elastic.co/u/vrviji)\ **Post date:** [May 2, 2023, 7:40pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/5 "2023-05-02T19:40:39Z")
Thank you for your time and Please share the code! ---
**Author:** ![Wave](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wave/32/117242_2.png) [@Wave](https://discuss.elastic.co/u/Wave)\ **Post date:** [May 2, 2023, 8:45pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/6 "2023-05-02T20:45:41Z")
Here's what I got so far assuming this data lives in an index called "example" with the following mappings: ```auto PUT /example { "mappings": { "properties": { "order_id": { "type": "integer" }, "error_message": { "type": "keyword" }, "status": { "type": "keyword" } } } } ``` And the data added with this: ```auto POST example/_doc { "order_id": 1, "status": "completed" } POST example/_doc { "order_id": 1, "status": "inprogress" } POST example/_doc { "order_id": 1, "error_message": "Not approved", "status": "inprogress" } POST example/_doc { "order_id": 1, "status": "inprogress" } POST example/_doc { "order_id": 2, "error_message": "Service NA", "status": "inprogress" } POST example/_doc { "order_id": 2, "status": "inprogress" } ``` Running this: ```auto GET example/_search { "size": 0, "aggs": { "agg1": { "terms": { "field": "order_id" }, "aggs": { "agg2": { "terms": { "field": "status" }, "aggs": { "agg3": { "terms": { "field": "error_message" } } } } } } } } ``` Would give you this output: ```auto { "took" : 2, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 8, "relation" : "eq" }, "max_score" : null, "hits" : [] }, "aggregations" : { "agg1" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 1, "doc_count" : 6, "agg2" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "inprogress", "doc_count" : 5, "agg3" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "Not approved", "doc_count" : 1 } ] } }, { "key" : "completed", "doc_count" : 1, "agg3" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [] } } ] } }, { "key" : 2, "doc_count" : 2, "agg2" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "inprogress", "doc_count" : 2, "agg3" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "Service NA", "doc_count" : 1 } ] } } ] } } ] } } } ``` Don't worry if the doc count is not the same. I was adding extra docs, but that shouldn't matter overall. As you can see the last key, "2" in this case shows the "inprogress" status and the error message, where as "1" shows that a status of "completed" is present. You'd have to parse that to see the completed and then to ignore it, if that makes sense. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 30, 2023, 8:45pm UTC](https://discuss.elastic.co/t/elastic-dsl-query/330656/7 "2023-05-30T20:45:56Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.