ElasticSearch using NEST with SearchDescriptor

Viewed 22

I would like to create a query where the value must be null. It seems that ElasticSearch will ignore any queries with a null value, and null is the exact value i need for that field.

I have tried something like this:

.Query(q => q
                .Bool(b => b
                     .Must(
                        sh => sh.MatchPhrasePrefix(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn").Query($"{criteria.SearchName}")),
                        sh => sh.MatchPhrasePrefix(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.vejnavn").Query($"{criteria.StreetName}")),
                        sh => sh.Match(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.husnummerFra").Query($"{criteria.StreetbuildingIdentifier}")),
                        sh => sh.Match(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.postnummer").Query($"{criteria.ZipCode}")),
                        sh => sh.Match(m => m.Field("Vrvirksomhed.cvrNummer").Query(criteria.CVRNumber)),
                        sh => sh.Match(m => m.Field("Vrvirksomhed.penheder.pNummer").Query(criteria.PNumber))
                        )
                     .Filter(f => f.Term("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.periode.gyldigTil", null))
                    )
                )  

But it will just ignore the "gyldigTil" field.

How can this be done?

Any help will be appreciated.

Best regards, Axel

Update: this is a result showing both gyldigTil with values and with null:

"hits" : [
  {
    "_index" : "cvr-v-20220630",
    "_type" : "_doc",
    "_id" : "1666283",
    "_score" : 11.932152,
    "_source" : {
      "Vrvirksomhed" : {
        "cvrNummer" : 86631628,
        "penheder" : [
          {
            "pNummer" : 1003215505,
            "sidstOpdateret" : "2013-11-22T22:02:48.000+01:00",
            "periode" : {
              "gyldigFra" : "1979-07-06",
              "gyldigTil" : "2000-02-10"
            }
          },
          {
            "pNummer" : 1003215517,
            "sidstOpdateret" : "2000-03-24T16:27:53.000+01:00",
            "periode" : {
              "gyldigFra" : "1979-07-06",
              "gyldigTil" : "2000-12-31"
            }
          },
          {
            "pNummer" : 1003215499,
            "sidstOpdateret" : "1999-10-17T00:00:00.000+02:00",
            "periode" : {
              "gyldigFra" : "1980-02-11",
              "gyldigTil" : "2001-11-14"
            }
          },
          {
            "pNummer" : 1003215529,
            "sidstOpdateret" : "1999-10-17T00:00:00.000+02:00",
            "periode" : {
              "gyldigFra" : "1986-01-01",
              "gyldigTil" : "1999-12-31"
            }
          },
          {
            "pNummer" : 1002648163,
            "sidstOpdateret" : "1999-10-17T00:00:00.000+02:00",
            "periode" : {
              "gyldigFra" : "1998-04-01",
              "gyldigTil" : "1999-12-31"
            }
          },
          {
            "pNummer" : 1009232539,
            "sidstOpdateret" : "2002-07-25T11:08:22.000+02:00",
            "periode" : {
              "gyldigFra" : "2001-01-01",
              "gyldigTil" : "2001-01-01"
            }
          },
          {
            "pNummer" : 1003215517,
            "sidstOpdateret" : "2002-11-29T08:56:30.000+01:00",
            "periode" : {
              "gyldigFra" : "2001-01-02",
              "gyldigTil" : null
            }
          },
          {
            "pNummer" : 1003215499,
            "sidstOpdateret" : "2002-01-02T15:36:28.000+01:00",
            "periode" : {
              "gyldigFra" : "2001-11-16",
              "gyldigTil" : null
            }
          },
          {
            "pNummer" : 1003501520,
            "sidstOpdateret" : "2010-02-03T16:23:39.000+01:00",
            "periode" : {
              "gyldigFra" : "2010-01-01",
              "gyldigTil" : "2011-02-16"
            }
          }
1 Answers

You should rather use exists query as Elasticsearch by default is ignoring nulls in fields and not indexing those values.

.Query(q => q
    .Bool(b => b
        .Must(
            sh => sh.MatchPhrasePrefix(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn").Query($ "{criteria.SearchName}")),
            sh => sh.MatchPhrasePrefix(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.vejnavn").Query($ "{criteria.StreetName}")),
            sh => sh.Match(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.husnummerFra").Query($ "{criteria.StreetbuildingIdentifier}")),
            sh => sh.Match(w => w.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.postnummer").Query($ "{criteria.ZipCode}")),
            sh => sh.Match(m => m.Field("Vrvirksomhed.cvrNummer").Query(criteria.CVRNumber)),
            sh => sh.Match(m => m.Field("Vrvirksomhed.penheder.pNummer").Query(criteria.PNumber)))
        .MustNot(f => f.Exists(e => e.Field("Vrvirksomhed.virksomhedMetadata.nyesteBeliggenhedsadresse.periode.gyldigTil")))))
Related