0

Can anyone help me to construct below query. I get below error, when running this query. ES version is 7.9.0; In my model there is a field "repliedBy" which is an array field. It's value is always initialized with empty array. But on some entities it has one or couple of objects. I need to write a query to get all items with empty array only.

   GET myTable/_search
    {
      "query": {
        "bool": {
          "must": [
            {
              "script": {
                "script": {
                  "source": "doc['repliedBy'].size() == params.val",
                  "params": {
                    "val": 0
                  }
                }
              }
            },
            {
              "range": {
                "receivedDate": {
                  "gte": "2020-09-15T07:51:21.000Z",
                  "lte": "2020-12-01T07:51:21.000Z"
                }
              }
            }
          ]
        }
      }
    }

Error:

  "error" : {
    "root_cause" : [
      {
        "type" : "script_exception",
        "reason" : "runtime error",
        "script_stack" : [
          "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:90)",
          "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:41)",
          "doc['repliedBy'].size() == params.val",
          "    ^---- HERE"
        ],
        "script" : "doc['repliedBy'].size() == params.val",
        "lang" : "painless",
        "position" : {
          "offset" : 4,
          "start" : 0,
          "end" : 37
        }
      }
    ],
Rufat Gulabli
  • 634
  • 6
  • 10
  • What's the mapping of `repliedBy`? – Joe - GMapsBook.com Dec 15 '20 at 09:54
  • "repliedBy" : { "properties" : { "id" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "username" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } } } }, – Rufat Gulabli Dec 15 '20 at 11:08

1 Answers1

1

This is the job for a bool/must_not/exists query combination, like this:

{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "repliedBy.id"
          }
        }
      ],
      "filter": [
        {
          "range": {
            "receivedDate": {
              "gte": "2020-09-15T07:51:21.000Z",
              "lte": "2020-12-01T07:51:21.000Z"
            }
          }
        }
      ]
    }
  }
}
Val
  • 207,596
  • 13
  • 358
  • 360