How to get empty fields in elasticsearch?

Question

When I do this request, I can get mappings of the index:

GET /users

And it returns this:

{
   "user":{
      "mappings":{
         "skill":{
            "properties":{
               ...
               "Rouge":{
                  "type":"float"
               },
               "Ruby":{
                  "type":"float"
               },
               "Rust":{
                  "type":"float"
               },
               "SAS":{
                  "type":"float"
               },
               "SASS":{
                  "type":"float"
               },
               "SCSS":{
                  "type":"float"
               },
               ...
               "settings":{
                  "index":{
                     "creation_date":"1584415338201",
                     "number_of_shards":"5",
                     "number_of_replicas":"0",
                     "provided_name":"user"
                  }
               }
            }
         }
      }
   }
}

The problem is, some of the fields are empty and I want to find them.

For example no one has a value for Ruby. If I can specifically search for a field to see if it is empty/null I can do it, but I need a query to find all the empty fields, which unfortunately I couldn't find anything online.

Of course I can get all the fields and run an empty-check query on all of them, but it is probably a bad idea. Do you know how to do it better?

I am using version 6.8.

Are you looking for fields that are always empty (never used)? So Ruby never has a value for any document it counts, but if Rouge had a value for one doc it doesn't count — Nate
– Nate, Commented May 27, 2020 at 14:44
I was expecting to see Ruby in the results if there are no documents with Ruby value regardless of other fields. Assume that I have 3 fields (let's say A, B and C) and there are 3 docs, 1 has A and B value, 1 has only A and 1 has only B value. In this case I expect to see C in the result, because no document has a value for C. — Alim Giray Aytar
– Alim Giray Aytar, Commented May 27, 2020 at 14:50

Nimer Awad · Accepted Answer · 2020-05-28 06:41:50Z

1

I think you can use aggregations to achieve that, I know it's not a straightforward solution and you need to write all field names; but it can be helpful.

GET users/_search
{
  "size": 0,
  "aggs": {
    "Rouge": {
      "value_count": {
        "field": "Rouge"
      }
    },
    "Ruby": {
      "value_count": {
        "field": "Ruby"
      }
    },
    "Rust": {
      "value_count": {
        "field": "Rust"
      }
    },
    "SAS": {
      "value_count": {
        "field": "SAS"
      }
    },
    "SASS": {
      "value_count": {
        "field": "SASS"
      }
    },
    "SCSS": {
      "value_count": {
        "field": "SCSS"
      }
    }
  }
}

If one of them has no value, it'll appear as "value": 0, like:

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "Ruby": {
      "value": 0
    }
  }
}

answered May 28, 2020 at 6:41

Nimer Awad

4,2193 gold badges19 silver badges35 bronze badges

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

1 Comment

Alim Giray Aytar Over a year ago

It's working but unfortunately it's pretty slow. I have 350 fields and 7 million documents and query takes around 20 seconds.

Collectives™ on Stack Overflow

How to get empty fields in elasticsearch?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related