how to get count of not-null value based on specific field in Elasticsearch

Question

I have elastic search index and I need total count of records that one of fields ("actual_start") is not-null how can I do this?

I have wrote this query and I want to append count of not-null actual start value to the result of my query:

 $params = [
            'index' => "appointment_request",
            'body' => [
                'from' => $request['from'],
                'size' => $request['size'],
                "query" => [
                    "bool"=>[
                        "must" => [

                            [
                                "term" => [
                                    "doctor_id" => [
                                        "value" => $request['doctor_id']
                                    ]
                                ]
                            ],
                            [
                                "match" => [
                                    "book_date" => $request['book_date']

                                ]
                            ],
                      
                        ]

                    ],
                ]
            ]
        ];

Assael Azran · Accepted Answer · 2020-09-13 08:38:22Z

7

Take a look at Exists Query

Try this:

GET <your-index>/_count
{
  "query": {
    "exists": {
      "field": "actual_start"
    }
  }
}

Then you can read the count value which will give you the total count of records that actual_start is not-null.

You can also replace _count with _search and total value under hits will give you a total count (in case you also want the hits) .

If you want to do the opposite (all records which actual_start is null):

GET <your-index>/_count
{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "actual_start"
          }
        }
      ]
    }
  }
}

UPDATE

If I understand you correctly you want to append your current query with the exists query.

Example:

GET <your-index>/_search
{
  "query": {
    "bool": {
      "must": [
        {
          <put-your-query-here>
        },
        {
          "exists": {
            "field": "actual_start"
          }
        }
      ]
    }
  }
}

edited Sep 13, 2020 at 8:38

answered Sep 13, 2020 at 7:18

Assael Azran

2,9931 gold badge12 silver badges14 bronze badges

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

4 Comments

Farzane Khazaei Over a year ago

thank you but i have another query and I need to append count of not-null value of actual_start field to it, also i need total to have count of all records in my index whould you please help me how can i do it?

Assael Azran Over a year ago

Can you please share your query? what have you tried so far?

Assael Azran Over a year ago

What do you mean "append count of not-null actual start value to the result of my query"? if you want the count result of not-null actual_start you will have to run the exists query as a different query. Elastic Search doesn't support subqueries. If you intended to filter your query with the not-null actual_start field then use the query I've written under my update.

Assael Azran Over a year ago

If you find my solution helpful please feel free to upvote/accept the answer.

Collectives™ on Stack Overflow

how to get count of not-null value based on specific field in Elasticsearch

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related