Search Name match with specific Character and output comes in hierarchical Order

nitesh313 · July 17, 2019, 7:37am

Hello All,

I am new in elasticsearch and straggle to get expected output and below is my query.

Here i basically looking to get all Employee in "organization" index whose name start with "jo" and also the employee who direct reportee to "Smith"(manager) that should come first and then rest of the employee whose name start with "jo".

if you could see in below organization index, all employee name start with "jo" but manager is different. so the employee which is direct reportee
to "smith" should come first then other employees in orgranisation whose name start with "jo".

organization Index script*********
POST organization/_doc/
{"employee" : "jonthan","gender" : "M", "department" : "manufacturing", "manager" : "kene"}

POST organization/_doc/
{"employee" : "jonty","gender" : "M", "department" : "digital", "manager" : "smith"}

POST organization/_doc/
{"employee" : "jolly","gender" : "M", "department" : "adming", "manager" : "mark"}

POST organization/_doc/
{"employee" : "john","gender" : "M", "department" : "digital", "manager" : "smith"}

POST organization/_doc/
{"employee" : "peter","gender" : "M", "department" : "digital", "manager" : "morgan"}

POST organization/_doc/
{"employee" : "steve","gender" : "M", "department" : "digital", "manager" : "morgan"}

So i am looking output something like this:

Output*
jonty
john
jonthan
jolly

dadoonet · July 17, 2019, 7:57am

Here is a naive way for doing it:

DELETE organization
PUT organization
POST organization/_doc/
{"employee" : "jonthan","gender" : "M", "department" : "manufacturing", "manager" : "kene"}
POST organization/_doc/
{"employee" : "jonty","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "jolly","gender" : "M", "department" : "adming", "manager" : "mark"}
POST organization/_doc/
{"employee" : "john","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "peter","gender" : "M", "department" : "digital", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "steve","gender" : "M", "department" : "digital", "manager" : "morgan"}
GET organization/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "prefix": {
            "employee": "jo"
          }
        },
        {
          "match": {
            "manager": "smith"
          }
        }
      ]
    }
  }
}

But I'd prefer using:

DELETE organization
PUT organization
{
  "settings": {
    "analysis": {
      "analyzer": {
        "prefix": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": [
            "lowercase",
            "prefix"
          ]
        }
      },
      "filter": {
        "prefix": {
          "type": "edgeNGram",
          "min_gram": 2,
          "max_gram": 5
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "department": {
        "type": "text"
      },
      "employee": {
        "type": "text",
        "analyzer": "prefix",
        "search_analyzer": "simple"
      },
      "gender": {
        "type": "text"
      },
      "manager": {
        "type": "text"
      }
    }
  }
}
POST organization/_doc/
{"employee" : "jonthan","gender" : "M", "department" : "manufacturing", "manager" : "kene"}
POST organization/_doc/
{"employee" : "jonty","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "jolly","gender" : "M", "department" : "adming", "manager" : "mark"}
POST organization/_doc/
{"employee" : "john","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "peter","gender" : "M", "department" : "digital", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "steve","gender" : "M", "department" : "digital", "manager" : "morgan"}
GET organization/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "employee": "jo"
          }
        },
        {
          "match": {
            "manager": "smith"
          }
        }
      ]
    }
  }
}

nitesh313 · July 17, 2019, 9:31am

Thank you David for prompt response. Both approach which you provided is working for me. However will use the one which you prefer.

nitesh313 · July 17, 2019, 1:07pm

Hello,

I tried number of condition in "Get" method to test the provided approach. However, in one of the condition, i am not getting satisfied output. Please find below Get method with parameters and its output.

So if you can see, Here i am giving employee parameter as "jont" and Manager as "Smith". so Ideally, there is only one employee is direct reportee with name prefix as "jont" but it is giving all Employees which are direct reportee to Smith. Hence could you please help me to fix this.

Output which i am looking is:
jonty
jonthan

********Get Method
GET organization/_search
{
"query": {
"bool": {
"should": [
{
"match": {
"employee": "jont"
}
},
{
"match": {
"manager": "smith"
}
}
]
}
}
}

Output is coming on execution of above get method**********
{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : 2.0296195,
"hits" : [
{
"_index" : "organization",
"_type" : "_doc",
"_id" : "gaHz_2sB4F_hAYlEUqLX",
"_score" : 2.0296195,
"_source" : {
"employee" : "jonty",
"gender" : "M",
"department" : "digital",
"manager" : "smith"
}
},
{
"_index" : "organization",
"_type" : "_doc",
"_id" : "g6Hz_2sB4F_hAYlEaqKR",
"_score" : 1.0296195,
"_source" : {
"employee" : "john",
"gender" : "M",
"department" : "digital",
"manager" : "smith"
}
},
{
"_index" : "organization",
"_type" : "_doc",
"_id" : "gKHz_2sB4F_hAYlES6Iw",
"_score" : 1.0,
"_source" : {
"employee" : "jonthan",
"gender" : "M",
"department" : "manufacturing",
"manager" : "kene"
}
}
]
}
}

dadoonet · July 17, 2019, 1:20pm

