Shopware 6 Advanced Search thinks nested boolean field is empty when sorting by it

Question

We are trying to use SwagEnterpriseSearchPlatform to execute faster searches on Shopware's storefront.

We have a custom extension for the product table named productInfo. This extension has isRejected (boolean field), flags (keyword field) and isAvailable (boolean field) fields. We want to sort the search results by the nested field productInfo.isAvailable. However results are never sorted and in the response object we get:

Request body:

{
    "query":{
        "bool":{
            "filter":[
                {
                    "terms":{
                        "id":[
                            "58d2bd4e1a1f47168aa3bf85cf6c5a68",
                            "e41101c882f740fbbafa5667e5929a6b",
                            "866e2a1e7a2643c084f07424300ab5ed"
                        ]
                    }
                },
                {
                    "bool":{
                        "must":[
                            {
                                "nested":{
                                    "path":"visibilities",
                                    "query":{
                                        "bool":{
                                            "must":[
                                                {
                                                    "range":{
                                                        "visibilities.visibility":{
                                                            "gte":10
                                                        }
                                                    }
                                                },
                                                {
                                                    "term":{
                                                        "visibilities.salesChannelId":"3b7b6ced513e4f6e9e59133ed2cfa160"
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            },
                            {
                                "term":{
                                    "active":true
                                }
                            }
                        ]
                    }
                }
            ]
        }
    },
    "from":0,
    "size":10000,
    "sort":[
        {
            "productInfo.isAvailable":{
                "order":"asc",
                "nested":{
                    "path":"productInfo"
                }
            }
        }
    ]
}

Response body:

{
    "took":7,
    "timed_out":false,
    "_shards":{
        "total":3,
        "successful":3,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":{
            "value":3,
            "relation":"eq"
        },
        "max_score":null,
        "hits":[
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"58d2bd4e1a1f47168aa3bf85cf6c5a68",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN PLUS C  01894063",
                    "id":"58d2bd4e1a1f47168aa3bf85cf6c5a68",
                    "fullTextBoosted":"ASPIRIN PLUS C  01894063"
                },
                "sort":[
                    9223372036854775807
                ]
            },
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"e41101c882f740fbbafa5667e5929a6b",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN 500MG UEBERZ TABL  10203632",
                    "id":"e41101c882f740fbbafa5667e5929a6b",
                    "fullTextBoosted":"ASPIRIN 500MG UEBERZ TABL  10203632"
                },
                "sort":[
                    9223372036854775807
                ]
            },
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"866e2a1e7a2643c084f07424300ab5ed",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN MIGRAENE  00958281",
                    "id":"866e2a1e7a2643c084f07424300ab5ed",
                    "fullTextBoosted":"ASPIRIN MIGRAENE  00958281"
                },
                "sort":[
                    9223372036854775807
                ]
            }
        ]
    }
}

However when sorting by productInfo.flags the results are sorted accordingly and the response object contains:

Request body:

{
    "bool":{
        "filter":[
            {
                "terms":{
                    "id":[
                        "866e2a1e7a2643c084f07424300ab5ed",
                        "58d2bd4e1a1f47168aa3bf85cf6c5a68",
                        "e41101c882f740fbbafa5667e5929a6b"
                    ]
                }
            },
            {
                "bool":{
                    "must":[
                        {
                            "nested":{
                                "path":"visibilities",
                                "query":{
                                    "bool":{
                                        "must":[
                                            {
                                                "range":{
                                                    "visibilities.visibility":{
                                                        "gte":10
                                                    }
                                                }
                                            },
                                            {
                                                "term":{
                                                    "visibilities.salesChannelId":"3b7b6ced513e4f6e9e59133ed2cfa160"
                                                }
                                            }
                                        ]
                                    }
                                }
                            }
                        },
                        {
                            "term":{
                                "active":true
                            }
                        }
                    ]
                }
            }
        ]
    }
},
"from":0,
"size":10000,
"sort":[
    {
        "productInfo.flags":{
            "order":"desc",
            "nested":{
                "path":"productInfo"
            }
        }
    }
]
}

Response body:

{
    "took":9,
    "timed_out":false,
    "_shards":{
        "total":3,
        "successful":3,
        "skipped":0,
        "failed":0
    },
    "hits":{
        "total":{
            "value":3,
            "relation":"eq"
        },
        "max_score":null,
        "hits":[
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"866e2a1e7a2643c084f07424300ab5ed",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN MIGRAENE  00958281",
                    "id":"866e2a1e7a2643c084f07424300ab5ed",
                    "fullTextBoosted":"ASPIRIN MIGRAENE  00958281"
                },
                "sort":[
                    "[\"1\", \"11\"]"
                ]
            },
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"58d2bd4e1a1f47168aa3bf85cf6c5a68",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN PLUS C  01894063",
                    "id":"58d2bd4e1a1f47168aa3bf85cf6c5a68",
                    "fullTextBoosted":"ASPIRIN PLUS C  01894063"
                },
                "sort":[
                    "[\"1\", \"11\", \"9\", \"10\", \"13\"]"
                ]
            },
            {
                "_index":"sw_product_5a2922f24a674e98801c1add4594e6a1_1692811773",
                "_type":"_doc",
                "_id":"e41101c882f740fbbafa5667e5929a6b",
                "_score":null,
                "_ignored":[
                    "createdAt"
                ],
                "_source":{
                    "fullText":"ASPIRIN 500MG UEBERZ TABL  10203632",
                    "id":"e41101c882f740fbbafa5667e5929a6b",
                    "fullTextBoosted":"ASPIRIN 500MG UEBERZ TABL  10203632"
                },
                "sort":[
                    "[\"1\", \"10\", \"11\"]"
                ]
            }
        ]
    }
}

This problem does not only occur on isAvailable property of productInfo extension but any other nested boolean field.

I also know the nested boolean fields EXIST within the search indexes as I can find them when inspecting the index:

"productInfo":{
    "type":"nested",
    "properties":{
        "flags":{
            "type":"keyword",
            "normalizer":"sw_lowercase_normalizer"
        },
        "isAvailable":{
            "type":"boolean"
        },
        "isRejected":{
            "type":"boolean"
        }
    }
}

Software used:

• Shopware 6.4.20.2
• SwagEnterpriseSearchPlatform 3.4.2
• Elasticsearch 7.17.10
• MySQL 8.0.34

Answer 1

This seems to be a general issue with elasticsearch. Something about how boolean values are being stored and how elasticsearch flattens structures for sorting.

Using a script for sorting worked in my tests.

"sort": [
    {
        "_script": {
            "type": "number",
            "nested": {
                "path": "productInfo"
            },
            "script": {
                "source": "doc['productInfo.isAvailable'].value ? 1 : 0",
                "lang": "painless"
            },
            "order": "asc"
        }
    }
]

Alternatively you might not use a nested type but an object type instead. When you extend the mapping:

[
    // ...
    'productInfo' => [
        'type' => 'object',
        'dynamic' => true,
        'properties' => [
            'isAvailable' => [
                'type' => 'boolean',
            ],
            // ...
        ],
    ],
]

That's how the custom fields are stored for instance. Then this would work as well:

"sort": [
    {
        "productInfo.isAvailable": {
            "order": "asc"
        }
    }
]