Sort on object part that exist

[MrDeliK]

Hi guys, sorry i have another question but this time concerning the sorting of a request.

Considering this mapping

{
  "sample_data": {
      "mappings": {
          "properties": {
              "title": {
                  "properties": {
                      "fr": {
                          "type": "text",
                          "fields": {
                              "keyword": {
                                  "type": "keyword",
                                  "ignore_above": 256
                              }
                          }
                      },
                      "nl": {
                          "type": "text",
                          "fields": {
                              "keyword": {
                                  "type": "keyword",
                                  "ignore_above": 256
                              }
                          }
                      },
                      "poly": {
                          "type": "text",
                          "fields": {
                              "keyword": {
                                  "type": "keyword",
                                  "ignore_above": 256
                              }
                          }
                      }
                  }
              }
          }
      }
  }
}

The restrictions here are if the poly field is set, the fr and nl must be empty. But if the fr is set then the nl must be set and the poly must be empty.
The issue here is when i try to sort on the title i need to use the poly when it is set or the fr/nl depending on the user language.

How can i do that ?

I tried the multiple sort but when the poly is empty it use null and not the fr/nl value

{
    "query": {
        "query_string": {
            "query": "**",
            "fields": ["title.poly", "title.fr"]
        }
    },
    "sort": [{"title.poly.keyword": {"order": "desc"}}, {"title.fr.keyword": {"order": "desc"}}, {"title.nl.keyword": {"order": "desc"}}],
    "_source": ["title"]
}

[
    {
        "_index": "sample_data",
        "_type": "_doc",
        "_id": "elem0",
        "_version": 2,
        "_score": null,
        "_source": {
            "title": {
                "poly": "Mi Casa"
            }
        },
        "sort": [
            "Mi Casa",
            null,
            null
        ]
    },
    {
        "_index": "sample_data",
        "_type": "_doc",
        "_id": "elem1",
        "_version": 1,
        "_score": null,
        "_source": {
            "title": {
                "fr": "test with fr",
                "nl": "test with nl"
            }
        },
        "sort": [
            null,
            "test with fr",
            "test with nl"
        ]
    }
]

[Tomo_M (Tomohiro Mitani)]

One possible recommendation is make other fields for sort and use copy_to mapping.

"properties": {
    "title": {
        "properties": {
            "fr": {
                "type": "text",
                "copy_to": "title_sort_fr",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "nl": {
                "type": "text",
               "copy_to": "title_sort_nl",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "poly": {
                "type": "text",
               "copy_to": ["title_sort_fr", "title_sort_nl"],
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            }
        }
     },
     "title_sort_fr":{
       "type": "keyword"
     },
     "title_sort_nl":{
       "type": "keyword"
     },

copy_to | Elasticsearch Guide [7.16] | Elastic

[MrDeliK]

unfortunately right now i don't have access to the back code.
I did success with a script sorting but i'm not sure if it's a good solution or not.

{
    "query": {
        "multi_match": {
            "query": "Mi ca",
            "type": "phrase_prefix",
            "fields": ["title.poly", "title.fr"]
        }
    },
    "sort" : {
        "_script": {
            "type": "string",
            "script": {
                "lang": "painless",
                "source": "if(doc['title.poly.keyword'].size() != 0) { return doc['title.poly.keyword'].value.toLowerCase(); } return doc['title.fr.keyword'].value.toLowerCase();"
            },
            "order": "desc"
        }
    },
    "_source": ["title"]
}

[Tomo_M (Tomohiro Mitani)]

I suppose there may be only a performance concern. If the response is quick enough for you, it's a good solution!