Aggregation Query filtering on results

Elastic Stack Elasticsearch
1

I have this query:

GET user_info,user_auth_cards_info/_search
{
  "size": 0,
  "aggs": {
    "sorted_user_id": {
      "terms": {
        "field": "user_id",
        "size": 15
      },
      "aggs": {
        "filtered_names": {
          "top_hits": {
            "size": 2,
            "_source": {
              "includes": [
                "e_name",
                "cards_user_name",
                "status",
                "del_status",
                "user_id",
                "frog_id",
                "telphone"
              ]
            }
          }
        }
      }
    }
  }
}

user_info has e_name
user_auth_cards_info has cards_user_name

Issue I'm having is that I want to filter out data where e_name and cards_user_name don't start with a particular string ie "test" but if one of them do then they both should be in the aggregation. I can't figure out how I should be doing this

2

Hi @lakhr034

Did you try add a query to filter before aggregation, like this:

  "query": {
    "bool": {
      "must_not": [
        {
          "bool": {
            "must": [
              {
                "match_phrase_prefix": {
                  "e_name": "test"
                }
              },
              {
                "match_phrase_prefix": {
                  "cards_user_name": "test"
                }
              }
            ]
          }
        }
      ]
    }
  }
3 
GET user_info,user_auth_cards_info/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "script": {
            "script": "doc['e_name'].value.startsWith('test')"
          }
        },
        {
          "script": {
            "script": "doc['cards_user_name'].value.startsWith('test')"
          }
        }
      ]
    }
  },
  "aggs": {
    "sorted_user_id": {
      "terms": {
        "field": "user_id",
        "size": 15
      },
      "aggs": {
        "filtered_names": {
          "top_hits": {
            "size": 2,
            "_source": {
              "includes": [
                "e_name",
                "cards_user_name",
                "status",
                "del_status",
                "user_id",
                "frog_id",
                "telphone"
              ]
            }
          }
        }
      }
    }
  }
}
4

Hey, I think the problem with this query is that if it only matches on one of e_name and not cards_user_name it will only return one whereas I need to get both so that’s why I want to group by id first and then will have to filter out from there.

5

Yeah same issue as above as it will only returning one of them but we still need both if either of them match so need to filter out in aggregation but not sure how

6

The query does exactly that, if both fields start with "test" they will be filtered out. Did you take the test?

7

Oh maybe I wasn't clear enough, my bad. After I said if one of them starts with test they should both be returned.

So I have this data in user_info
{“e_name” : “test”, “user_id” : 1}

then in user_auth_cards_info
{“cards_user_name” : “yellow”, “user_id” : 1}

If I searched on yellow, it would only return user_auth_cards_info but I need it to return both.

8

So it means that the same documents do not have the fields e_name and and cards_user_name. But explain something to me. How do you want to recover the other doc if it doesn't have the yellow value? Your query uses user_id and search term?

9

Yes well I don’t think the search query would work but some type of filter in the aggregation.

So once I group all users by id a filter possibly that filters out the data if there is no sign of test in any values. But seems like this is going to be a tough ask to do so

10

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