Perform Union on Two Sorted Queries


(Wajahat Javaid) #1

Hi all,
I have a use case where I have to run two elastic search Queries which are paginated, have a sorting criteria, but the number of results are same, I would like to know if there is a possibility to UNION both queries, besides doing it my self by iterating results of both the queries at application end.

Sample Type definition :

{
"lineNo": 4,
"x1": 1556,
"x2": 1758,
"snippetText": "This is my text",

}

x1 and x2 are numric values per line, there are multiple documents with same lineNo and multiple x1, x2 values
what I want is a list of relative x1 sorted by x1 and line number
list of relative x2 sorted by x2 and line number

My Current Queries are:
{
"size": 192,
"query": {
"filtered": {
"filter": {
"range": {
"x1": {
"gt": 0
}
}
}
}
},
"track_scores": true,
"sort": {
"x1": {
"order": "asc"
},
"lineNo": {
"order": "asc"
}
}
}

{
"size": 192,
"query": {
"filtered": {
"filter": {
"range": {
"x2": {
"gt": 0
}
}
}
}
},
"track_scores": true,
"sort": {
"x2": {
"order": "asc"
},
"lineNo": {
"order": "asc"
}
}
}

Both Queries work fine separately, I just want to run them in single request, resulting in a single hits[] object.


(Zachary Tong) #2

You can combine them by using a bool filter with two should clauses. You'll have to combine the sort values as well, and decide if you want to sort x1 or x2 first when there are ties. If some of the docs don't have an x1 or x2 field, you may also have to specify a missing value for sort to handle them correctly.

{
  "size": 400,
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "should": [
            {
              "range": {
                "x1": {
                  "gt": 0
                }
              }
            },
            {
              "range": {
                "x2": {
                  "gt": 0
                }
              }
            }
          ]
        }
      }
    }
  },
  "track_scores": true,
  "sort": {
    "x1": {
      "order": "asc"
    },
    "x2": {
      "order": "asc"
    },
    "lineNo": {
      "order": "asc"
    }
  }
}

(Wajahat Javaid) #3

Thank you for the quick reply, seems really helpful.
x1 and x2 values would not be missing in any scenario, so missing value handling isn't going to be a problem.
I'll try to use it in my scenario and see if it can serve the purpose.

Thank you again


#4

Hi guys, what if I need to limit in size the first resultset (x1>0)?

I need to select maximum XX records if x1>0 and unlimited records if x2>0.

Is it possible without iterating at application end?


(system) #5