Elasticsearch query for loop through nested array

Question

Elasticsearch v7.0

Hello and good day!

I'm trying to create query on index which have child property(nested type), db_name & contact_full_name. i have documents which in child have different db_name value than db_name field of main doc. so I want to get only documents which have childs of same db_name's count > 0

I have tried to loop of child field but getting error all shards failed can anyone help me to find cause of it?

INDEX MAPPING:

PUT /test_dup_contacts_new
{
  "settings": {
    "number_of_shards": 1
  },
  "mappings": {
    "properties": {
      "con_full_name": { "type": "text" },
      "db_name": { "type": "text" },
      "child": { 
            "type": "nested",
            "properties":{
                "con_full_name": { "type": "text" },
                "db_name": { "type": "text" }
            } 
        }
    }
  }
}

DOCUMENTS:

[
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "3HwmhIMBS7IAsS38K8vg",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "Jesse Grinstead SirManBoy",
          "db_name" : "bc483ac37173a45849add211047a6dd7",
          "child" : [
            {
              "con_full_name" : "Jesse child 1",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            },
            {
              "con_full_name" : "Jesse child 2",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            }
          ]
        }
      },
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "3XwmhIMBS7IAsS388MtR",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "Jesse Grinstead",
          "db_name" : "bc483ac37173a45849add211047a6dd7",
          "child" : [
            {
              "con_full_name" : "Grinstead child 1",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            },
            {
              "con_full_name" : "Grinstead child 2",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            }
          ]
        }
      },
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "3nw9hIMBS7IAsS38LMsf",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "Test Dev",
          "db_name" : "bc483ac37173a45849add211047a6dd7",
          "child" : [
            {
              "con_full_name" : "Grinstead child 1",
              "db_name" : "test"
            },
            {
              "con_full_name" : "Grinstead child 2",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            }
          ]
        }
      },
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "33xHhIMBS7IAsS386ctL",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "Test Dev",
          "db_name" : "bc483ac37173a45849add211047a6dd7",
          "child" : [
            {
              "con_full_name" : "Grinstead child 1",
              "db_name" : "test"
            }
          ]
        }
      },
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "4HxjiYMBS7IAsS38w8sU",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "devid miller",
          "db_name" : "bc483ac37173a45849add211047a6dd7",
          "child" : [
            {
              "con_full_name" : "devid child 1",
              "db_name" : "test_db"
            },
            {
              "con_full_name" : "devid child 2",
              "db_name" : "bc483ac37173a45849add211047a6dd7"
            },
            {
              "con_full_name" : "devid child 3",
              "db_name" : "test_db"
            },
            {
              "con_full_name" : "devid child 4",
              "db_name" : "test_db"
            }
          ]
        }
      },
      {
        "_index" : "test_dup_contacts_new",
        "_type" : "_doc",
        "_id" : "43xliYMBS7IAsS38KMsW",
        "_score" : 1.0,
        "_source" : {
          "con_full_name" : "Snu dev",
          "db_name" : "test_db",
          "child" : [
            {
              "con_full_name" : "Snu child 1",
              "db_name" : "test_db"
            }
          ]
        }
      }
    ]

UNSUCCESSFUL/FAILED QUERY:

GET test_dup_contacts_new/_search
{
  "query": {
    "bool": {
      "filter":{
      "script":{
      "script": {
        "lang": "painless",
        "source": "def num = 0; for (int i=0; i<params._source['child'].length; i++){num++;} if(num>0){ return true;} else{ return false;}"
        
      }
    }
    }
    } 
    
  }
}

ERROR

"error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "runtime error",
        "script_stack": [
          "i=0; i<params._source['child'].length; i++){",
          "             ^---- HERE"
        ],
        "script": "def num = 0; for (int i=0; i<params._source['child'].length; i++){num++;} if(num>0){ return true;} else{ return false;}",
        "lang": "painless"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",

Thanks in advance!

score 1 · Answer 1 · answered Sep 30 '22 at 11:37

Please check this question where Val and Pierre Mallet have given answer and i think so it is similar to what you are looking for. I will suggest to go with Val answer where he suggested to add field at index time and in you scenario, you can have field which store that if db_name is available in nested field or not.

If you still want to go with script then below is script which work for your usecase: (But this is not suggested as it will impact query performance)

POST test1/_search
{
  "min_score": 0.1,
  "query": {
    "function_score": {
      "query": {
        "match_all": {}
      },
      "functions": [
        {
          "script_score": {
            "script": {
              "source": """
              for (int i=0; i<params["_source"]["child"].length; i++){
                def childVal = params._source["child"].get(i);
                if(doc['db_name.keyword'].value==childVal.db_name){
                  return 1;
                }
              }
              return 0;
"""
            }
          }
        }
      ]
    }
  }
}

Also, you need to change your index mapping and configured keyword type of field for using script.

"mappings": {
      "properties": {
        "child": {
          "type": "nested",
          "properties": {
            "con_full_name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword"
                }
              }
            },
            "db_name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword"
                }
              }
            }
          }
        },
        "con_full_name": {
          "type": "text"
        },
        "db_name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword"
            }
          }
        }
      }
    }

Elasticsearch query for loop through nested array

1 Answers1