elasticsearch filter nested object

Question

I have an index with a nested object containing two attributes namely scopeId and categoryName. Following is the mappings part of the index

    "mappedCategories" : {
      "type" : "nested",
      "properties": {
        "scopeId": {"type":"long"},
        "categoryName": {"type":"text",
          "analyzer" : "productSearchAnalyzer",
          "search_analyzer" : "productSearchQueryAnalyzer"}
      }
    
    }

A sample document containing the nested mappedCategories object is as follows:

POST productsearchna_2/_doc/1
{
          "categoryName" : "Operating Systems",
          "contexts" : [
            0
          ],
          "countryCode" : "US",
          "id" : "10076327-1",
          "languageCode" : "EN",
          "localeId" : 1,
          "mfgpartno" : "test123",
          "manufacturerName" : "Hewlett Packard Enterprise",
          "productDescription" : "HPE Microsoft Windows 2000 Datacenter Server - Complete Product - Complete Product - 1 Server - Standard",
          "productId" : 10076327,
          "skus" : [ 
            {"sku": "43233004",
              "skuName": "UNSPSC"},
            {"sku": "43233049",
              "skuName": "SP Richards"},
            {"sku": "43234949",
              "skuName": "Ingram Micro"}
          ],
          "mappedCategories" : [ 
            {"scopeId": 3228552,
              "categoryName": "Laminate Bookcases"},
            {"scopeId": 3228553,
              "categoryName": "Bookcases"},
            {"scopeId": 3228554,
              "categoryName": "Laptop"}
          ]
 }

I want to filter categoryName "lap" on scopeId: 3228553 i.e. my query should return 0 hits since Laptop is mapped to scopeId 3228554. But my following query is returning 1 hit with scopeId : 3228554

POST productsearchna_2/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "mappedCategories",
            "query": {
              "term": {
                "mappedCategories.categoryName": "lap"
              }
            },
            "inner_hits": {}
          }
        }
      ],
      "filter": [
        {
          "nested": {
            "path": "mappedCategories",
            "query": {
              "term": {
                "mappedCategories.scopeId": {
                  "value": 3228552
                }
              }
            }
          }
        }
      ]
    }
  },
  "_source": ["mappedCategories.categoryName", "productId"]
}

Following is part of the result of the query:

"inner_hits" : {
          "mappedCategories" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 1.5586993,
              "hits" : [
                {
                  "_index" : "productsearchna_2",
                  "_type" : "_doc",
                  "_id" : "1",
                  "_nested" : {
                    "field" : "mappedCategories",
                    "offset" : 2
                  },
                  "_score" : 1.5586993,
                  "_source" : {
                    "scopeId" : 3228554,
                    "categoryName" : "Laptop"
                  }
                }
              ]
            }
          }

I want my query to return zero hits, and in case I search for "book" with scopeId: 3228552, I want my query to return 2 hits, 1 for Bookcases and another for Laminate Bookcases categoryNames. Please help.

Laminate Bookcases have "scopeId": 3228552 Bookcases have "scopeId": 3228553 When you search for book and filter by ""scopeId": 3228552 you will eliminate the doc "scopeId": 3228553 because it is an AND used in the clauses. If you change it to should you will have the problem of searching for "lap" in scopeId: 3228553 and receiving both scopeId: 3228554 and scopeId: 3228553 docs. Using the scopeId filter you will always get only the docs that have the desired scopeId. — rabbitbr
– rabbitbr, Commented Oct 5, 2022 at 13:20

rabbitbr · Accepted Answer · 2022-10-05 13:20:53Z

0

This query solves part of the problem but when searching for book" with scopeId: 3228552 it will only get 1 result.

GET idx_test/_search?filter_path=hits.hits.inner_hits
{
  "query": {
    "nested": {
      "path": "mappedCategories",
      "query": {
        "bool": {
          "filter": [
            {
              "term": {
                "mappedCategories.scopeId": {
                  "value": 3228553
                }
              }
            }
          ],
          "must": [
            {
              "match": {
                "mappedCategories.categoryName": "laptop"
              }
            }
          ]
        }
      },
      "inner_hits": {}
    }
  }
}

answered Oct 5, 2022 at 13:20

rabbitbr

3,2712 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

elasticsearch filter nested object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related