Elasticsearch - How do i search on 2 fields. 1 must be null and other must match search text-CodePudding

I am trying to do a search on elasticsearch 6.8. I don't have control over the elastic search instance, meaning i cannot control how the data is indexed.

I have data structured like this when i do a match. all search:

  { "took": 4,
    "timed_out": false,
    "_shards": {
        "total": 13,
        "successful": 13,
        "skipped": 0,
        "failed": 0
    },
"hits": {
    "total": 2,
    "max_score": 15.703552,
    "hits": [    {
            "_index": "(removed index)",
            "_type": "_doc",
            "_id": "******** (Removed id)",
            "_score": 15.703552,
            "_source": {
                "VCompany": {
                    "cvrNummer": 12345678,
                    "penheder": [
                        {
                            "pNummer": 1234567898,
                            "periode": {
                                "gyldigFra": "2013-04-10",
                                "gyldigTil": "2014-09-30"
                            }
                        }
                    ],
                    "vMetadata": { 
                        "nyesteNavn": {
                            "navn": "company1",
                            "periode": {
                                "gyldigFra": "2013-04-10",
                                "gyldigTil": "2014-09-30"
                            }
                        },
                        }
                    }
                }
            }
        }]

The json might not be fully complete because i removed some unneeded data. So what I am trying to do is search where: "vCompany.vMetaData.nyesteNavn.gyldigTil" is null and where "vCompany.vMetaData.nyesteNavn.navn" will match a text string.

I tried something like this:

{
"query": {
    "bool": {
        "must": [
            {"match": {"Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn": "company1"}}
        ],
        "should": {
            "terms": {
                "Vrvirksomhed.penheder.periode.gyldigTil": null
            }
        }
    }
}

CodePudding user response：

You need to use must_not with exists query like below to check if field is null or not. Below query will give result where company1 is matching and Vrvirksomhed.penheder.periode.gyldigTil field is null.

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn": "company1"
          }
        }
      ],
      "must_not": [
        {
          "exists": {
            "field": "Vrvirksomhed.penheder.periode.gyldigTil"
          }
        }
      ]
    }
  }
}