Find all documents containing array object field of size 0 or more

Elastic Stack Elasticsearch
1

Please find below the sample documents that I have in my elasticsearch 7.7.0. How can i build a optimize query to fetch all the documents that contains the array field problemInvestigations . As you can see some documents doesn't have this field and some has with value as null . Need to fetch documents that have this field as empty array or have some items in array. need to eliminate docs with missing field or field with null value.

Sample Documents in elasticsearch:

[
     {
       "_index" : "test",
       "_type" : "_doc",
       "_id" : "5",
       "_score" : 1.0,
       "_source" : {
         "severity" : 5,
         "problemInvestigations" : [
           {
             "Request ID01" : "PBI000000000001",
             "Request Description01" : "PBI000000000001: Test 1"
           },
           {
             "Request Description02" : "PBI000000000002: Test 2",
             "Request ID02" : "PBI000000000002"
           },
           {
             "Request ID03" : "PBI000000000003",
             "Request Description03" : "PBI000000000003: Test 3"
           },
           {
             "Request ID01" : "PBI000000000016",
             "Request Description01" : "PBI000000000016: Test 3"
           }
         ],
         "Summary*" : "Bitlocker encryption log in issue",
         "tenantId" : "tenant",
         "Operational Categorization Tier 2" : "Software",
         "Incident Number" : "PDINC0003203145"
       }
     },
     {
       "_index" : "test",
       "_type" : "_doc",
       "_id" : "92",
       "_score" : 1.0,
       "_source" : {
         "severity" : 5,
         "problemInvestigations" : [
           {
             "Request ID01" : "PBI000000000016",
             "Request Description01" : "PBI000000000016: Test 3"
           }
         ],
         "Summary*" : "Bitlocker encryption log in issue",
         "tenantId" : "tenant",
         "Operational Categorization Tier 2" : "Software",
         "Incident Number" : "PDINC0003203145"
       }
     },
     {
       "_index" : "test",
       "_type" : "_doc",
       "_id" : "107",
       "_score" : 1.0,
       "_source" : {
         "severity" : 5,
         "problemInvestigations" : [ ],
         "Summary*" : "Bitlocker encryption log in issue",
         "tenantId" : "tenant",
         "Operational Categorization Tier 2" : "Software",
         "Incident Number" : "PDINC0003203145"
       }
     },
     {
       "_index" : "test",
       "_type" : "_doc",
       "_id" : "134",
       "_score" : 1.0,
       "_source" : {
         "severity" : 5,
         "problemInvestigations" : null,
         "Summary*" : "Bitlocker encryption log in issue",
         "tenantId" : "tenant",
         "Operational Categorization Tier 2" : "Software",
         "Incident Number" : "PDINC0003203145"
       }
     },
     {
       "_index" : "test",
       "_type" : "_doc",
       "_id" : "135",
       "_score" : 1.0,
       "_source" : {
         "severity" : 5,
         "Summary*" : "Bitlocker encryption log in issue",
         "tenantId" : "tenant",
         "Operational Categorization Tier 2" : "Software",
         "Incident Number" : "PDINC0003203145"
       }
     }
   ]

I am looking for below documents as part of result:

Expected Result:

    [
          {
            "_index" : "test",
            "_type" : "_doc",
            "_id" : "5",
            "_score" : 1.0,
            "_source" : {
              "severity" : 5,
              "problemInvestigations" : [
                {
                  "Request ID01" : "PBI000000000001",
                  "Request Description01" : "PBI000000000001: Test 1"
                },
                {
                  "Request Description02" : "PBI000000000002: Test 2",
                  "Request ID02" : "PBI000000000002"
                },
                {
                  "Request ID03" : "PBI000000000003",
                  "Request Description03" : "PBI000000000003: Test 3"
                },
                {
                  "Request ID01" : "PBI000000000016",
                  "Request Description01" : "PBI000000000016: Test 3"
                }
              ],
              "Summary*" : "Bitlocker encryption log in issue",
              "tenantId" : "tenant",
              "Operational Categorization Tier 2" : "Software",
              "Incident Number" : "PDINC0003203145"
            }
          },
          {
            "_index" : "test",
            "_type" : "_doc",
            "_id" : "92",
            "_score" : 1.0,
            "_source" : {
              "severity" : 5,
              "problemInvestigations" : [
                {
                  "Request ID01" : "PBI000000000016",
                  "Request Description01" : "PBI000000000016: Test 3"
                }
              ],
              "Summary*" : "Bitlocker encryption log in issue",
              "tenantId" : "tenant",
              "Operational Categorization Tier 2" : "Software",
              "Incident Number" : "PDINC0003203145"
            }
          },
          {
            "_index" : "test",
            "_type" : "_doc",
            "_id" : "107",
            "_score" : 1.0,
            "_source" : {
              "severity" : 5,
              "problemInvestigations" : [ ],
              "Summary*" : "Bitlocker encryption log in issue",
              "tenantId" : "tenant",
              "Operational Categorization Tier 2" : "Software",
              "Incident Number" : "PDINC0003203145"
            }
          }
    ]

I tried below script query, but not able to find any documents.

GET test/_search
{
    "query": {
        "bool" : {
          "must" : {
            "match_all": {}
            },
            "filter" : {
                "script" : {
                    "script" : "doc.containsKey('problemInvestigations') && doc['problemInvestigations'].value.length >= 0"
                }
            }
        }
    }
}

Above query gave below output:

    {
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

Please suggest how can I get the expected result

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.