Trying to get sort to work on items containing an optional field

Elastic Stack Elasticsearch
1

I have records with an element that is an array of data. If one of the array elements has the 'role' element attached to it, the sort should apply to the data labeled with 'FranchiseCode'. Below is the mapping, sample data, and the query. When run, the sorting is on the 'XYZ' data belonging to the 'role'-associated data.

What is ES using for its criteria to determine which array element to use? If I swap the 'XYZ' and 'FranchiseCode' data associated with 'role', it still picks up the 'XYZ' data.
I have a mapping of:

  {
   "mappings":{
      "properties":{
         "groupUnit":{
            "properties":{
               "identifier":{
                  "properties":{
                     "type":{
                        "type":"text",
                        "fields":{
                           "keyword":{
                              "type":"keyword",
                              "ignore_above":256
                           }
                        }
                     },
                     "value":{
                        "type":"text",
                        "fielddata":true,
                        "fields":{
                           "keyword":{
                              "type":"keyword",
                              "ignore_above":256
                           }
                        }
                     }
                  }
               },
               "name":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               },
               "role":{
                  "type":"text",
                  "fields":{
                     "keyword":{
                        "type":"keyword",
                        "ignore_above":256
                     }
                  }
               }
            }
         }
      }
   }
}

Record 1:

{
   "groupUnit":[
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"IP/IP3"
            },
            {
               "type":"XYZ",
               "value":"0000153"
            }
         ],
         "role":[
            "Controller"
         ],
         "name":"Test3"
      },
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"93/NRF"
            },
            {
               "type":"XYZ",
               "value":"0677423"
            }
         ],
         "name":"Test3"
      }
   ]
}

Record 2:

    {
       "groupUnit":[
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"IP/IP1"
            },
            {
               "type":"XYZ",
               "value":"0000151"
            }
         ],
         "role":[
            "Controller"
         ],
         "name":"Test1"
      },
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"91/NRF"
            },
            {
               "type":"XYZ",
               "value":"0677421"
            }
         ],
         "name":"Test1"
      }
   ]
}

Record 3:

{
   "groupUnit":[
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"IP/IP2"
            },
            {
               "type":"XYZ",
               "value":"0000252"
            }
         ],
         "role":[
            "Controller"
         ],
         "name":"Test2"
      },
      {
         "identifier":[
            {
               "type":"FranchiseCode",
               "value":"92/NRF"
            },
            {
               "type":"XYZ",
               "value":"0677422"
            }
         ],
         "name":"Test2"
      }
   ]
}

Query:

    {
       "query":{
          "match_all":{
          }
   },
   "sort":[
      {
         "groupUnit.identifier.value.keyword":{
            "order":"asc"
         }
      }
   ]
}
2

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