elasticsearch only show where nested object has no values

Question

I have the following structure (simplified):

{
    "id": 100,
    "vendorStatuses": [
        {
            "id": 200,
            "status": "Open"
        }
    ]
}

What I want to find is records where there are no vendor statuses. We recently upgraded from elasticseach 1.x to 5.x and I'm having trouble converting to get this functionality back.

My old Nest query looked like this:

!Filter<PurchaseOrder>.Nested(nfd => nfd.Path(x => x.VendorStatuses.First())
    .Filter(f2 => f2.Missing(y => y.Id)));

The new query (now that Missing isn't available) looks like this so far:

Query<PurchaseOrder>
    .Bool(z => z
        .MustNot(a => a
            .Exists(t => t
                .Field(f => f.VendorStatuses)
            )
        )
    );

Which generates this:

GET purchaseorder/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "vendorStatuses",
          }
        }
      ]
    }
  }
}

But I'm still seeing results that have vendorStatuses records.

What am I doing wrong? I've tried searching for vendorStatuses.id or other fields, but it's not working. When I try to reverse the logic and do a must i see no results. I also tried doing it as a nested but couldn't get any closer with that.

Russ Cam · Accepted Answer · 2017-08-21 21:19:11Z

1

The query using must_not and exists is not a nested query like the 1.x query. I think you're looking for something like

var query = Query<PurchaseOrder>
    .Bool(z => z
        .MustNot(a => a
            .Nested(n => n
                .Path(p => p.VendorStatuses)
                .Query(nq => nq
                    .Exists(t => t
                        .Field(f => f.VendorStatuses)
                    )
                )
            )
        )
    );

client.Search<PurchaseOrder>(s => s.Query(_ => query));

which yields

{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "query": {
              "exists": {
                "field": "vendorStatuses"
              }
            },
            "path": "vendorStatuses"
          }
        }
      ]
    }
  }
}

You can use operator overloading to make the query more succinct too

var query = !Query<PurchaseOrder>
    .Nested(n => n
        .Path(p => p.VendorStatuses)
        .Query(nq => nq
            .Exists(t => t
                .Field(f => f.VendorStatuses)
            )
        )
    );

answered Aug 21, 2017 at 21:19

Russ Cam

126k34 gold badges211 silver badges275 bronze badges

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

2 Comments

DLeh Over a year ago

That seems to work, thank you so much! I was trying to do Nested and THEN bool, i didn't realize it was possible to do bool then nested, thanks!

Russ Cam Over a year ago

No worries :) You can do nested with an inner bool query, if you have multiple nested queries

DLeh · Accepted Answer · 2017-08-21 19:33:49Z

0

I found a workaround that is far from ideal in my opinion. I created a new property on my PurchaseOrder model for NumberOfStatuses, then I just do a term search on that for value of 0.

public int NumberOfStatuses => VendorStatuses.OrEmptyIfNull().Count();

Query<PurchaseOrder>.Term(t => t.Field(po => po.NumberOfStatuses).Value(0));

answered Aug 21, 2017 at 19:33

DLeh

24.6k17 gold badges96 silver badges141 bronze badges

Collectives™ on Stack Overflow

elasticsearch only show where nested object has no values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related