Magento 2.x Elasticsearch Category Products Not Showing

I located an exception in ./magento/var/log/exception.log that showed an issue with the configuration of a custom attribute. The attribute was set to a type of text for the attribute custom_attribute in the eav_attribute table, but is_filterable set to 1 in the catalog_eav_attribute table. Setting this value to to 0 and reindexing/clearing cache allowed the category pages to load as expected.

[2020-09-18 19:46:47] main.CRITICAL: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"magento2_product_1_v10","node":"fqGZNT31RSOuoKWU0r5GXQ","reason":{"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."}}],"caused_by":{"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.","caused_by":{"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."}}},"status":400} {"exception":"[object] (Elasticsearch\\Common\\Exceptions\\BadRequest400Exception(code: 400): {\"error\":{\"root_cause\":[{\"type\":\"illegal_argument_exception\",\"reason\":\"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":[{\"shard\":0,\"index\":\"magento2_product_1_v10\",\"node\":\"fqGZNT31RSOuoKWU0r5GXQ\",\"reason\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.\"}}],\"caused_by\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.\",\"caused_by\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Fielddata is disabled on text fields by default. Set fielddata=true on [custom_attribute] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.\"}}},\"status\":400} at /magento/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php:632)"} []
[2020-09-19 17:58:37] main.CRITICAL: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory."}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"magento2_product_1_v2","node":"6KxafAupTSqGYWZUYyULWg","reason":{"type":"illegal_argument_exception","reason":"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory."}}],"caused_by":{"type":"illegal_argument_exception","reason":"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory.","caused_by":{"type":"illegal_argument_exception","reason":"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory."}}},"status":400} {"exception":"[object] (Elasticsearch\\Common\\Exceptions\\BadRequest400Exception(code: 400): {\"error\":{\"root_cause\":[{\"type\":\"illegal_argument_exception\",\"reason\":\"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory.\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":[{\"shard\":0,\"index\":\"magento2_product_1_v2\",\"node\":\"6KxafAupTSqGYWZUYyULWg\",\"reason\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory.\"}}],\"caused_by\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory.\",\"caused_by\":{\"type\":\"illegal_argument_exception\",\"reason\":\"Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [custom_attribute] in order to load field data by uninverting the inverted index. Note that this can use significant memory.\"}}},\"status\":400} at /magento/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php:632)"} []

Show misconfigured attributes:

SELECT *
FROM catalog_eav_attribute cea 
JOIN eav_attribute ea
  ON ea.attribute_id = cea.attribute_id 
  AND backend_type IN ('text')
WHERE 
  cea.is_filterable = 1 OR cea.is_filterable_in_search = 1

Set is_filterable to 0 to allow category pages to load.

UPDATE catalog_eav_attribute cea 
JOIN eav_attribute ea
  ON ea.attribute_id = cea.attribute_id 
  AND backend_type in ('text')
SET 
  cea.is_filterable = 0, cea.is_filterable_in_search = 0
WHERE 
  cea.is_filterable = 1 OR cea.is_filterable_in_search = 1

Reindex/Refresh Cache

magento indexer:reindex
magento cache:clean
magento cache:flush