Elastic DSL Query

Elastic Stack Elasticsearch
1

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

2

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.

3

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

4

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.

1 Like
5

Thank you for your time and Please share the code!

6

Here's what I got so far assuming this data lives in an index called "example" with the following mappings:

PUT /example
{
  "mappings": {
    "properties": {
      "order_id":    { "type": "integer" },  
      "error_message":  { "type": "keyword"  }, 
      "status":   { "type": "keyword"  }     
    }
  }
}

And the data added with this:

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:

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:

{
  "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.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.