Please read this about how to format.

dadoonet · July 17, 2019, 1:24pm

If you add size: 1 you will get only the most relevant hit.

nitesh313 · July 17, 2019, 3:44pm

By Adding "size: 1" it restricting my output to get only one value. In actaul production table, there might be bulk of records. Provided "Organisation" index with its value are the sample record to satisfy the use Case.
Hence Below is the "orgraniation" index with few more employees record. Please execute below script to delete previous index and create new organisation index.

Delete and Create Organization index

DELETE organization
PUT organization
POST organization/_doc/
{"employee" : "nikolas","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "nikon","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "nikeso","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "mark","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "fleming","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "boucher","gender" : "M", "department" : "digital", "manager" : "Kene"}
POST organization/_doc/
{"employee" : "niky","gender" : "M", "department" : "manufacturing", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "jack","gender" : "M", "department" : "manufacturing", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "nikonic","gender" : "M", "department" : "Admin", "manager" : "Tom"}

So Here i basically looking to get Employees who is direct reportee to "Smith" with name prefix as "nik" that should come first and then rest of the employee, who is direct reportee to different manager with name prefix as "nik" . so the output which i am looking here is

*ouput
nikolas
nikon
nikeso
niky
nikonic

dadoonet · July 17, 2019, 3:58pm

What query did you run?

nitesh313 · July 18, 2019, 7:35am

I am running below query.

GET organization/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "prefix": {
            "employee": "nik"
          }
        },
        {
          "match": {
            "manager": "smith"
             
          }
        }
      ]
    }
  }
}

***Getting output on executing above query as:

nikolas
nikon
nikeso
niky
nikonic
mark
fleming

******Actual output i am expecting:

nikolas
nikon
nikeso
niky
nikonic

Please note that in the organization index there are 5 direct reportee to "Smith". But in real scenario there might be more direct reportee. By Using "Size:3" as you suggested in earlier post, it restricting output to the count which i providing in "Size" attribute. Hence, I am not using "Size" attribute here. So query should be dynamic as possible, which gives me 3 employees name which direct reportee to "smith" whose name prefix with "Nik" and also give employees name who direct reportee to other manager but whose name prefix with "Nik".

dadoonet · July 18, 2019, 7:49am

What about:

GET organization/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "manager": "smith"
          }
        }
      ],
      "should": [
        {
          "prefix": {
            "employee": "nik"
          }
        }
      ]
    }
  }
}

nitesh313 · July 18, 2019, 8:47am

By executing above Get query it gives list of all employees who are directly reporting to smith. Prefix condition is not working in above Get query. It is not giving expected result.

****Output coming by executing above query is:
nikolas
nikon
nikeso
mark
fleming

dadoonet · July 18, 2019, 10:01am

Ha right. Sorry. Try this.

GET organization/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "manager": "smith"
          }
        },
        {
          "prefix": {
            "employee": "nik"
          }
        }
      ]
    }
  }
}

nitesh313 · July 18, 2019, 10:35am

I also tried with same GET query which you shared. By executing this query i am getting employees who are direct reportee to "Smith" with name prefix as "nik". However, i also want employees who is not direct reportee to "Smith" with name prefix as "nik".

by executing above query i am getting output as:

nikolas
nikon
nikeso

However i actually expecting below output:
In below output, first 3 employees direct reportee to "Smith" and 4th, 5th employee direct reportee to "morgan" and "tom" respectively

nikolas
nikon
nikeso
niky
nikonic

dadoonet · July 18, 2019, 12:15pm

Here we go:

DELETE organization
PUT organization
POST organization/_doc/
{"employee" : "nikolas","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "nikon","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "nikeso","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "mark","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "fleming","gender" : "M", "department" : "digital", "manager" : "smith"}
POST organization/_doc/
{"employee" : "boucher","gender" : "M", "department" : "digital", "manager" : "Kene"}
POST organization/_doc/
{"employee" : "niky","gender" : "M", "department" : "manufacturing", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "jack","gender" : "M", "department" : "manufacturing", "manager" : "morgan"}
POST organization/_doc/
{"employee" : "nikonic","gender" : "M", "department" : "Admin", "manager" : "Tom"}
GET organization/_search?filter_path=hits.hits._source.employee
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "match": {
                  "manager": "smith"
                }
              },
              {
                "prefix": {
                  "employee": "nik"
                }
              }
            ]
          }
        },
        {
          "prefix": {
            "employee": "nik"
          }
        }
      ]
    }
  }
}

This gives:

{
  "hits" : {
    "hits" : [
      {
        "_source" : {
          "employee" : "nikolas"
        }
      },
      {
        "_source" : {
          "employee" : "nikon"
        }
      },
      {
        "_source" : {
          "employee" : "nikeso"
        }
      },
      {
        "_source" : {
          "employee" : "niky"
        }
      },
      {
        "_source" : {
          "employee" : "nikonic"
        }
      }
    ]
  }
}

nitesh313 · July 18, 2019, 12:56pm

Thanks a lot..by executing above query getting exactly what i am looking.

system · August 15, 2019, 12:56pm

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